Compile Function Oracle_Connect(CharStr Username)
* Oracle connection
* call SQL_Error to look for errors after using this function
$Insert Logical
$insert Microsoft_Ado_Equates

DataSource = "DEV"
pwd = "alpha"
ConnectionString = "Provider=OraOLEDB.Oracle; User id=Access;Data Source=" : DataSource : ";Password=" : pwd : ";"
oConn = OleCreateInstance("ADODB.Connection")

oConn->ConnectionString = ConnectionString
x = oConn->Open()

Return oConn

** to check for ADO errors after any call

Compile function SQL_Error(connDB)
declare subroutine msg
eDescription = ""
if OleStatus() <> 0 then
Errors = connDB->Errors
eCount = Errors->Count
eNumber = ""
For ev = 1 To eCount
this_error = OLEGetProperty(Errors,"ITEM",ev-1)
eNumber<1,ev> = this_error->Number
eDescription<1,ev> = this_error->DESCRIPTION
Next ev
end
return eDescription

*** Oracle storeD procedure call
** Note: couldn't get ADdate type to interpret, so used varchar
** gave up trying to return a record set from an SP as well - query works though (see below)

Compile function RATE_ORACLE(yadda,yadda,yadda...
declare subroutine msg
Declare Function Oracle_Connect, SQL_Error
$insert Microsoft_Ado_Equates
$Insert Oracle_Common

Rates = ""
errMsg = ""
connDB = Oracle_Connect(@USERNAME)
ErrMsg = SQL_Error(connDB)
end
if ErrMsg = "" then
Gosub GetRates
end
rates<1> = errmsg
x = connDB->Close()
connDB = ""


return Rates



GetRates:

cmd = OleCreateInstance("ADODB.Command")
OlePutProperty(cmd, 'ActiveConnection', connDB )
OlePutProperty(cmd, 'CommandType', adCmdStoredProc )
OlePutProperty(cmd, 'CommandText', "XXX.TEST_pkg.Calc" )
params = cmd->Parameters
* temporary patch until adDate or adDBDate gets working
Date_conv = Oconv(Idate,'D4E')
Convert " " to "-" In Date_conv
param = oleCallMethod(cmd,'CREATEPARAMETER','pi_Eff_Date',adVarChar,adParamInput,Len(date_conv),Date_conv)
x = oleCallMethod(params,'APPEND',param)
param = oleCallMethod(cmd,'CREATEPARAMETER','pi_Zip',adVarChar,adParamInput,Len(Zip),Zip)
x = oleCallMethod(params,'APPEND',param)
param = oleCallMethod(cmd,'CREATEPARAMETER','pi_Class',adDouble,adParamInput)
OlePutProperty(param,"Value",Rate_Class/10)
x = oleCallMethod(params,'APPEND',param)
param = oleCallMethod(cmd,'CREATEPARAMETER','pi_Weight',adDouble,adParamInput)
OlePutProperty(param,"Value",Tot_weight)
x = oleCallMethod(params,'APPEND',param)
param = oleCallMethod(cmd,'CREATEPARAMETER',"po_Rate_Base_No",adDouble,adParamOutput)
x = oleCallMethod(params,'APPEND',param)
param = oleCallMethod(cmd,'CREATEPARAMETER',"po_AddOn_Factor",adDouble,adParamOutput)
x = oleCallMethod(params,'APPEND',param)
param = oleCallMethod(cmd,'CREATEPARAMETER',"po_Territory",adVarChar,adParamOutput,3)
x = oleCallMethod(params,'APPEND',param)
param = oleCallMethod(cmd,'CREATEPARAMETER',"po_Rate",adDouble,adParamOutput)
x = oleCallMethod(params,'APPEND',param)

x = cmd->Execute()
ErrMsg = SQL_Error(connDB)
If ErrMsg = "" then
For opv = 1 To 4
param = oleGetProperty(params,"Item",opv+8)
Rates<1+opv> = param->value
Next opv
Rates<5> = rates<5> * 10000
end
cmd = ""
param = ""
params = ""

Return

** Return a Record Set from a Query

GetUserProperties:

Row = ""
UserRecord = ""
Script = "SELECT A, B, C, D, F, E FROM DUAL"
affected = 0
rs = ConnDB->Execute(Script, affected,1)
Errmsg = LTL32_SQL_Error(ConnDB)
If Errmsg = "" then
rCount = 0
fields = rs->Fields
x = rs->MoveFirst()
loop Until rs->Eof or OleStatus() <> 0
rcount += 1
For iv = 1 To 7
item = OleGetProperty(Fields, 'ITEM', iv-1)
UserRecord<iv> = item->value
Next iv
For iv = 8 To 10
item = OleGetProperty(Fields, 'ITEM', iv-1)
UserRecord<iv,rcount> = item->value
Next iv
x = rs->MoveNext()
repeat
end
x = OleCallMethod(rs, 'Close')
rs = ""

Return