Executing SQL Statements

In order to execute an SQL statement, you must first prepare the SQL statement.
  • During preparation, the database will usually precompile the SQL statement and creates an access plan for the statement.
  • The access plan is kept as long as the statement exists.
  • You can then execute the statement as many times as you want.
The PREPARESTATEMENT function prepares an SQL statement for execution. It allocates a statement handle that represents the statement.
  • The application must pass the statement handle to all subsequent functions that require a statement.
  • An application can prepare multiple statements at-a-time.
  • The application must ensure it uses the required statement handle with subsequent functions.

The PREPARECALL function prepares a stored procedure call statement for execution.

SQL statements may contain parameters that are evaluated at execution time.
  • Parameters are marked by a question mark (?) within the SQL statement.
  • The parameters are numbered in order of appearance, starting with 1.
  • After preparing, the application can bind host variables to the parameters using the BINDPARAMETER function. When the statement is later executed, the content of the host variables is used and sent to the database.

The following example SELECT query contains one parameter:

SELECT * FROM EMPLOYEE WHERE SALARY>?

You can use the GETNUMPARAMETERS and GETPARAMETERINFO functions to obtain detailed information about the statement parameters.

To execute a statement, you must call the EXECUTE function.
  • If the statement was an update SQL statement, you can retrieve the number of rows updated using the GETUPDATECOUNT function or the UPDATE-COUNT parameter at the EXECUTE function.
  • If the statement was a SQL query, you can use a cursor to retrieve (fetch) the result rows and columns.
  • A statement can provide multiple results.
    • To retrieve the additional results you must call the GETMORERESULTS function.
    • The GETMORERESULTS function will move to the next available cursor or update count.
    • If the statement was a stored procedure call, you must call the GETMORERESULTS function until no more results are available. Only then will the output parameters be updated with the data passed back by the stored procedure.

You can set and get several attributes on the statement level. You do so by calling the SETSTMTATTR or GETSTMTATTR functions.

When you no longer need a statement, you must close it by calling the CLOSESTATEMENT function:
  • The CLOSESTATEMENT function frees the statement handle and closes all cursors (if any) that may still be open from the last statement execution.
  • The statement handle is no longer valid after the CLOSESTATEMENT function.