SQLGetData()--Fetch Data in Segments
Fetching blob segments using SQLGetData() is more straightforward than SQLPutData(). If the previous SQLPutData() example were expanded to fetch the data after insertion, the following code would fetch the 5,000-character blob in 1,000-character segments:
Example: SQLGetData() Function
RETCODE ret = SQL_SUCCESS;
/*
** Execute the fetch query.
*/
SQLExecDirect ( hstmt, "select * from longv", SQL_NTS );
/*
** Fetch in a loop.
*/
while ( TRUE )
{
rc = SQLFetch ( hstmt );
if ( rc == SQL_NO_DATA )
{
printf( "EOD\n" );
break;
}
/*
** Exit the loop if an error is found.
*/
if ( !SQL_SUCCEEDED ( rc ) )
{
printf("Error fetching from blob table\n" );
break;
}
len = 0;
blob = &b[0];
*blob = '\0';
while ( TRUE )
{
/*
** Get the data in segments until
** the status is SQL_SUCCESS.
*/
ret = SQLGetData( hstmt,
1,
SQL_C_CHAR,
1000,
blob,
&len );
/*
** A status value of SQL_SUCCESS means we're done.
** Exit the loop.
*/
if ( ret == SQL_SUCCESS )
break;
if ( ret == SQL_ERROR )
{
printf ("Error fetching blob segments!\n" );
break;
}
/*
** Increment the pointer to the blob for each successful
** segment fetch.
*/
blob += 1000;
}
}
Often, ODBC functions can share status code variables, but in this case, a second status code, "ret" must be declared in addition to "rc". This is because segment fetching uses two fetch loops: one for the fetch itself, and one for retrieving the segments. Each loop needs to track its own status.
There is no counterpart to SQLParamData() when fetching in segments. Instead, SQLGetData() returns a status of SQL_SUCCESS_WITH_INFO when there are more segments to be fetched. If the status of SQLGetData() were further analyzed, the SQLSTATE would have the value 01004 (data truncated). See Error Reporting in the next section for more information on SQLGetData().
The last argument to SQLGetData(), represented by the len variable, indicates the length of the data available in the ODBC driver cache. For the ODBC Driver, it is normal for this argument to contain a larger value than the segment length until the last segment is fetched.
When all of the blob segments are fetched, SQLGetData() returns a status of SQL_SUCCESS.
As with SQLPutData(), the ODBC Driver supports the traditional use of SQLGetData(). The ODBC application could make a single call to SQLGetData() specifying the entire length of the blob:
/*
** Fetch in a loop.
*/
while ( TRUE )
{
rc = SQLFetch ( hstmt );
/*
** Exit the loop at EOD.
*/
if ( rc == SQL_NO_DATA )
{
printf( "EOD\n" );
break;
}
/*
** Exit the loop if an error is found.
*/
if ( !SQL_SUCCEEDED ( rc ) )
{
printf("Error fetching from blob table\n" );
break;
}
len = 5000;
blob = &b[0];
*blob = '\0';
/*
** Just one call to SQLGetData() is all that is required.
*/
ret = SQLGetData( hstmt, /* Statement handle */
1, /* Column number */
SQL_C_CHAR, /* It's a string */
5000, /* Max length */
blob, /* Buffer to fetch into */
&len ); /* Length indicator */
/*
** Exit the loop if the data cannot be converted to the blob
** buffer.
*/
if ( ret != SQL_SUCCESS ) )
{
printf ("Error entire fetching blob data!\n" );
break;
}
}
The ODBC application would need to know that the blob was at least 5,000 characters or less in order for the above example to work; otherwise, the application would truncate the data. Furthermore, the data buffer must be pre-allocated to 5,000 characters. Therefore, this approach may be inefficient for fetching very large blobs.