Strange numeric behavior

[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 view

#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

[Rick Lipkin]

Enrico

I compiled this into a console app with both

Code: Select all  Expand view

 *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

[Enrico Maria Giordano]

Don't work here. I wonder if it have something to do with localization...

EMG

[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.

[Enrico Maria Giordano]

With

Code: Select all  Expand view

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

or

Code: Select all  Expand view

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

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

EMG

[MarcoBoschi]

bug of the year

[nageswaragunupudi]

bug of the year

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

[Enrico Maria Giordano]

Ok, thank you.

EMG

[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?

[Enrico Maria Giordano]

NageswaraRao,

Mr EMG

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

Me too.

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

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.

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

Marco Boschi found the same problem too.

EMG

[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?

[Enrico Maria Giordano]

Mr. NageswaraRao,

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

Tried. No changes.

EMG

[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.

[Enrico Maria Giordano]

Ok.

EMG

[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