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 theEXECUTE
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.
- To retrieve the additional results you must call the
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.