[Sqlrelay-discussion] Scrollable cursors?

[Cal H. <ca...@fb...>]

Hello everyone,

In experimenting with porting my DB2 CLI application over to SQL Relay's
API, I haven't found how to implement scrollable cursors... but I believe I
found a workaround, and I'm curious why it appears to be faster than
cursors.

Here's a little snippet of what I'm doing with DB2:

// set cursor type to static.
SQLSetStmtAttr( *stmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_STATIC,
0 );

SQLExecute( *stmt );

SQLFetchScroll( *stmt, SQL_FETCH_ABSOLUTE, firstfetchoffset );

// after the first fetch, I call:
SQLFetch( *stmt );

Selecting any given 5 rows out of a 40,000 row result set takes around 300 -
400ms with this method.

To implement a similar behavior, I hacked this test up in about 5 minutes.
This throws out result set buffers until it reaches row 30000, then fetches
5 rows and breaks out.

cur->setResultSetBufferSize(100);
cur->sendQuery("select id from logon");
int zoom = 30000 / 100;
for ( unsigned int i = 0 ; i < 30000 ; i += zoom )
{
        if ( (tmp = (char *)cur->getField(i, (uint32_t)0)) != NULL )
             printf("skip %d: %s\n", i, tmp);
        else
              break;
}
for ( unsigned int i = 29999 ; i < 30005 ; i++ )
{
    if ( tmp = (char *)cur->getField(i, "id") )
         printf("skip %d: %s\n", i, tmp);
}


This process seems to work fine, and happens in about 50ms!  I guess I'm
just curious how this is happening internally, and how seeking through the
result set in this manner is so much faster than a static cursor.   (And
also if there is a better method to accomplish this)

Thanks for your time!

--Cal