Executing SQL Statements

Team-Fly    

 
DB2 Universal Database for OS/390 v7.1 Application Certification Guide
By Susan Lawson
Table of Contents
Chapter 11.  ODBC/CLI Programming

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 Markers

Host 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 Parameters

When 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 Parameters

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.

Input/Output Parameters

When 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 Execution

A 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

  • Calls SQLParamData() to conceptually advance to the first such parameter. SQLParamData() returns SQL_NEED_DATA and provides the contents of the input data pointer argument specified on the associated SQLBindParameter() call to help identify the information required.

  • Calls SQLPutData() to pass the actual data for the parameter. Long data can be sent in pieces by calling SQLPutData() repeatedly.

  • Calls SQLParamData() again after it has provided the entire data for this data-at-execute parameter. If more data-at-execute parameters exist, SQLParamData() again returns SQL_NEED_DATA and the application repeats steps 2 and 3 above.

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 Locators

The 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 Types

The 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 Markers

Parameter 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 Values

Programs 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:

  • When the SQL statement succeeds, the value in FetchOffsetPtr is set to the input value in RowCount to indicate that all elements of the array have been processed successfully.

  • When the SQL statement returns an error, the application can access the value in FetchOffsetPtr to find out which element in the array failed.

  • When SQLParamData() returns SQL_NEED_DATA, the application can access the value in FetchOffsetPtr to determine which set of parameters is being assigned values.

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


DB2 Universal Database for OS. 390 v7. 1 Application Certification Guide
DB2(R) Universal Database for OS/390 V7.1 Application Certification Guide (IBM DB2 Certification Guide Series)
ISBN: 0131007718
EAN: 2147483647
Year: 2002
Pages: 163
Authors: Susan Lawson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net