Database Procedure Execution
If the database procedure has no parameters, the procedure can be executed in a straightforward manner using SQLExecDirect():
SQLExecDirect( hstmt, "execute procedure myDbProc", SQL_NTS );
If the database procedure requires parameters, ODBC "escape sequence" syntax must be used. The ODBC uses escape sequence syntax to signify to the ODBC Driver Manager that implementation of the syntax in question is to be performed in a way that is specific to the driver.
The general form of escape syntax for database procedures is:
{ retcode = call dbproc [ ( ? ) [ , ( ? ) ... ] }
The ODBC Driver supports the following Ingres database procedures:
• Input parameters
• BYREF parameters
• Returned rows
• Procedure return values
Database Procedures that Return Values
SQLBindParameter() binds parameters for database procedures, just as for other types of queries. The following example executes a procedure that has no input parameters and returns an integer value:
SQLINTEGER retval = 500;
SQLINTEGER orind = 0;
SQLBindParameter( hstmt, /* Statement handle */
1, /* Parameter number */
SQL_PARAM_OUTPUT, /* It's an output parameter */
SQL_C_LONG, /* Source data is an integer */
SQL_INTEGER, /* Target column is an integer */
0, /* Length not required */
0, /* Precision not required */
&retval, /* The data itself */
0, /* Max length not required */
&orind1); /* Indicator can be zero */
SQLExecDirect( hstmt, "{ ? = call myDbProc () }", SQL_NTS );
The value returned from the procedure "myDbProc" is returned in the integer "retval" after the procedure is executed. Note that the third argument, ParameterType, is designated as SQL_PARAM_OUTPUT.
Database Procedures with Input Parameters
Input parameters are sent to the database procedure but not returned to the application. The following example shows how input parameters are used:
SQLINTEGER retval = 500;
SQLINTEGER orind = 0;
SQLBindParameter( hstmt, /* Statement handle */
1, /* Parameter number */
SQL_PARAM_INPUT, /* It's an input parameter */
SQL_C_LONG, /* Source data is an integer */
SQL_INTEGER, /* Target column is an integer */
0, /* Length not required */
0, /* Precision not required */
&inputVal, /* The data itself */
0, /* Max length not required */
&orind1); /* Indicator can be zero */
SQLExecDirect( hstmt, "{ call myDbProc ( ? ) }", SQL_NTS );
Note that the ParameterType argument for "inputVal" is now SQL_PARAM_INPUT. The parameter marker "?" is now designated as an input parameter to myDbProc by placing it within the parentheses after myDbProc.
Database Procedures with BYREF Parameters
BYREF parameters can be used for both input and output. The following example is almost the same as the Input Parameters example, but with one exception:
SQLBindParameter( hstmt, /* Statement handle */
1, /* Parameter number */
SQL_PARAM_INPUT_OUTPUT, /* It is a BYREF parameter */
SQL_C_LONG, /* Source data is an integer */
SQL_INTEGER, /* Target column is an integer */
0, /* Length not required */
0, /* Precision not required */
&byRefval, /* The data itself */
0, /* Max length not required */
&orind1); /* Indicator can be zero */
SQLExecDirect( hstmt, "{ call myDbProc ( ? ) }", SQL_NTS );
Since this procedure handles BYREF parameters, the call to SQLExecDirect() can begin with a value of 500 for the "byRef" variable, but return with any valid integer value, such as -1.
Database Procedures that Return Rows
No special parameter treatment is required for database procedures that return rows. SQLBindParameter() can be used for return values, input parameters, and BYREF parameters as before, regardless of whether rows are to be returned.
The following example shows a procedure that returns rows but has no input parameters:
/* Create the row-returning procedure. */
SQLExecDirect( hstmt, "create procedure retRow result row " \
"( varchar(20) ) as declare pmodel = varchar(20) not null; " \
"begin for select model into pmodel from cars do " \
"return row( pmodel ); endfor; end" ), SQL_NTS );
/* Execute the procedure. */
SQLExecDirect( hstmt, "{ call retRow () }", SQL_NTS );
/* Fetch the result data. */
SQLFetch( hstmt );
Batch Execution
Database procedures execute a set of queries on the database server. This is server-based batch execution. The ODBC Driver allows sets of queries to be defined and executed in an application; this is client-based batch execution.
Explicit Batch Execution
Explicit batch execution can be considered an alternative to database procedures. In explicit batch execution, a set of CREATE, DELETE, UPDATE, or EXECUTE PROCEDURE queries are chained together in a single SQLExecDirect() statement:
[ Allocate handles and connect. ]
SQLExecDirect(hstmt, "insert into cars( model ) values( 'Hummer' ); " \
"update cars set model = 'Altima' where model = 'Mustang' ); " \
"insert into cars( model ) values( 'Camray' ", SQL_NTS;
Explicit batch execution supports dynamic parameters. The parameters are bound with the column number 1 to N as if the batch were a single statement:
SQLCHAR model[3][21] = { "Hummer", "Mustang ", "Camray " };
int i;
SQLINTEGER orind = SQL_NTS;
[ Allocate handles and connect. ]
SQLExecDirect(hstmt, "insert into cars( model ) values( ? ); " \
"update cars set model = 'Altima' where model = ?; " \
"delete from cars where model = ? ", SQL_NTS;
/*
** Note that the column number increments from 1 to 3.
*/
for ( i = 0; i < 3; i++ )
{
SQLBindParameter( hstmt, /* Statement handle */
i+1, /* Column number 1 */
SQL_PARAM_INPUT, /* This is an input parameter */
SQL_C_CHAR, /* This is a string in C */
SQL_VARCHAR, /* Destination column is varchar */
strlen( model[i] ), /* Length of the parameter */
0, /* No scale specifier */
model[i], /* The data itself */
0, /* Maximum length (default 0) */
&orind ); /* Null-terminated string */
}
Limitation of Explicit Batch Queries
Since Ingres does not support multiple result sets, select queries are not allowed in explicit batches. Likewise, row-returning database procedures are not supported. Database procedures that include BYREF or output parameters are not supported in explicit batch.
Batch Execution using Parameter Arrays
A single statement that uses dynamic parameters can be effectively executed multiple times using parameter arrays. An ODBC application can bind rows of data or columns of data, called row-wise and column-wise binding, respectively. The following example uses column-wise binding:
SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_BIND_TYPE, SQL_PARAM_BIND_BY_COLUMN, 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)nbrInserts, 0);
SQLExecDirect(hstmt, "insert into cars( model ) values( ? ); ", SQL_NTS;
/*
** Note that the column number is numbered 1 through n.
*/
for ( i = 0; i < 3; i++ )
{
SQLBindParameter( hstmt, /* Statement handle */
1, /* Column number 1 */
SQL_PARAM_INPUT, /* This is an input parameter */
SQL_C_CHAR, /* This is a string in C */
SQL_VARCHAR, /* Destination column is varchar */
strlen( model[i] ), /* Length of the parameter */
0, /* No precision specifier */
model[i], /* The data itself */
0, /* Maximum length (default 0) */
&orind ); /* Null-terminated string */
}
We strongly recommend the use of parameter arrays, which can result in dramatically improved performance if the database server is Ingres 10.0 or later.