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
Any comments on this article? Email Me
|