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.