Page 1 of 2

Strange numeric behavior

Posted: Fri Sep 20, 2013 5:34 pm
by Enrico Maria Giordano
Dear friends, anybody knows why the sample below prints 10.50 and 1050.00? And why 1050 is written to Test field?

Code: Select all | Expand

#define adOpenForwardOnly 0
#define adOpenKeyset      1
#define adOpenDynamic     2
#define adOpenStatic      3

#define adLockReadOnly        1
#define adLockPessimistic     2
#define adLockOptimistic      3
#define adLockBatchOptimistic 4

#define adUseNone   1
#define adUseServer 2
#define adUseClient 3


FUNCTION MAIN()

    LOCAL cCns := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=MyDatabase.mdb"

    LOCAL oCn, oRs

    FERASE( "MyDatabase.mdb" )

    CREATEOBJECT( "ADOX.Catalog" ):Create( cCns )

    oCn = CREATEOBJECT( "ADODB.Connection" )

    oCn:Open( cCns )

    oCn:Execute( "CREATE TABLE Test ( Id COUNTER PRIMARY KEY, Test NUMERIC ( 7, 2 ) )" )

    oRs = CREATEOBJECT( "ADODB.Recordset" )

    oRs:CursorLocation = adUseClient

    oRs:Open( "SELECT * FROM Test", oCn, adOpenForwardOnly, adLockOptimistic )

    oRs:AddNew()

    oRs:Fields( "Test" ):Value = 10.5

    oRs:Update()

    ? oRs:Fields( "Test" ):Value

    oRs:Requery()

    ? oRs:Fields( "Test" ):Value

    oRs:Close()

    oCn:Close()

    RETURN NIL


EMG

Re: Strange numeric behavior

Posted: Fri Sep 20, 2013 6:42 pm
by Rick Lipkin
Enrico

I compiled this into a console app with both

Code: Select all | Expand


 *oCn:Execute( "CREATE TABLE Test ( Id COUNTER PRIMARY KEY, Test NUMERIC ( 7, 2 ) )" )
 oCn:Execute( "CREATE TABLE Test ( Id COUNTER PRIMARY KEY, Test DECIMAL(7, 2) DEFAULT 0 )" )
 


and got the same result .. both times the screen message was 10.50 .. the Table showed 10.5. I am using xHarbour 1.21 9444

Rick Lipkin
Image

Image

Re: Strange numeric behavior

Posted: Fri Sep 20, 2013 8:20 pm
by Enrico Maria Giordano
Don't work here. I wonder if it have something to do with localization...

EMG

Re: Strange numeric behavior

Posted: Sat Sep 21, 2013 6:12 am
by nageswaragunupudi
I am also getting correct results, i.e. 10.50. Not 1050, with both Harbour and xHarbour.

Are you getting the same error if you use field type Money or Double? Though Numeric type with Access working perfectly for me, earlier I got feedback from some users (probably with some older installations) they were having problems with numeric field type.

It is worth verifying how this is behaving with money and double field types. If you are getting this error with all three field types then we need to examine other reasons like localization, etc.

Re: Strange numeric behavior

Posted: Sat Sep 21, 2013 9:30 am
by Enrico Maria Giordano
With

Code: Select all | Expand

oCn:Execute( "CREATE TABLE Test ( Id COUNTER PRIMARY KEY, Test DOUBLE )" )


or

Code: Select all | Expand

oCn:Execute( "CREATE TABLE Test ( Id COUNTER PRIMARY KEY, Test CURRENCY )" )


It works fine, but I need to use NUMERIC or DECIMAL. :-(

EMG

Re: Strange numeric behavior

Posted: Sat Sep 21, 2013 10:09 am
by MarcoBoschi
bug of the year
:D :D :D :D

Re: Strange numeric behavior

Posted: Sat Sep 21, 2013 10:16 am
by nageswaragunupudi
MarcoBoschi wrote:bug of the year
:D :D :D :D

It should be a known issue.
It is not safe to use NUMERIC field type with MSAccess. This may work on latest installations but not on all PCs In the old versions of Access this field type was not available.

On my PC it works but I can not assume it works on client's pc.
FW Ado functions use Double or Money in Table create functions for Access

Re: Strange numeric behavior

Posted: Sat Sep 21, 2013 10:28 am
by Enrico Maria Giordano
Ok, thank you. :-(

EMG

Re: Strange numeric behavior

Posted: Sat Sep 21, 2013 12:33 pm
by nageswaragunupudi
Mr EMG

Though by abundant caution I am using Money and Double, the issue of Numeric fields is still confusing to me.

My earlier feedbacks were that saving was giving errors or when try/catch was used nothing was saved. But I was never informed that 12.50 was saved as 1250. Even Jet and DBMS versions were matching. I should admit that my confusion still persists and wish we understand better.

I understand you have been using MSAccess/ADO for long and also know that you generally use Numeric field type. Is this present issue experienced on a particular PC now or all PCs?

Re: Strange numeric behavior

Posted: Sat Sep 21, 2013 12:48 pm
by Enrico Maria Giordano
NageswaraRao,

nageswaragunupudi wrote:Mr EMG

Though by abundant caution I am using Money and Double, the issue of Numeric fields is still confusing to me.


Me too. :-)

nageswaragunupudi wrote:My earlier feedbacks were that saving was giving errors or when try/catch was used nothing was saved. But I was never informed that 12.50 was saved as 1250. Even Jet and DBMS versions were matching. I should admit that my confusion still persists and wish we understand better.


I agree. I also found this regarding MySQL:

http://bugs.mysql.com/bug.php?id=42316

nageswaragunupudi wrote:I understand you have been using MSAccess/ADO for long and also know that you generally use Numeric field type.


Yes, I've been using ADO (with various database engines) since very long but usually with no NUMERIC or DECIMAL fields. Now that I need them I found the bug. :-(

nageswaragunupudi wrote:Is this present issue experienced on a particular PC now or all PCs?


Marco Boschi found the same problem too.

EMG

Re: Strange numeric behavior

Posted: Sat Sep 21, 2013 1:25 pm
by nageswaragunupudi
Mr EMG

I agree. I also found this regarding MySQL:

http://bugs.mysql.com/bug.php?id=42316

Thanks for the information. However its somewhat comforting that clientside recordsets are working well and inserts are working well.

In the case of MSAcess can you take the trouble of doing this test?

oRs:AddNew( { "test" }, { 12.50 } )
and then please check the result. Does it make any difference?

Re: Strange numeric behavior

Posted: Sat Sep 21, 2013 2:43 pm
by Enrico Maria Giordano
Mr. NageswaraRao,

nageswaragunupudi wrote:oRs:AddNew( { "test" }, { 12.50 } )


Tried. No changes. :-(

EMG

Re: Strange numeric behavior

Posted: Sat Sep 21, 2013 2:52 pm
by nageswaragunupudi
Thanks for your efforts. I too did not expect much difference.
Let us keep exploring.
I can not do any tests here. Only you can. :-)

Re: Strange numeric behavior

Posted: Sat Sep 21, 2013 2:58 pm
by Enrico Maria Giordano
Ok. :-)

EMG

Re: Strange numeric behavior

Posted: Sat Sep 21, 2013 6:20 pm
by Enrico Maria Giordano
I just discovered that if I change my international settings ("." for decimal separator and "," for group separator - my settings was the other way around) then my sample works just fine!

Of course this is not a solution. I have to find a way to change the settings for my application...

Any ideas? :-)

EMG