Executing SQL Statements There are two main functions in DB2 ODBC for executing SQL statements: SQLExecDirect() and SQLExecute(). The SQLExecDirect() function prepares and executes a SQL statement similar to the SQL EXECUTE IMMEDIATE embedded dynamic SQL statement. SQLRETURN SQLExecDirect (SQLHSTMT StatementHandle, SQLCHAR *Statement, SQLINTEGER StatementLength); The SQLExecute() function requires that the statement be prepared using SQLPrepare() first. Once the statement has been prepared it can be executed many times, saving CPU resources. SQLRETURN SQLPrepare (SQLHSTMT StatementHandle, SQLCHAR *Statement, SQLINTEGER StatementLength); SQLRETURN SQLExecute (SQLHSTMT StatementHandle); Because the statement is dynamic, a program can put literal values directly into the SQL statement prior to the SQLPrepare() or SQLExecDirect() call. For example, char stmt[500]; char testcenter[7]; char newname[41]; strcpy(stmt,"UPDATE DB2USER1.TEST_CENTER "); strcat(stmt,"SET NAME='"); strcat(stmt,newname); strcat(stmt,"' "); strcat(stmt,"WHERE TCID = '"); strcat(stmt,testcenter); strcat(stmt,"' ;"); rc = SQLExecDirect(StmtHandle, stmt, strlen(stmt)); This works because the SQLExecDirect() function optimizes and executes the statement at runtime. This technique can also be used to supply literal values in predicates of SELECT statements. If this were to be done repetitively in a program, the optimization would be done every time and potentially waste resources. For repetitive statements, there are parameter markers. Parameter MarkersHost variables in ODBC programs are inserted into SQL statements using parameter markers. Parameter markers are represented by a "?" (question mark) character in the statement. Parameter markers can be placed in any SQL statement where an embedded statement would include a host variable. To make the connection between the parameter marker and the variable, the SQLBindParameter() function is used. SQLRETURN SQLBindParameter(SQLHSTMT StatementHandle, SQLUSMALLINT ParamMarkerNumber, SQLSMALLINT ParamType, SQLSMALLINT CDataType, SQLSMALLINT SqlDataType, SQLUINTEGER Precision, SQLSMALLINT Scale, SQLPOINTER Buffer, SQLINTEGER BufferMaxSize, SQLINTEGER *BufferDataSize); The SQLBindParameter() call may be made prior to a call to SQLPrepare(), but must be made prior to the execution of the SQL statement via SQLExecute() or SQLExecDirect(). Each parameter marker used for a statement must be assigned a unique sequential ParamMarkerNumber by the application. They need not be defined in order, but there cannot be any gaps when the SQLExecute() call is made. The application needs to call SQLBindParameter() for every parameter marker included in the SQL statement. ParamType specifies whether the parameter is input to the SQL statement (SQL_PARAM_INPUT), output from the SQL statement (SQL_PARAM_OUTPUT), or both (SQL_PARAM_INPUT_OUTPUT). Input parameter markers can be used in any SQL statement; output and input/output parameters may only be used for SQL CALL statements to stored procedures. Using the above program excerpt as a model, a parameterized program might look like the following. char stmt[500] = "UPDATE DB2USER1.TEST_CENTER" " SET NAME=? WHERE TCID=?;"; char testcenter[7]; char newname[41]; SQLBindParameter(StmtHandle, 1, SQL_C_CHAR, SQL_CHAR, 41, 0, newname, sizeof(newname), SQL_NTS); SQLBindParameter(StmtHandle, 2, SQL_C_CHAR, SQL_CHAR, 7, 0, testcenter, sizeof(testcenter), SQL_NTS); rc = SQLExecDirect(StmtHandle, stmt, strlen(stmt)); Input ParametersWhen the statement is executed, the actual data value for the parameter is sent to the server. The buffer must contain valid input data values and the BufferDataSize must contain the corresponding length, SQL_NTS, or SQL_NULL_DATA. Output ParametersAfter the statement is executed, data for the output parameter is returned to the application buffer and BufferDataSize, unless both are null pointers, in which case the output data is discarded. Input/Output ParametersWhen the statement is executed, the actual data value for the parameter is sent to the server. The buffer must contain valid input data values and the BufferDataSize variable must contain the corresponding length, SQL_NTS, SQL_NULL_DATA. After the statement is executed, data for the output parameter is returned to the application buffer and BufferDataSize, unless both are null pointers, in which case the output data is discarded. Data at ExecutionA bound parameter for which value is prompted at execution time instead of stored in memory before calling SQLExecute() or SQLExecDirect() is called a data-at-execute parameter. To indicate this, the application sets the BufferDataSize to the value SQL_DATA_AT_EXEC. If there are any data-at-execute parameters when the application calls SQLExecDirect() or SQLExecute(), the call returns with SQL_NEED_DATA to prompt the application to supply values for these parameters. The application then
When all data-at-execute parameters are assigned values, SQLParamData() completes execution of the SQL statement and produces a return value and diagnostics as the original SQLExecDirect() or SQLExecute() would have produced. If there is more than one data-at-execute parameter, set each input data pointer argument to some value that the program can recognize to uniquely identify the field. LOB LocatorsThe application variable can be a LOB Locator, rather than a data area. In this case, the LOB Locator is passed to or from the server rather than to or from the LOB itself. LOB Locators allow an application to manipulate LOB data without passing the entire object back and forth. Data TypesThe CDataType and SQLDataType parameters tell the DB2 ODBC interface what data types the host variable and database variable are, respectively. Data conversion will be done if necessary, within the limits of valid conversions. The DB2 ODBC header files provide many convenient constants for coding data types in your programs. Along with the specification of data types and column buffers, the DB2 ODBC functions usually require a long integer field to hold the actual length of the buffer contents. For binary data types like INTEGER and SMALLINT, this is generally a fixed value. For character fields, this may contain SQL_NTS, which indicates that the character field is a null- terminated string. The field may also be used to indicate a null value when set to SQL_NULL_DATA. Releasing Parameter MarkersParameter markers remain bound to a statement handle until the statement handle is deallocated using SQLFreeHandle(), another SQLBindParameter() call is made for that ParamMarkerNumber, or SQLFreeStmt() is called with the SQL_RESET_PARAMS option. This is true even if the statement handle is reused for another different SQL statement. Arrays of ValuesPrograms that insert, delete, or update many values repetitively can pass arrays of values to DB2 ODBC. This is done by allocating an array of each parameter that is passed to SQLBindParameter() and calling SQLParamOptions() to indicate the number of array elements. SQLRETURN SQLParamOptions (SQLHSTMT StatementHandle, SQLUINTEGER RowCount, SQLUINTEGER *FetchOffsetPtr); The RowCount should be set to the number of entries in the array. The contents of FetchOffsetPtr have the following uses:
The output argument FetchOffsetPtr indicates how many sets of parameters were successfully processed. If the statement processed is a query, FetchOffsetPtr indicates the array index associated with the current result set returned by SQLMoreResults() and is incremented each time SQLMoreResults() is called. DB2 ODBC prepares the statement, and executes it repeatedly for the array of parameter markers. As a statement executes, FetchOffsetPtr is set to the index of the current array of parameter values. If an error occurs during execution for a particular element in the array, execution halts and SQLExecute(), SQLExecDirect(), or SQLParamData() returns SQL_ERROR. ![]() |
![]() | |
Team-Fly ![]() |
Top |