Conversion Problem Decimal Sep. with ADO

[Dr. Johannes Grabsch]

I find the following problem:

A Server runs an ORACLE DB, where certain numbers are stored as strings
with Comma as the decimal separator (this is in Germany).

I have client maschines with "English" Settings, i.e. decimal sep. is a
Point.

Now, if I run a Query from VBS like:

SET cn = CreateObject("ADODB.Connection")
cn.Open "DSN=...........

SQLStm = "SELECT SPOOL, I02_TIME, SPMASS, SPAMASS, SPMMASS, SPTMASS,
SPDMASS " & _
"FROM ARC_REP_SUPERPOOL " & _
"GROUP BY SPOOL, I02_TIME, SPMASS, SPAMASS, SPMMASS, SPTMASS,
SPDMASS " & _
"HAVING I02_TIME > " & DATUM_FROM & "ORDER BY I02_TIME;"

Set rs = CreateObject ("ADODB.Recordset")
rs.Open SQLStm, cn, adOpenForwardOnly, adLockReadOnly

...

WHILE NOT rs.EOF
SPOOL = rs("SPOOL")
I02_TIME = rs("I02_TIME")
SPMASS = rs("SPMASS")
SPAMASS = rs("SPAMASS")

etc.

I only get numbers with the decimal fractions of the original numbers
being part of the pre-decimal part.

Example:

123,4567 on server will be 1234567 and 3,141592654 is 3141592654

This is a desaster!

How can I fix it without rebuilding or reconfiguring all the systems,
which would be out of the question anyhow.

N.B:

doing something like

CDbl(rs("SPAMASS")) does NOT help.

It is the incoming data item, that does not contain any decimal
separator.

If I do

SPMASS = CDbl(rs("SPMASS"))
NewNumber = CDbl(replace(rs("SPMASS"),",","."))
call Write_To_Log(SPMASS & " " & vartype(rs("SPMASS")) & " " &
vartype(SPMASS) & " " & NewNumber)

2998339 14 5 2998339

The number in Oracle is actually

29983,39

If I query in Access I see this

29983.39

What is the problem?

TIA for any suggestions or help.

Johannes Grabsch

[Egbert Nierop]

the oracle numeric fields are like variants that can either be strings.

"Dr. Johannes Grabsch" <Johannes....@OPE.SHELL.COM> wrote in message
news:39110B...@OPE.SHELL.COM...

[Dr. Johannes Grabsch]

Hallo Christian

Thanks for the answer, but this does not help.

Whatever the contents of rs("SPOOL") is, it does not contain ANY decimal
character here. Also I should point out that the ADO call is in a VBS
context.

Even if I change your function

Public Function fctCdbl(strData As String) As Double
fctCdbl = Left(strData, InStr(strData, ",") - 1) & Mid(Format(0,
"standard"), 2, 1) & Mid(strData, InStr(strData, ",") + 1)
End Function

to

Public Function fctCdbl(strData)
fctCdbl = Left(strData, InStr(strData, ",") - 1) & Mid(Format(0,
"standard"), 2, 1) & Mid(strData, InStr(strData, ",") + 1)
End Function

it fails, as a missing Comma (then InStr(strData, ",") is zero) would
result in a
Left(strData, - 1) and
this is illegal.

The core of the problem must be in the ODBC Driver of ADO to Oracle, I
think it ignores the Comma and does lump together all the digits in the
source string

from 29983,39 to 2998339

Later it is impossible to find back the true position of the decimal
separator.

The Field Type in ORACLE is VarChar2, where the ADO equivalent is
adVarChar.
How can I tell ADO that these fields are of type adVarChar ?

Johannes

Christian Straven send me the following message:

Hi,
Maybe you could use the following function :

Public Function fctCdbl(strData As String) As Double
fctCdbl = Left(strData, InStr(strData, ",") - 1) & Mid(Format(0,
"standard"), 2, 1) & Mid(strData, InStr(strData, ",") + 1)
End Function

So

SPOOL = rs("SPOOL")

must be changed to
SPOOL =fctCdbl( rs("SPOOL"))
I know this is tricky but that's what I use in Belgium
Mid(Format(0, "standard"), 2, 1) returns the character used as decimal
separator

Hope this will help
Chris

[Egbert Nierop]

Sorry,

Today I was answering your message and I did not come further than the
Variant message because of computers all around got infected by the I love
you virus...

have a look at this article:
http://support.microsoft.com/support/c.asp?FR=0 and find the article Q235553

It might help...

If you stick to this, try the Replace function. It's much faster...

Public Function fctCdbl(strData As String) As Double

fctCdbl = Replace(StrDate, ",", "")
End Function

Is the oracle field a Decimal type? Then try to use a CDec() function...

"Dr. Johannes Grabsch" <Johannes....@OPE.SHELL.COM> wrote in message

news:3911A6...@OPE.SHELL.COM...

Rs.Fields("SPOOL").Type = adVarChar

But this might not work because it is read only data...