December 30, 2009 - WinTask - Using ODBC Functions with an Oracle Database

Some Oracle datatypes are not directly supported by WinTask.

For example you cannot use DbGetFieldNumeric() with Oracle Number fields.  When you try, you will get an execution error that indicates the field is not a numeric field:

Error at line 13 : XXX is not a numeric field

WinTask Tech Support indicates that they don't support this type of numeric field.

Here are some Oracle datatypes and how to deal with them:

Char(x)

DbGetFieldString()

Date

DbGetFieldString()

Float

DbGetFieldString()

Then, use ToInteger() or ToNumber() below to convert to a number

Integer

DbSelect("select CAST(MyCol as VARCHAR2(x)) from MyTable t",SNAPSHOT)

DbGetFieldString("CAST(MYCOLASVARCHAR2(x))",val$)
Then, use ToInteger() or ToNumber() below to convert to a number

Number

DbGetFieldString()
Then, use ToInteger() or ToNumber() below to convert to a number

Number(x)

DbSelect("select CAST(MyCol as VARCHAR2(x)) from MyTable t",SNAPSHOT)
DbGetFieldString("CAST(MYCOLASVARCHAR2(x))",val$)
Then, use ToInteger() or ToNumber() below to convert to a number

Number(x,y)

DbSelect("select CAST(MyCol as VARCHAR2(x)) from MyTable t",SNAPSHOT)
DbGetFieldString("CAST(MYCOLASVARCHAR2(x))",val$)
Then, use ToInteger() or ToNumber() below to convert to a number
Note: Will lose trailing zeros (e.g. 1.000 -> "1" and 2.220 -> "2.22")

NVarChar2(x)

DbGetFieldString()

RAW(x)

DbSelect("select CAST(MyCol as VARCHAR2(y)) from MyTable t",SNAPSHOT)  'where y = x*2
DbGetFieldString("CAST(MYCOLASVARCHAR2(y))",val$)

TimeStamp()

DbGetFieldString()

VarChar2(x)

DbGetFieldString()

Some helper functions:

Function ToNumber(test$)
    Local i
    Local new$
    Local work$
    work$=""
    i=1
    While i<= len(test$)
        Select Case Mid$(test$,i,1)
            Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9"
                new$=Mid$(test$,i,1)
            Case Else
                new$=""
        EndSelect
        work$=work$+new$
        i=i+1
    Wend
    ToNumber=Val(work$)
EndFunction

Function ToInteger(test$)
    Local i
    Local new$
    Local work$
    work$=""
    i=1
    While i<= len(test$)
        Select Case Mid$(test$,i,1)
            Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9"
                new$=Mid$(test$,i,1)
            Case "."
                new$=""
                i=len(test$) 
            Case Else
                new$=""
        EndSelect
        work$=work$+new$
        i=i+1
    Wend
    ToInteger=Val(work$)
EndFunction