SUMMARY
This article describes how to use the ODBC API to bind input, output, and
return values to parameters of stored procedures. This is a workaround
using the
rdoPreparedStatement object to accomplish this behavior.
RDO has problems binding parameters to an
rdoPreparedStatement if your ODBC
driver does not correctly support the
SQLDescribeParam and
SQLProcedureColumns functions.
RDO depends on information returned from these functions to determine the
type, direction, and size of each parameter in a stored procedure. If any
of this information is missing or invalid, RDO fails with one of the following error
messages:
Invalid parameter value
Illegal syntax or access violation
RDO generates the error the first time you access the
rdoParameters
collection (check the
Count property or try to get an item from it),
because RDO calls
SQLDescribeParam or
SQLProcedureColumns and attempts to
build the parameters collection at that time.
Note RDO uses
SQLProcedureColumns if the SQL uses the ODBC {Call}
syntax and
SQLDescribeParam for all others, such as the proprietary Execute
for SQL Server.
One way to avoid this problem is to pass the input parameters to your
stored procedure "in-line," without using an
rdoPreparedStatement. The
following line of code demonstrates this:
Set rs = cn.OpenResultset("{call GetNames ('Jones')}")
Note Oracle 7.2 cannot return a result set from a stored procedure.
In the case of this sample, a stored procedure named
GetNames received one
input parameter with the value of "Jones," and should return a
result set with each row from the table that contained that value in a
certain column. This works fine for a stored procedure that only accepts
input parameters, but it is not useful for a stored procedure that returns
output parameters or return values.
To work around this problem, a class module has been developed that uses the ODBC API to call your parameterized stored procedure
without the use of
SQLDescribeParam or
SQLProcedureColumns. This class
module does not have the ability to return a result set from the stored
procedure, but you can set multiple input, output, and return values. This
sample,
Odbcbind.exe, is available for download as a class module that you
can include in your application or compile into an OLE DLL.