Repeating - Prepared Execution

---

Where an SQL statement will be repeatedly executed, it is more usual to use prepared execution, as a means to reduce the parsing and compilation overheads.

Mimer SQL versions 8.2 and later reduce the performance difference between direct and prepared execution by maintaining and re-using compiled statements on the server.

Prepared Statement Example

In this example each of the parameters in the prepared SQL statement (indicated by ?) are bound to a variable in the application before the statement is executed:

 SQLFLOAT   increase;
 SQLCHAR    code[4];
 SQLINTEGER increaseInd, codeInd;
 . . .
 
 SQLPrepare( hstmt,
             "UPDATE mimer_store.currencies \
                 SET exchange_rate = exchange_rate * ? \
                 WHERE code = ?", SQL_NTS );
 
 SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_DOUBLE,
                   SQL_REAL, 7, 0,
                   &increase, 0, &increaseInd );
 SQLBindParameter( hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
                   SQL_CHAR, 4, 0,
                   code, sizeof(code), &codeInd );
 
 /* Set parameter values and length/indicator */
 increase = 1.05;
 strcpy( code, "USD" );
 codeInd = SQL_NTS;
 
 SQLExecute( hstmt );
 SQLRowCount( hstmt, &rowcount );
 printf( "%d rows have been updated\n", rowcount );

Stored Procedure Example

Similarly, it is possible to prepare an SQL statement that calls a stored procedure:

 SQLINTEGER  order_id, item_id;
 SQLSMALLINT quantity;
 SQLINTEGER  orderInd = 0, itemInd = 0, quantityInd = 0;
 . . .
 
 SQLPrepare( hstmt,
             "{CALL mimer_store.order_item( ?, ?, ? )}",
             SQL_NTS );
 
 SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG,
                   SQL_INTEGER, 0, 0,
                   &order_id, SQL_IS_INTEGER, &orderInd );
 SQLBindParameter( hstmt, 2, SQL_PARAM_INPUT, SQL_C_SLONG,
                   SQL_INTEGER, 0, 0,
                   &item_id, SQL_IS_INTEGER, &itemInd );
 SQLBindParameter( hstmt, 3, SQL_PARAM_INPUT, SQL_C_SSHORT,
                   SQL_INTEGER, 0, 0,
                   &quantity, SQL_IS_SMALLINT, &quantityInd );
 
 /* Set parameter values */
 order_id = 700001;
 item_id = 60158;
 quantity = 2;
 
 SQLExecute( hstmt );

Parameters in Procedure Calls

Parameters in procedure calls can be input, input/output, or output. A more complicated example illustrates how to handle an output parameter:

 SQLCHAR     country[3];
 SQL_INTERVAL_STRUCT interval;
 SQLINTEGER  countryInd, intervalInd;
 SQLSMALLINT numparams;
 . . .
 
 SQLPrepare( hstmt,
             "{CALL mimer_store.age_of_adult( ?, ? )}",
             SQL_NTS );
 
 SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
                   SQL_CHAR, 3, 0,
                   country, sizeof(country), &countryInd );
 SQLBindParameter( hstmt, 2, SQL_PARAM_OUTPUT, SQL_C_INTERVAL_YEAR,
                   SQL_INTERVAL_YEAR, 0, 0,
                   &interval, sizeof(SQL_INTERVAL_STRUCT),
                   &intervalInd );
 
 SQLNumParams( hstmt, &numparams );
 printf( "statement contains %d parameters\n", numparams );
 
 /* Set input parameter value and length */
 strcpy( country, "US" );
 countryInd = SQL_NTS;
 
 SQLExecute( hstmt );
 printf( "%d years\n", interval.intval.year_month.year );
 

A statement handle is released by calling SQLFreeHandle; however, it is more efficient to reuse statement handles rather than freeing them and allocate new ones. When SQLFreeHandle is called, the driver releases the associated structure. SQLDisconnect automatically frees all statements on a connection.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se