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
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
Re: Strange numeric behavior
Posted: Sat Sep 21, 2013 10:16 am
by nageswaragunupudi
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=42316nageswaragunupudi 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
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
