RDO (forget prev, pls)

[thegios]

Please look at this code.
I have to connect to an Oracle8 DB with odbc to call a poarametricstored procedure (in my case a function accepting a string and returning an integer). The SP exists in the DB and works well if called from SQL*PLUS. For the connection I use RDO.


'first of all I define the variables for the connection
Dim Cn as new rdoConnection 'the RDO object for connection
Dim Qy as new rdoQuery 'the RDO object for the query
Dim rs as rdoResultset 'the RDO object for the result recordset
Dim str as string 'the string for the query

'then I set the variables and establish the connection
With Cn
.Connect = "DSN=ZZZ; UID=XXX ; PWD=YYY;"
'ZZZ is the DSN registered on the NT client in the ODBC driver section
.EstablishConnection rdDriverNoPrompt, false
End With

'till here no problem, the connection is established

'I then set the active connection for my query object
set Qy.ActiveConnection = Cn

'the string representing the query should have the following format, where the "?" takes the
' place of each parameter
str = "{? = Call stored_procedure_name (?)}"
'I set str as the query for the query object
Qy.sql = str

'now I should set the properties and the values of the params, i.e. I have to set the first parameter
' (the first "?") as a returned value, and then set the value for the passed parameter
Qy.rdoParameters(0).Direction = rdParamReturnValue
Qy(1) = "xyx"

'finally I have to execute the query and colse the recordset and the connection
set rs = Qy.OpenResultset()
rs.Close
Cn.Close

End Sub





What's wrong is the settings of the SP params; when executing the line

Qy.rdoParameters(0).Direction = rdParamReturnValue



VB returns an error, saying that there are no elements in the collection rdoParameters; that is it seems that the characters ? are not recognbised as parameters for the SP. If I print the number of parameters of the query object (Qy.rdoparameters.Count) it is 0 !!!!!!!
Should the SP not return a value (that is, it's a procedure and not a function) I can set str as follows


Dim inputvar as string
inputvar = "XXXX"
str = "{Call stored_procedure_name ('" & inputvar & "')}"




I tried that, and it works!!!!! So it seems the problems are the "?"s.
please help me. thnx.

I've seen things u ppl couldn't believe...