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

When manipulating long data, it may 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.

1. 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);    

2. 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.
3. 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.
4. 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.
5. Pass the actual data for the parameter by calling SQLPutData(). Long data can be sent in pieces by calling SQLPutData() repeatedly.
6. Call SQLParamData() again after providing the entire data for this data-at-execute parameter.
7. If more data-at-execute parameters exist, SQLParamData() again returns SQL_NEED_DATA and the application repeats steps 4 and 5 above.
   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);
                   /* ... */    		
               }
           }