SQLPutData()—Send Data in Segments
SQLPutData() sends blob data in segments. The length of the data must be known in advance, and applied to the SQL_LEN_DATA_AT_EXEC macro prior to execution of an insert or update query.
The following example sends a blob 5,000 characters in length to a table containing a long varchar. Each segment is 1,000 characters long.
Example: SQLPutData() Function
SQLCHAR b[5000], *b = &b[0]; /* The blob to be sent */
int i; /* Loop counter */
SQLINTEGER len; /* Segment length */
RETCODE rc = SQL_SUCCESS; /* Return code */
SQLPOINTER pToken = NULL; /* Column indicator */
SQLHSTMT hstmt; /* Statement handle */
/*
** Fill the blob buffer with test data.
*/
for ( i = 0; i < 5000; i++ )
b[i] = 'x ';
/*
** Bind the blob, indicating that the length will be
** provided at runtime.
*/
SQLBindParameter(hstmt, /* Statement handle */
1, /* Column number */
SQL_PARAM_INPUT, /* This is an input parameter */
SQL_C_CHAR, /* Parameter is a string */
SQL_LONGVARBINARY, /* Destination type is long varchar */
0, /* No length required */
0, /* No precision required */
(PTR)1, /* Long parameter number 1 */
0, /* No max length required */
&len ); /* Variable blob length */
/*
** This macro definition tells the ODBC when to expect the end of
** data for the blob.
*/
len = SQL_LEN_DATA_AT_EXEC( 5000 );
/*
** Execute the insert query.
*/
rc = SQLExecDirect( hstmt,
"insert into longv values ( ? )", SQL_NTS );
/*
** Loop, sending the data in segments of 1000.
*/
while ( rc == SQL_NEED_DATA )
{
/*
** Check for EOD marker.
*/
rc = SQLParamData( hstmt, pToken );
if ( rc == SQL_NEED_DATA )
{
/*
** If more data to send, send it.
*/
if ( pToken == 1 )
{
SQLPutData( hstmt, blob, 1000 );
blob += 1000;
}
}
}
Note that SQLBindParameter() never directly references the data buffer "blob". Instead, the number 1 is used to indicate that parameter 1 is a variable length parameter. The overall length of the blob is calculated as a negative number into the len argument via the SQL_LEN_DATA_AT_EXEC() macro. The ODBC driver uses these clues to detect that data is to be sent in segments.
The SQLParamData() function tracks the progress of SQLPutData(). If the status return of SQLParamData() is SQL_NEED_DATA, the ODBC driver has not yet finished sending data segments. The pToken argument returns the parameter number of the data to be sent; thus, more than one blob or variable-length datum can be sent in segments.
The above example could have been coded in the traditional way, as shown in this example:
/*
** Set the maximum length of the blob.
*/
len = 5000;
/*
** Bind in the traditional way.
*/
SQLBindParameter(hstmt, /* Statement handle */
1, /* Column number */
SQL_PARAM_INPUT, /* This is an input parameter */
SQL_C_CHAR, /* Parameter is a string */
SQL_LONGVARBINARY, /* Destination type is long varchar */
5000, /* Length of the data */
0, /* No precision required */
(PTR)blob, /* The data itself */
0, /* No max length required */
&len ); /* Variable blob length */
/*
** Only one call to SQLExecDirect() is required.
*/
SQLExecDirect( hstmt,
"insert into longv values ( ? )", SQL_NTS );
This traditional approach may be acceptable for smaller-length blobs, but would be less practical for large blobs, especially those that consume substantial memory. The ODBC application would need to pre-allocate memory for the entire blob before using it as a query parameter. By contrast, if blobs are sent in segments, only memory for the blob segment needs to be allocated.