DB2 10.5 for Linux, UNIX, and Windows

Specifying parameter values at execute time for long data manipulation in CLI applications

When manipulating long data, it might not be feasible for the application to load the entire parameter data value into storage at the time the statement is executed, or when the data is fetched from the database. A method has been provided to allow the application to handle the data in a piecemeal fashion. The technique of sending long data in pieces is called specifying parameter values at execute time. It can also be used to specify values for fixed size non-character data types such as integers.

Before you begin

Before specifying parameter values at execute time, ensure you have initialized your CLI application.

About this task

While the data-at-execution flow is in progress, the only CLI functions the application can call are:

SQLParamData() and SQLPutData() functions.
The SQLCancel() function which is used to cancel the flow and force an exit from the loops without executing the SQL statement.
The SQLGetDiagRec() function.

A data-at-execute parameter is a bound parameter for which a value is prompted at execution time instead of stored in memory before SQLExecute() or SQLExecDirect() is called.

Procedure

To indicate such a parameter on an SQLBindParameter() call:

Set the input data length pointer to point to a variable that, at execute time, will contain the value SQL_DATA_AT_EXEC. For example:

    /* dtlob.c */    
    /* ... */    
    SQLINTEGER      blobInd ;
    /* ... */
    blobInd = SQL_DATA_AT_EXEC;
    sqlrc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_BINARY,
                             SQL_BLOB, BUFSIZ, 0, (SQLPOINTER)inputParam,
                             BUFSIZ, &blobInd);

If there is more than one data-at-execute parameter, set each input data pointer argument to some value that it will recognize as uniquely identifying the field in question.
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 responds with the subsequent steps.
Call 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.
Pass the actual data for the parameter by calling SQLPutData(). Long data can be sent in pieces by calling SQLPutData() repeatedly.
Call SQLParamData() again after providing 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 4 and 5.

For example:

    /* dtlob.c */    
    /* ... */    
        else 
        {   sqlrc = SQLParamData( hstmt, (SQLPOINTER *) &valuePtr);
            /* ... */   		
		
            while ( sqlrc == SQL_NEED_DATA)
            {   /*
                if more than 1 parms used DATA_AT_EXEC then valuePtr would
                have to be checked to determine which param needed data
                */
                while ( feof( pFile ) == 0 )
                {   n = fread( buffer, sizeof(char), BUFSIZ, pFile);
                    sqlrc = SQLPutData(hstmt, buffer, n);
                    STMT_HANDLE_CHECK( hstmt, sqlrc);    
                    fileSize = fileSize + n;
                    if ( fileSize > 102400u)
                    {   /* BLOB column defined as 100K MAX */
                        /* ... */
                        break;
                    }			
                }
                /* ... */		    
                sqlrc = SQLParamData( hstmt, (SQLPOINTER *) &valuePtr);
                /* ... */    		
            }
        }

Results

When all data-at-execute parameters have been assigned values, SQLParamData() completes execution of the SQL statement and returns a return value and diagnostics as the original SQLExecDirect() or SQLExecute() might have produced.