ODBC decimal number problem

[nw1]

Hi,

Sorry if this problem has already been adressed earlier , i could not find
it.

Our application :VB5 /MDAC 2.0/Microsoft ODBC/Oracle 7 client/Oracle 8
database worked perfectly

BUT

some PC moved to win XP and
we had to use Oracle 9 client , so

VB5/MDAC 2.0/Oracle 9 client ODBC/Oracle 9 Client/Oracle 8 database does
not correctly access
decimal numbers:decimal numbers fetched from the database are either
truncated (35.48 -> 35.00) or
multiplicated (35 ->3500).

A similar (not exactly the same) problem occurs with crystal report.

We tried to analyze things with crystal:
Crystal/oracle driver 7 for odbc -> OK
Crystal/Oracle OLE DB driver 9 ->OK
Crystal/Oracle 9 ODBC driver -> NOT OK

We thought of an NLS (france) problem ( , <-> .) and changed things on the
server and/or client:
nothing changed.

Have no more ideas (moving to OLE DB is a worst case solution ??)

Thanks for any help

[Dmitri Ivanov]

Hello nw1,
"nw1" <j.bra...@online.fr> wrote:

n> Sorry if this problem has already been adressed earlier , i could
n> not find it.
n> Our application :VB5 /MDAC 2.0/Microsoft ODBC/Oracle 7 client/Oracle
n> 8 database worked perfectly
n> BUT
n> some PC moved to win XP and
n> we had to use Oracle 9 client , so
n>
n> VB5/MDAC 2.0/Oracle 9 client ODBC/Oracle 9 Client/Oracle 8 database
n> does not correctly access
n> decimal numbers:decimal numbers fetched from the database are either
n> truncated (35.48 -> 35.00) or
n> multiplicated (35 ->3500).
n>
n> A similar (not exactly the same) problem occurs with crystal report.
n>
n> We tried to analyze things with crystal:
n> Crystal/oracle driver 7 for odbc -> OK
n> Crystal/Oracle OLE DB driver 9 ->OK
n> Crystal/Oracle 9 ODBC driver -> NOT OK
n>
n> We thought of an NLS (france) problem ( , <-> .) and changed things
n> on the server and/or client:
n> nothing changed.

Unfortunately, Oracle 8 (and 9 I think) ODBC drivers behave as normal Oracle
clients respecting NLS settings while converting decimal numbers to/from
strings. This does not conform to the ODBC specification which states dot
(.) as a decimal separator.

Some MDAC/Crystal versions seem to take this into account, but others may
not.

Possible workarounds:
(1) Try execute "Set NLS_..." statement after connecting but before any
queries.
(2) Provide correct input string or parse output string accordingly.
(3) Bind columns as SQL_C_FLOAT or SQL_C_DOUBLE.

Though, if you use VB and DAO, (2) and (3) are hardly available.
--
HTH,
Dmitri Ivanov
Common Lisp ODBC interface - www.ystok.ru

[Kingsley Idehen]

In addition to Dimitri's suggestions, you can look at other ODBC Drivers for Oracle. Our ODBC Drivers for Oracle do not have this problem and they are obtainable on a free 30 day evaluation basis from: http://www.openlinksw.com
Regards,
Kingsley Idehen
OpenLink Software http://www.openlinksw.com/blog/~kidehen

[Rolf Beier]

We have a similar problem as j bratieres wrote. We use an Oracle database (Vers. 9) and PC's with W2K as operating system. The most actual servicepacks and updates are installed (Including the actual version of the jet engine. Office 2000 is installed on all of the PCs (Servicepack 1a and in some cases SP3).
We have a frontend program designed with MS Access 2000 which collects data from different databases (partly MS Access database, partly Oracle (8.1.6 and 9) via MS ODBC for Oracle (Versions all around 2.573.9001.00).
With the most PC's our application runs well, but there are some PC's which don't do regarding the correct access to decimal numbers. We can fix this problem with the country settings in the control set, changing the decimal delimiter from
, (comma) to . (dot). Then the application works fine. But thats no solution, because this change is relevant for all other programs to (Excel ...). Another bugfix, which worked on some PC's was to uninstall Office 2000 and delete all "Office" registry entries in the software section of the current user and local machine. Then it worked again after reinstallation of Office 2000.
But now, we got some new DELL computers, preinstalled with Windows 2000. I finished the preinstallation of the operating system, installed Office 2000, the Oracle client and several other applications. But the problem came up again.
We have no solution jet, but we think there must be a registry entry or an entry in an ini-file which decides about a correct or uncorrect handling of the decimal delimiter.
(Using third party ODBC-drivers may be a solution, if time and money does not matter, but it's not a solution for us.)
Can anybody help ??