Strange numeric behavior

User avatar
Enrico Maria Giordano
Posts: 8770
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia
Has thanked: 1 time
Been thanked: 7 times
Contact:

Strange numeric behavior

Post 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 3FUNCTION 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
User avatar
Rick Lipkin
Posts: 2677
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA
Been thanked: 2 times

Re: Strange numeric behavior

Post 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
User avatar
Enrico Maria Giordano
Posts: 8770
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia
Has thanked: 1 time
Been thanked: 7 times
Contact:

Re: Strange numeric behavior

Post by Enrico Maria Giordano »

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

EMG
User avatar
nageswaragunupudi
Posts: 10733
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Been thanked: 11 times
Contact:

Re: Strange numeric behavior

Post 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.
Regards

G. N. Rao.
Hyderabad, India
User avatar
Enrico Maria Giordano
Posts: 8770
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia
Has thanked: 1 time
Been thanked: 7 times
Contact:

Re: Strange numeric behavior

Post 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
User avatar
MarcoBoschi
Posts: 1072
Joined: Thu Nov 17, 2005 11:08 am
Location: Padova - Italy
Contact:

Re: Strange numeric behavior

Post by MarcoBoschi »

bug of the year
:D :D :D :D
Marco Boschi
info@marcoboschi.it
User avatar
nageswaragunupudi
Posts: 10733
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Been thanked: 11 times
Contact:

Re: Strange numeric behavior

Post 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
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
Posts: 10733
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Been thanked: 11 times
Contact:

Re: Strange numeric behavior

Post 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?
Regards

G. N. Rao.
Hyderabad, India
User avatar
Enrico Maria Giordano
Posts: 8770
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia
Has thanked: 1 time
Been thanked: 7 times
Contact:

Re: Strange numeric behavior

Post 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
User avatar
nageswaragunupudi
Posts: 10733
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Been thanked: 11 times
Contact:

Re: Strange numeric behavior

Post 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?
Regards

G. N. Rao.
Hyderabad, India
User avatar
Enrico Maria Giordano
Posts: 8770
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia
Has thanked: 1 time
Been thanked: 7 times
Contact:

Re: Strange numeric behavior

Post by Enrico Maria Giordano »

Mr. NageswaraRao,

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


Tried. No changes. :-(

EMG
User avatar
nageswaragunupudi
Posts: 10733
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Been thanked: 11 times
Contact:

Re: Strange numeric behavior

Post 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. :-)
Regards

G. N. Rao.
Hyderabad, India
User avatar
Enrico Maria Giordano
Posts: 8770
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia
Has thanked: 1 time
Been thanked: 7 times
Contact:

Re: Strange numeric behavior

Post 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
Post Reply