Large Objects (Blobs) Support
The SQLGetData() and SQLPutData() functions allow fetching or insertion of data in segments. Any non-atomic data type, such as char or byte varying, can be sent or fetched in segments, but generally segments are used with large objects, sometimes known as "blobs".
The ODBC Driver supports the following large objects:
• Long varchar (SQL_LONGVARCHAR)
• Long byte (SQL_LONGVARBYTE)
• Long nvarchar (SQL_WLONGVARCHAR)
Although large object locators are supported in Ingres 9.1 and later, the ODBC Driver does not support large object locators, since there is no corresponding support in the ODBC specification.
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.
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.