I noticed this in last time, so i guess it may not hurt to share my findings and even some sort of solution:
It seems that oracle (at least v9) excpects string representation of decimal numbers to use localized decimal separator, so for example 1.2 produces 'invalid number' if passed as a value to SQLBindParam for numeric field under slovak locale (which uses , as decimal separator) - 1,2 work fine then. Also, returned values use localized decimal separator too. So, what to do with it:
For input values, 1.2 = 12E-1. Exp format seems to be recognized well by odbc (at least for oracle and sybase asa wich are my targets), so all that needs to be done is format number in a way that it does not use any decimal separator.
For output values, simply treat any non-digit character as decimal separator - nobody should be stupid enough to set digit as decimal separator i guess.
This seems to working just well for input formatting:
function BcdToStrExpNotationWithNoDecimalSeparator(Bcd: TBcd): String;
Sc := Bcd.SignSpecialPlaces and 63;
if Sc > 0 then
Bcd.SignSpecialPlaces := Bcd.SignSpecialPlaces and not 63;
Result := Format('%sE-%d', [ BcdToStr(Bcd), Sc ]);
Result := BcdToStr(Bcd);
Very interestingly. I shall test your variant. Even if it will work not everywhere, I all the same shall add it.
Btw, this is extract from msdn:
ODBC Programmer's Reference
Numeric Literal Syntax
The following syntax is used for numeric literals in ODBC:
numeric-literal ::= signed-numeric-literal | unsigned-numeric-literal
signed-numeric-literal ::= [sign] unsigned-numeric-literal
unsigned-numeric-literal ::= exact-numeric-literal | approximate-numeric-literal
exact-numeric-literal ::= unsigned-integer [period[unsigned-integer]] |
sign ::= plus-sign | minus-sign
approximate-numeric-literal ::= mantissa E exponent
mantissa ::= exact-numeric-literal
exponent ::= signed-integer
signed-integer ::= [sign] unsigned-integer
unsigned-integer ::= digit...
plus-sign ::= +
minus-sign ::= -
digit ::= 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 0
period ::= .
1998-2004 Microsoft Corporation. All rights reserved.
Well, i wonder where did oracle get the idea about using localized decimal searator at first place? However i'm loking at it, i still see just "period ::= ." - that is a DOT exatly. Oh, well, at least that same specification tells that exp notation should work!
Log in to post a comment.