SQLPrepare - Prepare a statement
SQLPrepare()
associates
an SQL statement with the input statement handle and sends the statement
to the DBMS to be prepared. The application can reference this prepared
statement by passing the statement handle to other functions.
If the statement handle has been
used with a SELECT statement, SQLFreeStmt()
must
be called to close the cursor, before calling SQLPrepare()
.
SQLPrepareW()
. Refer to Unicode in Db2 for i CLI for more information about Unicode
support for DB2® CLI.Syntax
SQLRETURN SQLPrepare (SQLHSTMT hstmt,
SQLCHAR *szSqlStr,
SQLINTEGER cbSqlStr);
Function arguments
Data type | Argument | Use | Description |
---|---|---|---|
SQLHSTMT | hstmt | Input | Statement handle. There must not be an open cursor associated with hstmt. |
SQLCHAR * | szSqlStr | Input | SQL statement string. |
SQLINTEGER | cbSqlStr | Input | Length of contents of szSqlStr argument.
This must be set to either the exact length of the SQL statement in szSqlstr, or to SQL_NTS if the statement text is null-terminated. |
Usage
SQLPrepare()
, the application can request information
about the format of the result set (if it is a SELECT statement) by
calling: SQLNumResultCols()
SQLDescribeCol()
SQLColAttribute()
A prepared statement can be processed once, or multiple
times by calling SQLExecute()
. The SQL statement
remains associated with the statement handle until the handle is used
with another SQLPrepare()
, SQLExecDirect()
, SQLColumns()
, SQLSpecialColumns()
, SQLStatistics()
,
or SQLTables()
.
The SQL statement string might
contain parameter markers. A parameter marker is represented by a
"?" character, and indicates a position in the statement where the
value of an application variable is to be substituted, when SQLExecute()
is
called. SQLBindParam()
is used to bind (or associate)
an application variable to each parameter marker, and to indicate
if any data conversion should be performed at the time the data is
transferred.
The SQL statement cannot be a COMMIT or ROLLBACK. SQLTransact()
must
be called to issue COMMIT or ROLLBACK.
If the SQL statement is a positioned DELETE or a Positioned UPDATE, the cursor referenced by the statement must be defined on a separate statement handle under the same connection handle.
Return codes
- SQL_SUCCESS
- SQL_SUCCESS_WITH_INFO
- SQL_ERROR
- SQL_INVALID_HANDLE
Diagnostics
SQLSTATE | Description | Explanation |
---|---|---|
24000 | Cursor state that is not valid | There is an open cursor on the specified hstmt. |
37xxx | Syntax error or access violation | szSqlStr contained one or
more of the following statements:
|
HY001 | Memory allocation failure | The driver is unable to allocate memory required to support the processing or completion of the function. |
HY009 | Argument value that is not valid | szSqlStr is a null pointer.
The argument cbSqlStr is less than 1, but not equal to SQL_NTS. |
HY013 * | Memory management problem | The driver is unable to access memory required to support the processing or completion of the function. |
HY021 | Internal descriptor that is not valid | The internal descriptor cannot be addressed or allocated, or it contains a value that is not valid. |
SQLExecute()
.Example
check_error,
initialize, and terminate
functions used in the following
example./*************************************************************************
** file = prepare.c
**
** Example of preparing then repeatedly executing an SQL statement.
**
** Functions used:
**
** SQLAllocConnect SQLFreeConnect
** SQLAllocEnv SQLFreeEnv
** SQLAllocStmt SQLFreeStmt
** SQLConnect SQLDisconnect
**
** SQLBindCol SQLFetch
** SQLTransact SQLError
** SQLPrepare SQLSetParam
** SQLExecute
**************************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include "sqlcli.h"
#define MAX_STMT_LEN 255
int initialize(SQLHENV *henv,
SQLHDBC *hdbc);
int terminate(SQLHENV henv,
SQLHDBC hdbc);
int print_error (SQLHENV henv,
SQLHDBC hdbc,
SQLHSTMT hstmt);
int check_error (SQLHENV henv,
SQLHDBC hdbc,
SQLHSTMT hstmt,
SQLRETURN rc);
/*******************************************************************
** main
** - initialize
** - terminate
*******************************************************************/
int main()
{
SQLHENV henv;
SQLHDBC hdbc;
SQLCHAR sqlstmt[MAX_STMT_LEN + 1]="";
SQLRETURN rc;
rc = initialize(&henv, &hdbc);
if (rc == SQL_ERROR) return(terminate(henv, hdbc));
{SQLHSTMT hstmt;
SQLCHAR sqlstmt[]="SELECT deptname, location from org where division = ?";
SQLCHAR deptname[15],
location[14],
division[11];
SQLINTEGER rlength,
plength;
rc = SQLAllocStmt(hdbc, &hstmt);
if (rc != SQL_SUCCESS )
check_error (henv, hdbc, SQL_NULL_HSTMT, rc);
/* prepare statement for multiple use */
rc = SQLPrepare(hstmt, sqlstmt, SQL_NTS);
if (rc != SQL_SUCCESS )
check_error (henv, hdbc, hstmt, rc);
/* bind division to parameter marker in sqlstmt */
rc = SQLSetParam(hstmt, 1, SQL_CHAR, SQL_CHAR, 10, 10, division,
&plength);
if (rc != SQL_SUCCESS )
check_error (henv, hdbc, hstmt, rc);
/* bind deptname to first column in the result set */
rc = SQLBindCol(hstmt, 1, SQL_CHAR, (SQLPOINTER) deptname, 15,
&rlength);
if (rc != SQL_SUCCESS )
check_error (henv, hdbc, hstmt, rc);
rc = SQLBindCol(hstmt, 2, SQL_CHAR, (SQLPOINTER) location, 14,
&rlength);
if (rc != SQL_SUCCESS )
check_error (henv, hdbc, hstmt, rc);
printf("\nEnter Division Name or 'q' to quit:\n");
printf("(Eastern, Western, Midwest, Corporate)\n");
gets(division);
plength = SQL_NTS;
while(division[0] != 'q')
{
rc = SQLExecute(hstmt);
if (rc != SQL_SUCCESS )
check_error (henv, hdbc, hstmt, rc);
printf("Departments in %s Division:\n", division);
printf("DEPTNAME Location\n");
printf("-------------- -------------\n");
while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS)
{
printf("%-14.14s %-13.13s \n", deptname, location);
}
if (rc != SQL_NO_DATA_FOUND )
check_error (henv, hdbc, hstmt, rc);
SQLFreeStmt(hstmt, SQL_CLOSE);
printf("\nEnter Division Name or 'q' to quit:\n");
printf("(Eastern, Western, Midwest, Corporate)\n");
gets(division);
}
}
rc = SQLTransact(henv, hdbc, SQL_ROLLBACK);
if (rc != SQL_SUCCESS )
check_error (henv, hdbc, SQL_NULL_HSTMT, rc);
terminate(henv, hdbc);
return (0);
}/* end main */