Re: [Sqlrelay-discussion] Scrollable cursors?

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

Oh, I see now that I don't have to seek through results.  Calling
getField(29999, "id") seems to work just fine, giving me the 30000'th record
in the result set.  Still, this seems pretty magical, I'm curious what's
happening here!

Thanks,

--Cal

On Thu, Apr 22, 2010 at 4:46 PM, Cal Heldenbrand <ca...@fb...> wrote:

> 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
>
>
>