Appendix F - Retrieving Results, Accessing BLOB and CLOB Data

Retrieving Results

After a statement is prepared or executed, application can call tbdrv_stmt_returns_rows() to verify if the prepared/executed statement will return a result set.

If the prepared and executed statement returns a result set, application can use the following APIs in sequence to get the metadata of the result set:

tbdrv_get_numcols() // returns the number of columns in a result set.

tbdrv_get_colspec_ex() // get columns information as array of TBDRV_COLUMN_SPEC

The metadata of result set is described by the TBDRV_COLUMN_SPEC structure which is defined as:

typedef struct {

int iColNum; // Zero based index of the column to retrieve information about.

// To get the total number of columns in the result set, call tbdrv_get_numcols()

int iOLEDBDataType; // Data type returned via this column.

// These data types can be any of the OLE DB DBTYPE_xxxx values. See OLEDB.H for details

int iNullable; // See piNullable parameter of tbdrv_get_colspec() for detail

char szColName[TBDRV_COLUMN_NAME_MAX_LEN+1]; // Buffer to accept the column name

char szTableName[TBDRV_TABLE_NAME_MAX_LEN+1]; // Buffer to accept the table name

char szSchemaName[TBDRV_SCHEMA_NAME_MAX_LEN+1]; // Buffer to accept the schema name

char szDBAliasName[TBDRV_EIQ_DB_ALIAS_NAME_MAX_LEN+1]; //Buffer to accept the database alias name

char szTableAliasName[TBDRV_TABLE_NAME_MAX_LEN+1]; //Buffer to accept the table alias name.

int iLen; // Length of the data for this column

int iPrecision; // See comment in tbdrv_get_colspec()

int iScale; // Currently unused

int iMantissaPrecision; // Currently unused

int iMaxExponent; // Currently unused

int iAliasLevel; // Currently unused

int iRowBufferOffset; // Column offset in the row buffer which is used the get the pointer to column value

// in result set buffer specified in tbder_get_rows()

int iSuperSchemaFlags; // Internal column flags mask

int iLOBLocatorType; // Large object locator type - see comments in tbdrv_read_lob_col_by_locator() for details

char _reserved[92]; // For future use

} TBDRV_COLUMN_SPEC;

There are two ways to fetch data:

Single row fetch: The application can access the result row by row and column by column using tbdrv_fetch_row() and tbdrv_get_colval().

Example:

while( tbdrv_fetch_row(lsession, lstatement, &iRowhandle)==SQLDRV_SUCCEED ) // iterate through the result set row by row

for(int i=0; i<iNumCol; i++) // get column value column by column where iNumCol is number of column in the result set.

tbdrv_get_colval() // piIndicator returns the Length/Indicator; see notes below for details.

Bulk fetch: The application can get the result set data by specifying the number of rows returned using tbdrv_get_row_buffer_len() and tbdrv_get_rows().

The size of single row in bytes for the result set includes both the size of column values and their length/indicator buffer as illustrated in the following diagram which shows the layout of row buffers in the result set generated by the query:

SELECT A, B, C from sample_table

The iRowBufferOffset in the TBDRV_COLUMN_SPEC is the 0 based position in the row buffer for particular column.

To get the pointer to value of COLUMN B which is the second column:

*pB = &pRowBuffer[pColSpec[1]. iRowBufferOffset];

To get the length/indicator of COLUMN B:

pBLenorNull = &(pRowBuffer[pColSpec[1]. iRowBufferOffset + pColSpec[1]. iLen ];

Example:

tbdrv_get_row_buffer_len(lsession, lstatementm &iRowBufferLen); // iRowBufferLen is the size of a single row in bytes for the result set.

unsigned char *pBuffer = new[iRowBufferLen* iRequestedRowCount]; // based on the application requirement and memory usage, allocate

// memory to hold the data of the number of rows requested.

while( SQLDRV_SUCCESS ==

tbdrv_get_rows(lSessionHandle, lStatementHandle, 1, plRowHandles, (unsigned char*)pBuffer, iBufferLen*50, &iReturnedRowCount) )

{

char *pRowBuffer = pBuffer; // pointer to the first row buffer

for(int r=0; r<iReturnedRowCount; r++) // process result buffer row by row

{

for(c=0; c<iNumCols; c++)

// access column value column by column based on the metadata specified in TBDRV_COLUMN_SPEC array pColSpec.

{

// Length/Indicator values; see notes below.

int iSizeOrNullIndicator = *((int*)&pRowBuffer[pColSpec[c]. iRowBufferOffset+ pColSpec[c]. iLen]);

// get the pointer to data in the row buffer.

unsigned char* pData = &pRowBuffer[pColSpec[c]. iRowBufferOffset]

pRowBuffer += iRowBufferLen; // advanced the pointer to next row by iRowBufferLen bytes

Notes:

Length/Indicator Values:

The length/indicator buffer is used to pass the byte length of the data in the data buffer or a special indicator such as TBDRV_NULL_DATA, which indicates that the data is NULL.

But for the LOB column, this is the upper bound of size of LOB data in the current row.

This value is a 4 bytes singed integer as the output parameter piIndicator in tbdrv_get_colval().

Accessing BLOB and CLOB Data

Binary Large Object (BLOB) and Character Large Object (CLOB) data is useful in certain situations where data which does not follow a normal structure needs to be stored an manipulated in a free form.

Accessing Large Object (LOB) Data:

Checking if the result set column is a LOB:

To determinate if the result set column is LOB, application can check the iNullable in TBDRV_COLUMN_SPEC array returned by tbdrv_get_colspec_ex(). The iNullable is a bitmask that describes column characteristics. If iNullable & DBCOLUMNFLAGS_ISLONG is true, then the column is a LOB.

Application can access the LOB value in two ways: Single row fetch, and Bulk fetch.

Single row fetch:

The following APIs provide access to BLOB and CLOB data in single row fetch.

tbdrv_get_colval() - returns LOB locator and size in bytes calling application needs to allocate to retrieve LOB value in one chunk.

tbdrv_read_lob_col_by_locator() - copies LOB value from data storage to user provided buffer.

Each of these APIs work with the current row in the context of a statement handle. The current row is defined as the row (or rows in a multi-table join) last fetched (via tbdrv_fetch_row()).

Example:

tbdrv_get_colspec_ex(lSessionHandle, lStatementHandle, iNumCols, pColSpecs, &lNumColReturned); // get column info

while( tbdrv_fetch_row(lSessionHandle, lStatementHandle, &lRowHandle) == SQLDRV_SUCCESS )

{

for(int i=0; i< lNumColReturned; i++)

{

unsigned char *pszVal = new unsigned char[pColSpecs[i].iLen+1];

int iLen = pColSpecs[i].iLen+1;

int iXODataType, iIndicator;

if( SQLDRV_SUCCESS == tbdrv_get_colval(lSessionHandle,

lStatementHandle,

&iXODataType),

&iIndicator, // iIndicator is the size in bytes required to get the LOB value in one chunk

(void*)pszVal, // szVal is the LOB locator

&iLen)

{

if( pColSpecs[i].iNullable & DBCOLUMNFLAGS_ISLONG && stValue.iIndicator != TBDRV_NULL_DATA && iIndicator>0 )

// clob, blob, memo, or doc column

{

unsigned char * pLOBBuffer = null;

WtInt32 lBufferLength = iIndicator, lBytesRead = 0, lBytesRequired = 0;

if(pColSpecs[i]. iOLEDBDataType == DBTYPE_BYTES ) // binary large object

{

pLOBBuffer = new unsigned char[iIndicator];

}

else

{

pLOBBuffer = new unsigned char[iIndicator+1]; // for CLOB, add one more byte for null terminated 0

lBufferLength++;

}

WtInt32 iRet = tbdrv_read_lob_col_by_locator( lSessionHandle,

lStatementHandle,

pColSpecs[i].iLOBLocatorType,

iLen,

(unsigned char*)szVal,

lBufferLength,

(WtUChar8*)pLOBBuffer,

&lBytesRead,

&lBytesRequired );

}

Another way is to use tbdrv_get_lob_col_length() and tbdrv_read_lob_col().

Example:

Following is an example code sequence of processing a result set through SQL SELECT command and reading the BLOB or CLOB data for each record while traversing the results.

tbdrv_execute_stmt()

while( tbdrv_fetch_row() )

tbdrv_get_lob_col_length()

tbdrv_read_lob_col()

Bulk fetch:

To access large object data in bulk fetch mode, use the LOB locators returned by tbdrv_get_rows(). LOB locators are the references to LOB values. When the application passes the locators to the server while requesting the result, and the server returns the LOB result.

Example:

char *pszSQL = "SELECT personid, note from table1"; // where note is a CLOB field.

tbdrv_execute_immed_stmt(lSessionHandle, lStatementHandle, pszSQL, &iNumRowsAffected); // execute the statement

tbdrv_get_numcols(lSessionHandle, lStatementHandle, &iNumCols); // get column count from result set

TBDRV_COLUMN_SPEC *pColSpecs = new TBDRV_COLUMN_SPEC[iNumCols];

WtInt32 lRecordsCopied = 0;

tbdrv_get_colspec_ex(lSessionHandle, lStatementHandle, iNumCols, pColSpecs, &lRecordsCopied); // get column info

tbdrv_get_row_buffer_len(lSessionHandle, lStatementHandle, &iBufferLen ); // get the buffer length for single row

char *pBuffer = new char[iBufferLen*50]; // allocate enough buffer to hold 50 rows

unsigned int *plRowHandles = new unsigned int[ 50 ] ; // allocate row handles

ZeroMemory( plRowHandles, sizeof(unsigned int) * 50 );

while( SQLDRV_SUCCESS == tbdrv_get_rows(lSessionHandle, lStatementHandle, 50, plRowHandles, (unsigned char*)pBuffer, iBufferLen*50, &iReturnedRowCount) )

{

char *pRowBuffer = pBuffer;

for(j=0; j<iNumCols; j++)

{

int iIndicator = *((int*)&pRowBuffer[pColSpecs[j].iRowBufferOffset+pColSpecs[j].iLen]); // size or null indicator

if( pColSpecs[j].iNullable & DBCOLUMNFLAGS_ISLONG && iIndicator != TBDRV_NULL_DATA) // to check the column is a LOB and value is not null

{

WtUChar8 *pLOBLocator = (WtUChar8*)(&pRowBuffer[pColSpecs[j].iRowBufferOffset]); // pointer to lob locator,

WtInt32 lBytesRead = 0, lBytesRequired = 0;

WtInt32 lBufferLength = iIndicator;

if(pColSpecs[j].iOLEDBDataType & DBTYPE_STR) lBufferLength++; // one extral byte for terminated null for CLOB

void* pLOBBuffer = malloc(lBufferLength);

WtInt32 iRet = tbdrv_read_lob_col_by_locator( lSessionHandle,

lStatementHandle,

pColSpecs[j].iLOBLocatorType,

pColSpecs[j].iLen,

pLOBLocator,

lBufferLength,

(WtUChar8*) pLOBBuffer, // buffer to hold the LOB data

&lBytesRead, // number of byte copied to the buffer

&lBytesRequired ); // actual size of byte require, normally, this will be size indicator

}

Writing Large Object (LOB) Data:

To write BLOB or CLOB data via INSERT command or UPDATE command, the application needs to prepare the SQL statement with parameter markers(?).

These two APIs are useful for setting BLOB and CLOB data:

tbdrv_get_numparams()

tbdrv_init_param()

Example:

The following is an example for setting the BLOB column 'image' and CLOB column 'note' in table table1. 'personid' is the primary key whose data type is defined as INT through SQL UPDATE command with parameter markers(?).

tbdrv_prepare_stmt( lSessionHandle, lStatementHandle, "update table1 set note = ? , image = ? where personid = ?")

int iNumParams = 0;

tbdrv_get_numparams(lSessionHandle, lStatementHandle, &iNumParams )

ASSERT(iNumParams==3);

// set the note and image of the person with person id 1

tbdrv_init_param( lSessionHandle, lStatementHandle, 1, 0, NoteBuffer1, DBTYPE_STR, NoteBufferSize1)

tbdrv_init_param( lSessionHandle, lStatementHandle, 2, 0, ImageBuffer1, DBTYPE_BYTES, ImageBufferSize1)

int personid = 1;

tbdrv_init_param( lSessionHandle, lStatementHandle, 3, 0, &personid, DBTYPE_I4, sizeof(int))

tbdrv_execute_stmt()

// set the note and image of the person with person id 2

tbdrv_init_param( lSessionHandle, lStatementHandle, 1, 0, NoteBuffer2, DBTYPE_STR, NoteBufferSize2)

tbdrv_init_param( lSessionHandle, lStatementHandle, 2, 0, ImageBuffer2, DBTYPE_BYTES, ImageBufferSize2)

personid = 2;

tbdrv_init_param( lSessionHandle, lStatementHandle, 3, 0, &personid, DBTYPE_I4, sizeof(int))

tbdrv_execute_stmt()