Fetched Data
As with dynamic parameters, ODBC applications must bind the fetched data to variables in the ODBC application. Data can be fetched one row at a time, or the application can declare an array to serve as a record set.
It is not necessary for the type of variable to be similar to the type of column in the DBMS. For instance, one can read a table containing an integer and bind it to a character string. The ODBC performs the conversion internally.
SQLFetch()--Fetch Single Rows
SQLFetch() fetches a single row of data after a select query has been executed. As with all ODBC functions, SQLFetch() returns a status that can be analyzed to determine whether the end of the result set has been reached.
The following example shows how SQLFetch() is used:
RETCODE rc = SQL_SUCCESS;
SQLExecDirect( hstmt, "select models from cars", SQL_NTS );
while ( TRUE )
{
rc = SQLFetch( hstmt );
if (rc == SQL_NO_DATA_FOUND)
{
printf("End of data.\n" );
break;
}
if ( !SQL_SUCCEEDED( rc ) )
{
printf("Error! status is %d\n", rc );
break;
}
}
SQLGetData() and SQLBindCol()--Bind Fetched Data
The previous SQLFetch() example fetches rows from the database, but does not make the data available to the application. The functions SQLGetData() and SQLBindCol() bind the fetched data to variables in the application. SQLGetData() binds the variables after the fetch; SQLBindCol() binds the variables before the fetch. SQLGetData() and SQLBindCol() can be used separately or together, although it is somewhat redundant to do both.
The following example expands the SQLFetch() example to show the use of SQLGetData() and SQLBindCol():
RETCODE rc = SQL_SUCCESS;
SQLCHAR model[21] = "\0";
SQLINTEGER orind = SQL_NTS;
SQLINTEGER orind1 = SQL_NTS;
/*
** Execute the select query.
*/
SQLExecDirect( hstmt, "select model from cars", SQL_NTS );
/*
** Bind the column to be fetched.
*/
SQLBindCol( hstmt, /* Statement handle */
1, /* Column Number */
SQL_C_CHAR, /* C type of variable */
model, /* The fetched data */
20, /* Maximum length */
&orind ); /* Status or length indicator */
/*
** Fetch the data in a loop.
*/
while ( TRUE )
{
rc = SQLFetch( hstmt );
/*
** Break out of the loop if end-of-data is reached.
*/
if (rc == SQL_NO_DATA_FOUND)
{
printf("End of data.\n" );
break;
}
/*
** Break out of the loop if an error is found.
*/
if ( !SQL_SUCCEEDED( rc ) )
{
printf("Error! status is %d\n", rc );
break;
}
/*
** Re-bind the data to be fetched (redundant in this
** case).
*/
SQLGetData( hstmt, /* Statement handle */
1, /* Column number */
SQL_C_CHAR, /* C type of variable */
model, /* The fetched data */
20, /* Maximum length */
&orind1 ); /* Status or length indicator */
printf("model of car: %s\n", model );
}
The above example works, but would work just as well if only SQLBindCol() or SQLGetData() were called. Both are included in the example to show how they are used. Note that SQLBindCol() is called only once and serves to bind data for all successive fetches. SQLGetData() is called after every fetch. SQLGetData() is called in this way to fetch variable-length data, such as large objects.
SQLFetchScroll()--Fetch Record Sets
SQLFetchScroll() returns record sets, or blocks of data. By itself, SQLFetchScroll() does not provide enough information to describe the characteristics of the record set; instead, a series of calls to SQLSetStmtAttr (set query attributes) define the record set characteristics.
The following example shows how the cars table is fetched into a record set containing five rows:
#define ROWS 5
#define MODEL_LEN 21
SQLCHAR model[ROWS][MODEL_LEN]; /* Record set */
SQLINTEGER orind[ROWS]; /* Len or status ind */
SQLUSMALLINT rowStatus[ROWS]; /* Status of each row */
RETCODE rc=SQL_SUCCESS; /* Status return code */
int i; /* Loop counter */
SQLHSTMT hstmt; /* Statement handle */
SQLUINTEGER numRowsFetched; /* Number of rows fetched */
/*
** Declare that the record set is organized according to columns.
*/
SQLSetStmtAttr( hstmt, SQL_ATTR_ROW_BIND_TYPE,
SQL_BIND_BY_COLUMN, 0 );
/*
** Declare that the record set has five rows.
*/
SQLSetStmtAttr( hstmt, SQL_ATTR_ROW_ARRAY_SIZE,
(SQLPOINTER)ROWS, 0 );
/*
** Bind an array of status pointers to report on the status of
** each row fetched.
*/
SQLSetStmtAttr( hstmt, SQL_ATTR_ROW_STATUS_PTR,
(SQLPOINTER)rowStatus, 0 );
/*
** Bind an integer that reports the number of rows fetched.
*/
SQLSetStmtAttr( hstmt, SQL_ATTR_ROWS_FETCHED_PTR,
(SQLPOINTER)&numRowsFetched, 0 );
/*
** Bind the array describing the column fetched.
*/
SQLBindCol( hstmt, /* Statement handle */
1, /* Column number */
SQL_C_CHAR, /* Bind to a C string */
model, /* The data to be fetched */
MODEL_LEN, /* Maximum length of the data */
orind ); /* Status or length indicator */
/*
** Execute the select query.
*/
SQLExecDirect(hstmt, "SELECT model from cars", SQL_NTS);
/*
** Fetch the data in a loop.
*/
while ( TRUE )
{
rc = SQLFetchScroll( hstmt, SQL_FETCH_NEXT, 0 ) );
/*
** Break out of the loop at end of data.
*/
if (rc == SQL_NO_DATA_FOUND)
{
printf("End of record set\n" );
break;
}
/*
** Break out of the loop if an error is found.
*/
if ( !SQL_SUCCEEDED( rc ) )
{
printf( "Error on SQLFetchScroll(), status is %d\n", rc );
break;
}
/*
** Display the result set.
*/
for (i = 0; i < numrowsfetched; i++)
{
printf("Model: %s\n", model[i]);
}
} /* end while */
Column-wise versus Row-wise Binding
The previous SQLFetchScroll() example depicts column-wise binding, which is the default. In column-wise binding, the variable arrays describe the columns of data to be fetched.
It is also possible to set up structures in your program that describe the rows to be fetched, rather than the columns. This is called row-wise binding.
The following program excerpt fetches exactly the same data as the SQLFetchScroll() example, but uses row-wise binding instead of column-wise binding. The structure typedef MODEL_ROW can be considered a snapshot of information about each row. Each column in the row structure consists of the data to be fetched and a row status indicator.
#define ROWS 5
#define MODEL_LEN 21
/*
** Describe a row in the result set.
*/
typedef struct
{
SQLCHAR model[MODEL_LEN]; /* The data to be fetched */
SQLINTEGER orind; /* Len or status indicator */
} MODEL_ROW;
MODEL_ROW model_row[ROWS]; /* The record set */
SQLUSMALLINT rowStatus[ROWS]; /* Status of each row */
SQLHSTMT hstmt; /* Statement handle */
RETCODE rc=SQL_SUCCESS; /* Return status */
int i; /* Loop counter */
SQLUINTEGER numRowsFetched; /* Number of rows fetched */
/*
** Declare that the record set is organized according to rows.
*/
SQLSetStmtAttr( hstmt, SQL_ATTR_ROW_BIND_TYPE,
(SQLPOINTER)sizeof( MODEL_ROW ), 0 );
/*
** Declare the number of rows in the result set.
*/
SQLSetStmtAttr( hstmt, SQL_ATTR_ROW_ARRAY_SIZE,
(SQLPOINTER)ROWS, 0 );
/*
** Bind to a status array reporting on each row fetched.
*/
SQLSetStmtAttr( hstmt, SQL_ATTR_ROW_STATUS_PTR,
(SQLPOINTER)rowStatus, 0 );
/*
** Bind to an integer reporting on the number of rows fetched.
*/
SQLSetStmtAttr( hstmt, SQL_ATTR_ROWS_FETCHED_PTR,
(SQLPOINTER)&numRowsFetched, 0 );
/*
** Execute the select statement.
*/
SQLExecDirect( hstmt, "SELECT model from cars", SQL_NTS );
/*
** Bind each column in the record set structure.
*/
SQLBindCol( hstmt, /* Statement handle */
1, /* Column number */
SQL_C_CHAR, /* Bind to C string */
&model_row[0].model, /* Column to fetch */
sizeof( model_row[0].model ), /* Length of data */
&model_row[0].orind ); /* Len or status indicator */
/*
** Fetch the data in a loop.
*/
while ( TRUE )
{
rc = SQLFetchScroll( hstmt, SQL_FETCH_NEXT, 0 ) )
/*
** Break out of the loop at end-of-data.
*/
if ( rc == SQL_NO_DATA_FOUND )
break;
/*
** Break out of the loop if an error is found.
*/
if ( !SQL_SUCCEEDED( rc ) )
{
printf( "Error on SQLFetchScroll(), status is %d\n", rc );
break;
}
/*
** Display the result set.
*/
for (i = 0; i < numRowsFetched; i++)
{
printf("Model: %s\n", model_row[i].model);
}
} /* end while */
SQLSetCursorName()--Declare Cursor
The term cursor is an acronym for CURrent Set Of Records. A database cursor is similar to the cursor on your computer screen. However, instead of pointing at something on your screen, a database cursor points to a data row set.
Some database vendors make a distinction between client and server-side cursors. However, a cursor declared in an Actian X ODBC program is always a server-side cursor. This means that the properties of the cursor are applied only on the DBMS server of the target database.
An ODBC application can name a cursor directly via a call to SQLSetCursorName():
SQLSetCursorName( hstmt, /* Statement handle */
"C1", /* Cursor Name */
SQL_NTS ); /* This is a null-terminated string */
The above code creates a cursor named C1, which is also visible to the DBMS as C1.
Updatable Cursors
For a cursor to be made updatable, the ODBC Driver imposes a set of syntax rules:
• The cursor must be explicitly named via SQLSetCursorName().
• SQLSetStmtAttr() must be invoked with SQL_ATTR_CONCURRENCY specified as SQL_CONCUR_VALUES.
• The update statement must include the "where current of" clause and refer to the cursor name declared in SQLSetCursorName().
The following code highlights the minimum code required to declare an updatable cursor:
SQLSetCursorName( hstmtS, /* Select statement handle */
"C1", /* Cursor Name */
SQL_NTS ); /* This is a null-terminated string */
SQLSetStmtAttr( hstmtS, SQL_ATTR_CONCURRENCY,
(SQLPOINTER)SQL_CONCUR_VALUES, 0 );
SQLExecDirect ( hstmtS,
"select model from cars where model = 'Hummer '",
SQL_NTS );
SQLExecDirect( hstmtU,
"UPDATE cars SET model = 'HummV ' WHERE CURRENT OF C1",
SQL_NTS );
Cursors versus Select Loops
A loop is an iterative set of fetches. Thus, a cursor loop is a set of fetches using cursors. Select loops are a set of fetches without a cursor defined. The ODBC uses select loops by default. This is true whether or not an ODBC DSN definition is specified.
Declaration of a cursor name is the same as a cursor loop in this discussion.
Select loops fetch multiple sets of rows from the DBMS. This is sometimes referred to as block fetching. A single fetch may appear to return only one row, but often the ODBC driver has already fetched many more rows that are cached in the driver.
Cursor loops must be specified if the cursor is scrollable or updatable.
Note: Cursors loops may need to be specified for Windows applications such as Microsoft Access or Microsoft ADO. If you are see an error message such as “API function cannot be called in the current state,” and are satisfied that your application is coded correctly, try using cursor loops.
Cursor loops may offer better performance for Windows applications, because the ODBC driver returns information that it supports unlimited active statements. This signifies, for example, that ADO applications can re-use existing connections for internal procedures.
Outside of Windows applications, the performance of cursor loops is often comparable to select loops, because the ODBC driver prefetches rows in blocks of 100 when cursors are used. The term prefetch means that multiple rows are fetched and cached in the ODBC driver before they are presented to the application.
If a cursor is declared as updatable, prefetching does not occur in order to preserve the current position for the update. Thus, updatable cursors may be slower that read-only cursors or select loops.
Only one select loop can be active at a time. As a result, select loops cannot be nested. For example, in ADO, multiple recordset objects cannot be retrieved within [Connection].BeginTrans and [Connection].CommitTrans methods. In direct ODBC code, SQLFreeStmt() must be called with the argument SQL_CLOSE before executing another select loop. By contrast, cursors place no limits on the number of active result sets. Cursor loops can be nested.
See the SQL Reference Guide for more information on cursors versus select loops.
SQLFreeStmt()--Close Fetch Loop
After the ODBC application has finished fetching, the cursor associated with the statement handle must be closed. If a cursor was not declared, the ODBC application still must tell the DBMS it has completed fetching.
A cursor is closed, or a select loop completes processing, via the SQLFreeStmt() function, as shown in this example:
/* Stop fetching data */
SQLFreeStmt( hstmt, SQL_CLOSE );
SQLFreeStmt() can be called at any time during a fetch operation. When the SQL_CLOSE argument is specified, all resources associated with the fetch are released. The statement handle can be re-used for other types of queries without having to call SQLAllocStmt() or SQLAllocHandle().