Execute prepared statements

When a statement is prepared, variable information can be passed as parameter markers, which are denoted by question marks (?). When the statement is executed, the parameter markers are replaced with the real variable information.

Preparing the statement is performed at the server. The SQL statements are compiled and the access plans are built. This allows the statements to be executed much more efficiently. When compared to using dynamic SQL to execute the statements, the result is much closer to static SQL. Extened Dynamic preserves prepared statements accross job sessions. This allows prepared statements with parameter markers to be executed multiple times within the job session even without Extended Dynamic ON. When the database server prepares the statements, it saves some of them in a special iSeries™ object called a package (*SQLPKG). This approach is called Extended Dynamic SQL. Packages are created automatically by the driver; an option is provided to turn off Package Support. This is covered in The performance architecture of the iSeries Access for Windows ODBC driver.

SQLPrepare

Prepares an SQL statement for execution:

In C, this statement is coded:

SQLCHAR szSQLstr[ ] = "INSERT INTO NAMEID VALUES (?,?)";

rc = SQLPrepare(hstmt, szSQLstr, SQL_NTS);

SQLBindParameter

Allows application to specify storage, data type, and length associated with a parameter marker in an SQL statement.

In the example, parameter 1 is found in a signed double word field called id. Parameter 2 is found in an unsigned character array called name. Since the last parameter is null, the driver expects that name is null-terminated as it will calculate the string's length.

In C, this statement is coded:

 
SQLCHAR szName[51];
SQLINTEGER id, parmLength = 50, lenParm1 = sizeof(SQLINTEGER) , lenParm2 = SQL_NTS ;

rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 
                                       sizeof(SQLINTEGER), 0, &id, sizeof(SQLINTEGER), &lenParm1);
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 
                                      parmLength, 0, szName, sizeof(szName), &lenParm2);

SQLExecute

Executes a prepared statement, using current values of parameter markers:

In C, this statement is coded:

id=500;
strcpy(szName, "TEST");
rc = SQLExecute(hstmt);  // Insert a record with id = 500, name = "TEST"
id=600;
strcpy(szName, "ABCD");
rc = SQLExecute(hstmt);  // Insert a record with id = 600, name = "ABCD"

SQLParamData / SQLPutData

Visual Basic does not directly support pointers or fixed-location ANSI character null-terminated strings. For this reason, it is best to use another method to bind Character and Binary parameters. One method is to convert Visual Basic String data types to/from an array of Byte data types and bind the array of Byte. This method is demonstrated in Convert strings and arrays of byte.

Another method, that should only be used for input parameters, is to supply the parameters at processing time. This is done using SQLParamData and SQLPutData APIs:

• They work together to supply parameters.
• SQLParamData moves the pointer to the next parameter.
• SQLPutData then supplies the data for that parameter.

's_parm is a character buffer to hold the parameters
    's_parm(1) contains the first parameter
    Static s_parm(2) As String
        s_parm(1) = "Rear Bumper"
        s_parm(2) = "ABC Auto Part Store"
    Dim rc As Integer
    Dim cbValue As Long
    Dim s_insert As String
    Dim hStmt As Long
    Dim lPartID As Long
        
    rc = SQLAllocHandle(SQL_HANDLE_STMT, ghDbc, hStmt)
    If rc <> SQL_SUCCESS Then _
		Call DspSQLDiagRec(SQL_HANDLE_DBC, ghDbc, "SQLAllocStmt failed.")
    
    s_insert = "INSERT INTO ODBCSAMPLE VALUES(?, ?, ?)"
    
    rc = SQLBindParameter(hStmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, _
                          4, 0, lPartID, 4, ByVal 0)
    If rc <> SQL_SUCCESS Then _
		Call DspSQLDiagRec(SQL_HANDLE_DBC, ghDbc, "SQLBindParameter failed.")
    
	'#define SQL_LEN_DATA_AT_EXEC_OFFSET  (-100) the parms will be supplied at run time
    cbValue = -100   
 
	' Caller set 8th parameter to "ByVal 2" so driver will return
	' 2 in the token when caller calls SQLParamData
    rc = SQLBindParameter(hStmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, _
                          4, 0, ByVal 2, 0, cbValue)
    If rc <> SQL_SUCCESS Then _
		Call DspSQLDiagRec(SQL_HANDLE_DBC, ghDbc, "SQLBindParameter failed.")
    
    ' Caller set 8th parameter to "ByVal 3" so driver will return
	' 3 in the token when caller calls SQLParamData the second time.
    rc = SQLBindParameter(hStmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, _
                          4, 0, ByVal 3, 0, cbValue)
    If rc <> SQL_SUCCESS Then _
		Call DspSQLDiagRec(SQL_HANDLE_DBC, ghDbc, "SQLBindParameter failed.")
    
	' Prepare the insert statement once.
    rc = SQLPrepare(hStmt, s_insert, SQL_NTS)

    lPartID = 1
    rc = SQLExecute(hStmt)	' Execute multiple times if needed.
    
	' Since parameters 2 and 3 are bound with cbValue set to -100,
	' SQLExecute returns SQL_NEED_DATA

    If rc = SQL_NEED_DATA Then
		
		' See comment at SQLBindParameter: token receives 2.
        rc = SQLParamData(hStmt, token)  
        
        If rc <> SQL_NEED_DATA Or token <> 2 Then _
			Call DspSQLDiagRec(SQL_HANDLE_DBC, ghDbc, "SQLParamData failed.")
        
		' Provide data for parameter 2.
        rc = SQLPutData(hStmt, ByVal s_parm(1), Len(s_parm(1)))
        If rc <> SQL_SUCCESS Then _
			Call DspSQLDiagRec(SQL_HANDLE_DBC, ghDbc, "SQLPutData failed.")
        
		' See comment at SQLBindParameter: token receives 3.
        rc = SQLParamData(hStmt, token)   
        If rc <> SQL_NEED_DATA Or token <> 3 Then _
			Call DspSQLDiagRec(SQL_HANDLE_DBC, ghDbc, "SQLParamData failed.")
        
		' Provide data for parameter 2.
        rc = SQLPutData(hStmt, ByVal s_parm(2), Len(s_parm(2)))
        If rc <> SQL_SUCCESS Then _
			Call DspSQLDiagRec(SQL_HANDLE_DBC, ghDbc, "SQLPutData failed.")
        
		' Call SQLParamData one more time. 
		' Since all data are provided, driver will execute the request.
        rc = SQLParamData(hStmt, token)   
        If rc <> SQL_SUCCESS Then _
			Call DspSQLDiagRec(SQL_HANDLE_DBC, ghDbc, "SQLParamData failed.")
    Else
        Call DspSQLDiagRec(SQL_HANDLE_STMT, hStmt, "SQLExecute failed.")
    End If