VO/ORACLE/ODBC
Meinhard Schnoor-Matriciani
I am struggling on a problem using VO's SQL classes in conjunction with our
Oracle database. Looks like there is a different behaviour using
SQLStatement with a dynamical generated insert statement ( that meens a new
statement for every row ) and using a prepared statement. In case of a
prepared statement, Oracle refuses to accept floating values, everything
else works fine. Now, i've been heavily debugging this stuff and came around
the following reason. It looks like the ODBC driver converts the decimal
point into a comma automatically. I assured that there is a point in the
passed string because Oracles decimal seperator is a point. Anyway this
doesn't work. As soon as I exchange the '.' with a ',' everything goes well.
I played around with the regional settings of my 2000 installation but seems
to have no effect. Anybody out there who had the same problem and found a
way to work around that ?
TIA
Meinhard
Jamal
Try using:
to_number(string) to convert strings in standard formats, or
to_number(string, mask) to convert strings with special formats
HTH,
Jamal
"Meinhard Schnoor-Matriciani" <Mein...@NewYorker.de> wrote in message
news:1008605234.260996@beavis...
Meinhard Schnoor-Matriciani
thx for your interest. This is not the problem here. Imagine the following
statement
local i as int
local cStmt as string
local oStmt as SQLStatement
cStmt := "insert into salefigures ( pk , price ) values ( ? , ? ) "
oStmt := SQLStatement{ cStmt , oConnection }
i := 0
do while ! ...
oStmt:execute( i , 10.12 )
i += 1
.....
enddo
Know what i mean ?
Regards,
Meinhard
Jamal
changing language and country setting to US and see what happens.
Jamal
"Meinhard Schnoor-Matriciani" <Mein...@NewYorker.de> wrote in message
news:1008621582.151834@beavis...
Geoff Schaller
I'm pretty sure that doesn't happen here - and the reason, I think, is
simply international settings. Although we are only playing with SQL Server
(not Oracle) I have heard of similar issues during our last conference.
Robert and others were demonstrating conversions with VO2ADO and 2 German
delegates (who did use Oracle) wanted to demonstrate a very similar issue
and couldn't. Everything worked <g>. The reason turned out to be
international settings because here, we use "." as the decimal separator.
Its only a long shot but try resetting to UK, US or Australia, including the
keyboard, and see what happens.
I guess, however, the eventual issue is how to compensate when running under
your own international settings.
Geoff
"Meinhard Schnoor-Matriciani" <Mein...@NewYorker.de> wrote in message
news:1008605234.260996@beavis...
Stephan Frigge
>[...] Now, i've been heavily debugging this stuff
>and came around the following reason. It looks like the ODBC driver
>converts the decimal point into a comma automatically. I assured that
>there is a point in the passed string because Oracles decimal seperator
>is a point. Anyway this doesn't work. As soon as I exchange the '.' with
>a ',' everything goes well. I played around with the regional settings
>of my 2000 installation but seems to have no effect. Anybody out there
>who had the same problem and found a way to work around that ?
if your problem is ODBCrelated you may find an answer on a Database-
Newsgroup.
Try: de.comp.datenbanken.misc
Regards Stephan
--
Stephan Frigge
www.XBaseGroup.de
German Visual-Objects Newsgroup:
VisualObjects.ger on news.visual-objects.net
Meinhard Schnoor-Matriciani
you're right and you're not right. <g>
We finally found the problem and a workaround. The kernel of the problem was
that they guy who wrote the SQL classes uses __str() instead of str().
__str() is a regional independent variant of str() and uses always '.' as
decimal seperator. This is no problem as long as the program runs in the
US/UK/... environment. But in a german environment the ODBC driver wants to
exchange the decimal seperator in a certain way ( I couldn't find out which
way <g> ) and that's the problem. Anyway, we found a workaround, but I am
still confused.
Regards,
Meinhard
Arne Ortlinghaus
perhaps it is not the ODBC-Driver but Oracle itself.
Oracle has the possibility to change the behaviour of
date/string/number-representation for the database or the actual session.
Perhaps it is working if you send the statement
ALTER SESSION set NLS_NUMERIC_CHARACTERS = ".,"
after initializing the session.
Arne