OraFAQ Forum: Precompilers, OCI & OCCI » OCCI rounding up higher value

Home » Developer & Programmer » Precompilers, OCI & OCCI » OCCI rounding up higher value (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

OCCI rounding up higher value [message #673058]

Sun, 04 November 2018 01:04

sinpeak
Messages: 59
Registered: January 2011
Location: india

Member

Hi,

I am not sure if this is the right form to discuss my problem.
If not, please advise about the right forum to go to.

I have an OCCI code that selects a table column :

CREATE TABLE ETC ( MNY NUMBER(18,3) );

OCCI code has a double variable and it retrieves the above table column using a resultset and stores it in the double variable:

double dbMny;
stmt1 = conn->createStatement("select mny from ETC ");
rs = stmt1->getResultSet();
while (rs->next())
{
dbMny=rs->getDouble(1);
// Some processing logic //
}

This code works absolutely fine if MNY has lower values.
But I am facing problems with the highest possible value :-

Total 18 digits. 15 before decimal. 3 after decimal : 999999999999999.999

This value gets inserted fine in ETC table but when I retrieve it in my OCCI code it gets rounded to :-

1000000000000000000 (Total 19 digits)

and this is unacceptable to my client.

Am I doing something wrong ?
Please suggest/advise.

Thanks.

Report message to a moderator

Re: OCCI rounding up higher value [message #673060 is a reply to message #673058]

Sun, 04 November 2018 03:01

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

How do you know the value is rounded? (copy and paste the code you use to know the variable value)

[Updated on: Sun, 04 November 2018 03:06]

Report message to a moderator

Re: OCCI rounding up higher value [message #673063 is a reply to message #673058]

Sun, 04 November 2018 05:45

Solomon Yakobson
Messages: 3301
Registered: January 2010
Location: Connecticut, USA

Senior Member

You got what you asked for Smile

. C++ float (double or not) follows IEEE 754 binary format and we obviously lose fractional part precision when converting decimal fraction to binary fraction. Same happens in SQL:

SQL> select cast(999999999999999.999 as binary_double) dbMny from dual;

                DBMNY
---------------------
 1000000000000000.000

SQL> select cast(999999999999999.999 as float) dbMny from dual;

                DBMNY
---------------------
  999999999999999.999

SQL>

SY.

Report message to a moderator

Re: OCCI rounding up higher value [message #673065 is a reply to message #673063]

Sun, 04 November 2018 09:04

sinpeak
Messages: 59
Registered: January 2011
Location: india

Member

Thanks Michel and Solomon for your replies.

To Michel :
The code prints the double value after retrieving from the table -

cout.precision(3);
cout << "COUT ETC :" << fixed << dbMny << endl;

I get the result :-

COUT ETC multi:1000000000000000.000

This is total 19 digits.
So I ran a select on the table value from SQL prompt just to check what is actual value and here is what I found in 3 attempts :-

SQL> select mny from etc;

  MNY
-------------------
         1.0000E+15

SQL> set numwidth 18
SQL> select mny from etc;

  MNY
-------------------
   1000000000000000

SQL> set numformat 9999999999999999.999
SQL> select mny from etc;

  MNY
---------------------
  999999999999999.999

SQL>

So the set numformat command gave the value that I originally inserted.
So I am relieved that the value inserted is fine BUT the value retrieved by the OCCI code is somehow rounding it up.
So I tried to cast the column as suggested in Solomon's code. Please read below my response to Solomon. Please suggest/advise if I am doing something wrong or missing something ?

To Solomon :
I changed the SQL in my OCCI code to :

SELECT CAST(dbMny as float) from ETC;

But still the OCCI code retrieves it as : 1000000000000000.000
So I ran the SQL at SQL prompt as here is the result :

SQL> select cast(mny as float) from etc;

  MNY
-------------------
         1.0000E+15

SQL> set numwidth 18
SQL> select cast(mny as float) from etc;

  MNY
-------------------
   1000000000000000

SQL> set numformat 9999999999999999.999
SQL> select cast(mny as float) from etc;

  MNY
---------------------
  999999999999999.999

SQL>

Please suggest advise.

Thanks.

Report message to a moderator

Re: OCCI rounding up higher value [message #673066 is a reply to message #673065]

Sun, 04 November 2018 09:50

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Use BDOUBLE and getBDouble instead.

And please answer to my question in your previous topic.

Report message to a moderator

Re: OCCI rounding up higher value [message #673067 is a reply to message #673065]

Sun, 04 November 2018 09:52

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

set numwidth 18

If you want the correct value with SQL*Plus (which is another question) you have to set it to at least 19.

SQL> set numwidth 18
SQL> select * from etc;
               MNY
------------------
  1000000000000000

1 row selected.

SQL> set numwidth 19
SQL> /
                MNY
-------------------
999999999999999.999

1 row selected.

Report message to a moderator

Re: OCCI rounding up higher value [message #673068 is a reply to message #673067]

Sun, 04 November 2018 12:27

sinpeak
Messages: 59
Registered: January 2011
Location: india

Member

Thanks Michel for your response.

I used BDouble and getBDouble but the value retrieved is still getting rounded up.

You have asked me to reply to your question.
I am guessing you are talking about this question - "How do you know the value is rounded ?"
After retrieving the value using getBDouble I am printing the value using :-

cout.precision(3);
cout << "COUT ETC :" << fixed << dbMny.value << endl;

where dbMny is declared and initialized as :

BDouble dbMny;
dbMny = BDouble();

Printing using cout gives me :

COUT ETC :1000000000000000.000

This is how I realize that the value is getting rounded.
Am I wrong somewhere ? or missing something ?
Should I insert the value in another table and see what actual value retrieved is ?

Thanks.

Report message to a moderator

Re: OCCI rounding up higher value [message #673069 is a reply to message #673068]

Sun, 04 November 2018 12:45

Solomon Yakobson
Messages: 3301
Registered: January 2010
Location: Connecticut, USA

Senior Member

I thought I explained it. C++ offered FLOAT types use binary, Oracle NUMBER/FLOAT type uses decimal so when you converting decimal 999999999999999.999 to C++ binary float (double or not) you lose precision (same like you would in oracle as I showed in my example where I am casting decimal 999999999999999.999 to binary double). C++ language does not have decimal variables built into it, unlike other languages such as C#. I believe there are some C++ libraries for decimals offered by third party.

SY.

Report message to a moderator

Re: OCCI rounding up higher value [message #673070 is a reply to message #673068]

Sun, 04 November 2018 13:05

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/ You could check if the problem doesn't come from cout using printf("%21.3f\n",dbMny.value)
Side question: are you using 32 or 64bit program?

2/ You can try Number and getNumber.
(From the doc "A Number guarantees 38 decimal digits of precision.")

3/ You can workaround the problem converting the number to string in Oracle then converting back into float in the program:
select TO_CHAR(mny) from ETC -> getString -> stod/stold

Also check constant DBL_DIG and LDBL_DIG constants (if you are using Visual Studio or similar if you are using another environment), they give the number of digits of double and long double data types. With MS Visual Studio 2017, there are 15.
Note that in IEEE 754 standard, double has a precision of 15 digits.

Report message to a moderator

Re: OCCI rounding up higher value [message #673124 is a reply to message #673070]

Thu, 08 November 2018 05:36

sinpeak
Messages: 59
Registered: January 2011
Location: india

Member

Hi Solomon,
Thanks for responding and sharing information.

Report message to a moderator

Re: OCCI rounding up higher value [message #673125 is a reply to message #673124]

Thu, 08 November 2018 05:40

sinpeak
Messages: 59
Registered: January 2011
Location: india

Member

Hi Michel,

I am compiling and executing the code directly on Linux not via MS Visual Studio.

Option 2 :
Quote:

2/ You can try Number and getNumber.
(From the doc "A Number guarantees 38 decimal digits of precision.")

that you suggested - WORKED !! Smile

Thanks a lot for the suggestions and your time and help.

Regards.

Report message to a moderator

Re: OCCI rounding up higher value [message #673134 is a reply to message #673125]

Thu, 08 November 2018 10:12

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

that you suggested - WORKED !! Smile

Can you post the working code for future readers. Thanks.

Report message to a moderator

Re: OCCI rounding up higher value [message #673156 is a reply to message #673134]

Fri, 09 November 2018 07:24

sinpeak
Messages: 59
Registered: January 2011
Location: india

Member

Sure.
Here it is :

inline string num2Str(Number numVal) {
   return (numVal.isNull()) ? "" : numVal.toText(env, "999999999999999999") ;

void function1
{
// declarations etc. go here
	Number dbMny(0);
	stmt1 = conn->createStatement("select mny from ETC ");
	rs = stmt1->getResultSet();
	while (rs->next())
	{
	  dbMny  =rs->getNumber(1);

	// customPrint is another function used to print strings.
	// I converted the number to string only because I was apprehensive that
	// if I convert to integer/long/double then again it will round it like it used to before..

	  customPrint("MESSAGE_CODE_AXG7",(num2Str(dbMny)).c_str());

	}
}

I did not get a chance to research on the best way to print Number in my code.
So instead of converting it into int/long/double and then printing it...I used .toText to convert to string and then print. It works fine.
I hope there is a way to print Number as it is without changing the value ( because of rounding ) and without converting to string.
If you know of any such way, then do let me know.

Thanks.

[Updated on: Fri, 09 November 2018 07:25]

Report message to a moderator

Re: OCCI rounding up higher value [message #673162 is a reply to message #673156]

Fri, 09 November 2018 10:06

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks for the code.

The way to print a Number is to convert it into string using the toText() method of the class.

[Updated on: Fri, 09 November 2018 10:14]

Report message to a moderator

Previous Topic:	sqlcxt getting hanged
Next Topic:	SAVEPOINT in OCCI

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 12:09:13 CST 2024