Microsoft KB Archive/158197

= SAMPLE: ODBCBIND.EXE RDO: Using ODBC to Bind Parameters to Stored Procedures =

Article ID: 158197

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft Visual Basic 6.0 Learning Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 4.0 32-Bit Enterprise Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition

-



This article was previously published under Q158197



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

or

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.



MORE INFORMATION
Note Odbcbind.exe requires the installation of Visual Basic 4.0, 5.0, or 6.0 Enterprise Editions on Windows 2000, Windows NT or Windows.

The following files are available for download from the Microsoft Download Center:

Odbcbind.exe

For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:

119591 How to Obtain Microsoft Support Files from Online Services

Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.

