Re: Problems with decimals in an APPEND FROM using Oracle 8i

From: Igor Korolyov (k1i2v3_at_km.ru)
Date: 03/18/04


Date: Fri, 19 Mar 2004 01:28:18 +0200

Hi, Anders!
You wrote on Thu, 18 Mar 2004 12:16:52 +0100:

AA> What decimal separaorors are used nationally, besides point or comma?

Hmm, What do you mean? Our Oracle server uses comma as separator, VFP
expects point to delimit decimal part. I mean not for display purpose - it
managed by SET POINT, but for internal storage - in DBF you _always_ will
have point as separator, if you accidentally change point to comma in DBF
(Say with HexEdit, or some other tool) - then you'll see abovementioned
problems - BROWSE will display data in "correct way", but when you'll try to
refer to these fields - you'll loose decimal part. The problem is that
sometimes ODBC send "comma separated" numbers, not "point separated" - so
VFP can do nothing with it (besides low-level access to DBF and replacing
commas with points in numeric fields). The solution is to change SQL server
to sent "point-separated numbers", or ODBC - to convert commas to points
while transfering results from server to client. MS Driver usually do this
stuff (at least the newest one) - Oracle ODBC driver don't bother...

2 Rodolfo
After several tests I found that this command will help:

SQLEXEC(m.nODBCHandle, "ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '. '")

It will turn Oracle to send "point-separated" numeric fields - that is OK
for VFP.
If you want to use Remote Views, and if you use single (shared) connection
to oracle - you may open this connection before really using RemoteViews -
and sent above command to server - it will affect only this particular
connection. If you don't use shared connections (BTW why not - IMHO it is
bad idea to create many connections from single program - DBA may kill you
if you'll do so :)) - then you have to USE RV_Some NODATA - then get
connection handle as CURSORGETPROP("ConnectionHandle") - sent command and
call REQUERY("RV_some") - it will retreive data in correct format.

-- 
WBR, Igor