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

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

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

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.

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.

• Oracle Built-in Datatypes

HTH /Michael