Query Execution
This section explores methods for executing queries in ODBC applications.
SQLExecDirect()--Execute Queries Directly
SQLExecDirect() is commonly used for executing queries. The following code snippet creates a table and inserts a row of data.
Example: SQLExecDirect() Function
/* Allocate a statement handle. */
SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt );
/* Execute a "create table" query. */
SQLExecDirect(hstmt, "create table cars( model varchar(20) )",
SQL_NTS);
/* Insert one row of data. */
SQLExecDirect(hstmt, "insert into cars ( model ) values
( 'Hummer' )", SQL_NTS);
SQLPrepare() and SQLExecute()--Prepare and Execute Queries
For queries that are executed multiple times, preparing the queries before execution may improve performance because the DBMS stores the query plan of prepared queries. Rather than calculating a query plan each time a query is executed, the DBMS references the query plan for all subsequent iterations of the query.
SQLPrepare() and SQLExecute() perform the prepare and execute functions, respectively.
Unless the ODBC application explicitly prepares a query via SQLPrepare(), the ODBC always executes queries directly; there is no provision for automatically preparing queries in Ingres.
The following code snippet is the equivalent of the SQLExecDirect() code example in the Execute Queries Directly section.
Example: SQLPrepare() and SQLExecute() Functions
/* Prepare a "create table" query. */
SQLPrepare( hstmt, "create table cars( model varchar(20) )",
SQL_NTS );
/* Execute the prepared query */
SQLExecute(hstmt);
Prepared queries persist in the default autocommit state. However, if you turn off autocommit via SQLSetConnectAttr(), the query plan associated with a prepared query is destroyed whenever the query is rolled back or committed. You must then re-prepare the query via SQLPrepare().
Queries with Dynamic Parameters
ODBC applications frequently require data to be sent to the database dynamically. Such data cannot be hard-coded in the query itself.
For comparison, the following set of queries inserts three rows with hard-coded parameters:
insert into cars( model ) values( 'Hummer' );
insert into cars( model ) values( 'Mustang' );
insert into cars( model ) values( 'Camray' );
The above set of queries would be adequate if it was known in advance that the target table would always receive this data. Often this is not the case, so the data must be sent dynamically. The ODBC uses question marks ("?") as placeholders for dynamic queries:
insert into cars( model ) values ( ? );
A character string can be defined and bound to the query. Binding a parameter to a query means that the data is sent as a parameter along with the query. The DBMS reconstructs the parameter data with the query as if the data were hard-coded in the original query.
To send data to the DBMS, the ODBC must tell the DBMS what the data looks like. The ODBC function SQLBindParameter() serves that purpose. The following example allows an ODBC application to send one row of data dynamically to a table consisting of an integer and a varchar of length 20:
SQLCHAR model[21] = "Mustang ";
int lotNbr = 124;
SQLINTEGER orind1 = 0, orind2 = SQL_NTS;
[ Allocate handles and connect. ]
SQLExecDirect( hstmt, "insert into cars( model ) values( ? )", SQL_NTS );
SQLBindParameter( hstmt, /* Statement handle */
1, /* Column number 1 */
SQL_PARAM_INPUT, /* This is an input parameter */
SQL_C_LONG, /* This is an integer in C */
SQL_INTEGER, /* Destination column is varchar */
strlen( model ), /* Length of the parameter */
0, /* No scale specifier */
model, /* The data itself */
0, /* Maximum length (default 0) */
&orind1 ); /* Null-terminated string */
SQLBindParameter( hstmt, /* Statement handle */
2, /* 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, /* The data itself */
0, /* Maximum length (default 0) */
&orind2 ); /* Null-terminated string */
Dynamic parameters can be used in WHERE clauses. The following example selects from the cars table, using a dynamic parameter. This query is known as a searched query:
SQLCHAR model[21] = "Hummer";
int i;
SQLINTEGER orind = SQL_NTS;
[ Allocate handles and connect. ]
SQLExecDirect( hstmt, "select model from cars where model =
( ? )", SQL_NTS );
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 ), /* Length of the parameter */
0, /* No precision specifier */
model, /* The data itself */
0, /* Maximum length (default 0) */
&orind ); /* Null-terminated string */
Faster inserts also can be achieved if the following conditions are met:
• Inserts must be into a base table (not a view or index).
• The table must not have any rules or integrities defined on it.
• The table must not be a gateway table (for example, an IMA table, security audit log file, or an Enterprise Access table).