Executing Stored Procedures with Data Direct ODBC Driver

Abstract: Executing Stored Procedures with Data Direct ODBC Driver

Note this issue is resolved in version 5.10.00.23 of the Data Direct ODBC driver. (This is the latest version, downloadable from the Registered Users download site)



Stored Procedures in general have 2 purposes. 1 - Do something. 2 - Return a result set. InterBase has two different API's to achieve these two different purposes. To do something, you execute a stored procedure. If you execute a stored procedure against a stored procedure that returns a result set, it returns only one row. To return a result set, you select from the stored procedure, just as if it was a table or view.

Typically reporting tools such as Crystal Reports and Brio Query use the ODBC CALL syntax to return a result set. However, developers using the ODBC API need the ODBC CALL syntax to execute a stored procedure. The ODBC specification does not specify which action to take when CALL is executed. Clearly InterBase users need to have both available to them.

The EasySoft ODBC driver makes this distinction via a check box labeled "Execute Proc" when setting up a DSN. When it is not checked (the default), CALL returns a result set. When it is checked, the stored procedure is executed.

The CALL syntax for the Data Direct ODBC driver ONLY executes a Stored Procedure at this time. It does not return a result set. [b]To return a result set using the Data Direct ODBC driver, you must use the SELECT Syntax at this time.[/b] Unfortunately, it appears some reporting tools do not have this as an option. It also means if you convert a report from the EasySoft ODBC driver to the Data Direct ODBC driver, your report will return only 1 row.

Borland has contacted Data Direct (8/30/6) and raised this as a high priority incident. We have requested Data Direct to provide a mechanism for the ODBC CALL syntax to both execute a stored procedure and return a result sets. We hope to have a new and improved driver from Data Direct shortly.