PxPlus User Forum

Main Board => Discussions => ODBC => Topic started by: michaelgreer on February 18, 2019, 05:41:40 PM

Title: odbc 5.1 vs 5.2
Post by: michaelgreer on February 18, 2019, 05:41:40 PM
I have the following construct in a development environment running 5.2 odbc:

,   {fn REPLACE(
      {fn REPLACE(
      {fn REPLACE(
      {fn REPLACE(
      {fn REPLACE(GEBH.gl_type,'A','Asset')}
                        ,'R','Revenue')}
                        ,'E','Expense')}
                        ,'L','Liability')}
                        ,'C','Capital')}
      as 'Acct Type Desc'   

When I port this to a customer running ODBC 5.1 I get an "invalid argument in a scalar function  - REPLACE" message. I don't see a note in the docs about this. Is this a known limit of 5.1?  a bug?  something else?

Thanks!
Title: Re: odbc 5.1 vs 5.2
Post by: Devon Austen on February 19, 2019, 10:25:01 AM
A bug was fixed in version 5.20.0002 of the ODBC driver with the REPLACE scalar function. The bug was triggered when the replacement text contained the search text. In your Example the search text is 'R' and that replacement text  'Revenue' also contains 'R'. Therefore it is expected that the bug would cause an issue with version 5.10 of the ODBC driver without the fix.

The customer running 5.10 should upgrade to at least 5.20.0002. If they are licensed for 5.10 then they can run 5.20 with that license.

Title: Re: odbc 5.1 vs 5.2
Post by: michaelgreer on February 19, 2019, 11:40:54 AM
Devon,

Would case change help here. That would be a temp. fix until I get them upgraded. 

thanks!
Michael
Title: Re: odbc 5.1 vs 5.2
Post by: Devon Austen on February 19, 2019, 01:35:46 PM
I believe that doing a replace of 'R' with 'revenue' would avoid the bug.
Title: Re: odbc 5.1 vs 5.2
Post by: Mike King on February 21, 2019, 09:34:39 AM
Another option would be to replace "R" with something else first then add a replace of that with Revenue.  Something like:

,   {fn REPLACE(
      {fn REPLACE(
      {fn REPLACE(
      {fn REPLACE(
      {fn REPLACE(
      {fn REPLACE(GEBH.gl_type,'A','Asset')}
                        ,'R','#')}
                        ,'#','Revenue')}

                        ,'E','Expense')}
                        ,'L','Liability')}
                        ,'C','Capital')}
      as 'Acct Type Desc'   


You likely would need to do this for all the replacements.