http://developer.mimer.com

Result-set Processing

---

There are two ways of processing a result-set. One method uses SQLBindCol to bind applications variables to the columns of the result set. The second method of processing the result-set is to use SQLGetData.

Using SQLBindCOL

When each row of data is fetched, the column data is copied to the application variables. The following example also illustrates how to use the indicator variable; this either returns the length of character data (a negative length indicates that truncation has taken place), or SQL_NULL_DATA if the data is NULL:

 SQLCHAR    code[4];
 SQLCHAR    currency[33];
 SQLINTEGER codeInd, currencyInd;
 . . .
 
 /* Allocate statement handle */
 SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt );
 
 SQLExecDirect( hstmt, 
                "SELECT code, currency \
                    FROM mimer_store.currencies", 
                SQL_NTS );
                    
 SQLBindCol( hstmt, 1, SQL_C_CHAR, 
             code, sizeof(code), &codeInd );
 SQLBindCol( hstmt, 2, SQL_C_CHAR, 
             currency, sizeof(currency), &currencyInd );
 
 while ((retcode = SQLFetch( hstmt )) != SQL_NO_DATA)
 {
    printf( "%s  %s\n", code, currency );
 }
 
 /* Close the cursor */
 SQLCloseCursor( hstmt );  
 

Using SQLGetData

The second method of processing the result-set is to use SQLGetData; the equivalent of the previous example can be written:

 SQLExecDirect( hstmt, 
                "SELECT code, currency \
                    FROM mimer_store.currencies", 
                SQL_NTS );
                    
 while ((retcode = SQLFetch( hstmt )) != SQL_NO_DATA)
 {
    SQLGetData( hstmt, 1, SQL_C_CHAR, 
                code, sizeof(code), &codeInd );
    SQLGetData( hstmt, 2, SQL_C_CHAR, 
                currency, sizeof(currency), &currencyInd );
                
    printf( "%s  %s\n", code, currency );

Combining Result-set Processing Methods

SQLBindCol and SQLGetData can be combined. The previous two examples used forward-only cursors, which means that they only support fetching rows serially from the start to the end of the cursor.

In modern screen-based application, the user expects to be able to scroll backwards and forwards through the data. While it is possible to cache small result sets in memory on the client, this is not feasible when dealing with large result sets. Scrollable cursors provide the answer.

Scrollable Cursors

Scrollable cursors allow you to move forward and back to any row within the result-set. A statement attribute of SQL_SCROLLABLE specifies that the cursor be opened in scroll mode.

The function SQLFetchScroll supports fetching the next, prior, first and last rows, as well as absolute and relative positioning.

For example:

 /* Allocate statement handle */
 SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt );
 
 /* Set cursor scrollable */
 retcode = SQLSetStmtAttr( hstmt, SQL_ATTR_CURSOR_SCROLLABLE,
                           (SQLPOINTER)SQL_SCROLLABLE, 0 );
 if (retcode == SQL_ERROR) goto error;
 
 SQLExecDirect( hstmt, 
                "SELECT code, currency \
                    FROM mimer_store.currencies \
                    WHERE code LIKE 'A%'", 
                SQL_NTS );
                
 SQLBindCol( hstmt, 1, SQL_C_CHAR, 
             code, sizeof(code), &codeInd );
 SQLBindCol( hstmt, 2, SQL_C_CHAR, 
             currency, sizeof(currency), &currencyInd );
             
 printf( "Original sort order\n" );
 while ((SQLFetchScroll( hstmt, SQL_FETCH_NEXT, 0 )) != SQL_NO_DATA)
    printf( "%s  %s\n", code, currency );
    
 printf( "\nReverse order\n" );
 while ((SQLFetchScroll( hstmt, SQL_FETCH_RELATIVE, -1 ))!=SQL_NO_DATA)
    printf( "%s  %s\n", code, currency );
    
 /* Close the cursor */
 SQLCloseCursor( hstmt );

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se