SAMPLE: RDO: Using ODBC to Bind Parameters to Stored Procs

ID: Q158197

The information in this article applies to:

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 SQLDescribeParam and SQLProcedureColumns. 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 will fail with the error message:

RDO will generate 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 will use 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 presumably will 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, Microsoft Technical Support developed a class module 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 is available for download as Odbcbind.exe as a class module that you can include in your application or compile into an OLE DLL.


MORE INFORMATION

Odbcbind.exe requires the installation of Visual Basic 4.0 Enterprise Edition on Windows NT or Windows.

The following file is available for download from the Microsoft Software Library:

~ Odbcbind.exe

For more information about downloading files from the Microsoft Software Library, please see the following article in the Microsoft Knowledge Base:

Q119591 : How to Obtain Microsoft Support Files from Online Services


REFERENCES

Open Database Connectivity (ODBC) SDK, version 2.10, "Programmer's Reference," on the Microsoft Developer Network (MSDN) CD-ROM.

"Hitchhiker's Guide to Visual Basic and SQL Server," William R. Vaughn, Microsoft Press, April 1996, ISBN: 1-55615-906-4.

Additional query words: kbVBp400 kbVBp600 kbWinOS98 kbVBp kbDSupport kbdse kbRDO kbODBC 


Keywords          : 
Version           : 
Platform          : WINDOWS 
Issue type        :

Last Reviewed: May 26, 1999