1. Konstantin Goldobin
  2. PowerBuilder
  3. Monday, 11 November 2019 14:22 PM UTC

Hello,

I have a simple Oracle stored function as follows:

CREATE OR REPLACE FUNCTION test_double(a_in NUMBER) RETURN NUMBER IS
BEGIN
RETURN a_in;
END test_double;

And a simple test window with a multilineedit and command button whose clicked event script follows:


double ldb_in, ldb_out
int li

DECLARE test_double PROCEDURE FOR TEST_DOUBLE( :ldb_in );

ldb_in = 17164295

for li = 1 to 10
execute test_double;
fetch test_double into :ldb_out;
mle_1.text += 'in: ' + string(ldb_in) + ' out: ' + string( ldb_out) + '~r~n'
close test_double;
ldb_in++
next

When this runs in PB2017 R3 connected using the O10 driver, the result is as follows:

in: 17164295 out: 17164296
in: 17164296 out: 17164296
in: 17164297 out: 17164296
in: 17164298 out: 17164298
in: 17164299 out: 17164300
in: 17164300 out: 17164300
in: 17164301 out: 17164300
in: 17164302 out: 17164302
in: 17164303 out: 17164304
in: 17164304 out: 17164304

When the ORA driver is used, the "out" values are exactly the same as the "in" values. In PB12.5 no matter which driver is used O10 or ORA the result is also correct. I was wondering if anyone knows anything about this change of the O10 driver in PB2017.

Michael Kramer Accepted Answer Pending Moderation
  1. Tuesday, 12 November 2019 09:43 AM UTC
  2. PowerBuilder
  3. # 1
0
Votes
Undo

Hi Konstantin (re your comment to reply #4),

I see that Appeon can reproduce your finding. It may be that they can fix so that the auto-convert into just 4-byte precision can be avoided. In any case here are the lookups + links I use when I compare datatypes.

  • PowerBuilder Help
    • Connecting to Your Database
      • Working with Native Database Interfaces
        • Using Oracle
          • Supported Oracle Datatypes

That section lists the supported Oracle datatypes. It also has a sub-section titled Datatype conversion that talks specifically about NUMBER and mapping to DECIMAL. What I usually do when in doubt of exact mappings is: Compare PowerScript datatypes to specific DBMS datatypes.

  • PowerBuilder Help
    • PowerScript Reference
      • PowerScript Topics
        • Datatypes
          • Standard datatypes
        • Declarations
          • Declaring external functions
            • Datatypes for external function arguments

Standard datatypes lists all the datatypes. The external function arguments page lists the byte-size of each numeric type.

Oracle's documentation for comparison of datatypes. Each DBMS has similar kind of online doc that I/you/we can use for comparison to PowerScript's built-in datatypes.

HTH /Michael

Comment
Hi Michael;

FYI: PB2017 R3 was released in January of 2018.

Regards ... Chris
  1. Chris Pollach @Appeon
  2. Wednesday, 13 November 2019 19:20 PM UTC
Hi Michael,

I reported this bug https://www.appeon.com/standardsupport/track/view?id=1264 in summer last year, and it's about PB fundamentals. Still no fix in sight, which to me looks like a sign that, with Appeon focus on cloudifying PB, the outlook for standard PB features maintenance and improvement is rather gloomy...
  1. Konstantin Goldobin
  2. Friday, 22 November 2019 09:54 AM UTC
I beg to differ on the "gloomy outlook for standard PB features" :: Native PDF, encrypt/decrypt, digital signatures, native browser control, UI themes, ribbon bar control, and C# interop without COM wrappers.

I suggest you contact Appeon directly regarding status on this bug report. Any priority decisions for specific bug is outside scope for us community members. In the days of PowerSoft and Sybase feasible workarounds often resulted in actual fix getting lower priority.
  1. Michael Kramer
  2. Friday, 22 November 2019 10:13 AM UTC
There are no comments made yet.
Konstantin Goldobin Accepted Answer Pending Moderation
  1. Tuesday, 12 November 2019 08:12 AM UTC
  2. PowerBuilder
  3. # 2
0
Votes
Undo

And yes, I did not mention this but a regular embedded SQL SELECT works without this issue, so if I change the sample code to:

double ldb_in, ldb_out
int li

ldb_in = 17164295

for li = 1 to 10
SELECT test_double( :ldb_in) INTO :ldb_out FROM dual;
mle_1.text += 'in: ' + string(ldb_in) + ' out: ' + string( ldb_out) + '~r~n'
ldb_in++
next

It works as expected. But as you understand this is just a test window I created for illustration purposes. The real procedures make changes on the DB.

Comment
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Tuesday, 12 November 2019 06:43 AM UTC
  2. PowerBuilder
  3. # 3
0
Votes
Undo

Hi,

If you need a workaround, try to declare your stored procedure as RPCFUNC in a transaction object, like n_tr of the pfc's or make your own. Use that one in the application object under the "additional properties" button > "variable types tab page" > SQLCA. Put "n_tr" there instead of sqlca.

You can still use sqlca everywhere in your code.

Then in the n_tr object, declare the following under "local external functions":

function double TEST_DOUBLE(double ldb_in ) RPCFUNC ALIAS FOR "owner.TEST_DOUBLE"

(for a stored procedure without return value, use "subroutine" instead of "function")

Then in your application's code, you can call the function / sp like this 

double ldbl_in, ldbl_ret

ldbl_in = 10.34

ldbl_ret = sqlca.test_double(ldbl_in)

 

See if that fixes the problem, though you might have to do this for all of your stored procedures.

Maybe you have to replace double with decimal, like Michael says.

Also, if you would use JDBC or ODBC to connect, you'd have to add this to the dbparm value of your transaction object when you connect: PBNewSPInvocation='Yes'

HIH

Comment
Thanks Miguel, I tried playing with dbparm when we accidentally discovered this for the first time but it did not change anything.
  1. Konstantin Goldobin
  2. Tuesday, 12 November 2019 08:02 AM UTC
YW Konstantin,

Even though it might be too much work, but .. did you try the proposed workaround of using RPCFUNC declarations in a transaction object?
  1. Miguel Leeuwe
  2. Tuesday, 12 November 2019 12:13 PM UTC
I did try it in my test window and it worked correctly just like the embedded SELECT (I though I'd written it here but apparently I had not).
  1. Konstantin Goldobin
  2. Tuesday, 12 November 2019 15:26 PM UTC
There are no comments made yet.
Zhao Kai @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 12 November 2019 06:08 AM UTC
  2. PowerBuilder
  3. # 4
0
Votes
Undo

Hi Konstantin,

Thanks for reporting this problem! We reproduced it on our end and will do
further research to figure it out. We will keep you posted about the progress.
BTW, PB 12.6 has the same issue as well.

Regards,
ZhaoKai

Comment
Thanks! Hopefully it will be fixed and other Oracle drivers will be checked as well.
  1. Konstantin Goldobin
  2. Tuesday, 12 November 2019 15:27 PM UTC
There are no comments made yet.
Michael Kramer Accepted Answer Pending Moderation
  1. Monday, 11 November 2019 18:16 PM UTC
  2. PowerBuilder
  3. # 5
0
Votes
Undo

Hi Konstantin, 

I would expect such rounding issues to occur at 7-8 digits precision if the data type somewhere in the process converts to a real instead of long, decimal, or double. 

Some Afterthought

You PL/SQL function uses NUMBER as data type. That data type matches DECIMAL in PowerScript, not DOUBLE!

When you use floating point data types (real/double) but database uses a decimal data type you WILL get rounding errors due to automatic type conversions.

Below code I would expect to behave better because it avoids precision loss.

dec ldec_in, ldec_out

for ldec_in = 17164295 to 17164304
   SELECT Test_double(:ldec_in) INTO :ldec_out FROM DUAL;
   mle_1.Text += 'in: ' + string(ldec_in) + ' out: ' + string(ldec_out) + '~r~n'
next

HTH /Michael

Comment
Hello Michael,



Thanks for your reply! I know that changing the datatype changes the result, whether it's decimal or long, but we are talking about migration of a big application from PB12.5 to PB2017 where all these places that are using doubles work as expected with O10. BTW, what Oracle datatype does match PB double? Could you maybe give a reference to online help where the matching is described? For example, if I use PB IDE Paste Special in a transaction object Local External Function declaration, PB inserts the following:

function double TEST_DOUBLE(double A_IN) RPCFUNC ALIAS FOR "~"MYSCHEMA~".~"TEST_DOUBLE~""
  1. Konstantin Goldobin
  2. Tuesday, 12 November 2019 08:01 AM UTC
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.