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

The information in this article applies to:

• Microsoft Visual Basic Enterprise Edition, 32-bit only, for Windows, version 4.0

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:

• "Invalid parameter value"

  -or-

• "Illegal syntax or access violation"

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')}")

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 95.

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

 ~ Odbcbind.exe (size: 25510 bytes) 

   ARTICLE-ID: Q119591
   TITLE     : 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.