On 8/8/06, Lijia Jin <[EMAIL PROTECTED]> wrote:
select string from string where ... limit 1 offset ? ;
and replace this offset value with the row number user supplied. This
solution can work but it requires modifying the SQL query and calling
sqlite3_reset for each get-row request.
This works very well, but your point is also correct.
The code is pretty trivial though:
bool function( int ImageIndex )
{
int i;
char* sql = "SELECT Path FROM Display ORDER BY
SortOrder LIMIT 1 OFFSET";
char sz[290];
i = sprintf( sz, "%s %d", sql, ImageIndex );
//Debug( sz );
sqlite3_stmt* pStmt;
if ( sqlite3_prepare( db, sz, i, &pStmt, NULL ) != SQLITE_OK )
return false;
bool Result = false;
for ( i = 0; i < 8; ++i )
{
// execute select
int rc = sqlite3_step( pStmt );
// got a result?
if ( rc == SQLITE_ROW )
{
const unsigned char* p =
sqlite3_column_text( pStmt, 0 );
// nulls changed to blank string
p = p ? p : (const unsigned char*)"";
ImagePath = (char*) p;
Result = true;
break;
}
// nothing returned
if ( rc == SQLITE_DONE )
{
ImageIndex = 0;
break;
}
// retry errors
}
// clean up when finished
sqlite3_finalize( pStmt );
return Result;
}
Essentially what we needed is a random access iterator because the
sqlite3_step is just a one direction, single step iterator. I did search the
mailing list but can't find anything I am looking for. Did I miss something
and there is already a simple solution for this?
SQL is designed to operate on sets. That's what it does most efficiently.
It's much better to write
update mytable set flag = 0 where index < 1000;
than to write
for ( i = 0; i < 1000; i++ )
update mytable set flag = 0 where index = i;
A random iterator is useful for some problems, but keep in mind the
right way to use it.
