Am 08.08.2006 um 13:15 schrieb Lijia Jin:
Hi,
I am new to sqlite and need some help for random accessing a table
row using
Sqlite C API.
The project I am working on supports network users to query the sqlite
database from remote sites. We like to provide an interface so that
user can
access any row in a result table without going through all the
prior rows. I
know this can be implemented by the sqlite3_get_table function but
this
requires too much memory and it's not that easy to handle database
update. I
like to use sqlite3_step and the only solution I can come up with
is to use
the offset value:
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.
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?
In any case you'll have to run one query per row since you don't want
to cache the entire dataset.
Another alternative to using LIMIT 1 OFFSET <x> would be to have the
initial query just return the rowID (assuming all your data is from a
single table), e.g.
SELECT rowID FROM MyData WHERE name LIKE 'john';
Cache the rowIDs and run your SELECT data query for each row, passing
the rowID:
SELECT name, address FROM MyData WHERE rowID=?
Not much difference to the LIMIT 1 OFFSET ? query, except if you have
a complex condition to evaluate, accessing rows by ID will be much
faster than using an offset, which essentially needs to re-evaluate
the condition on each query.
</jum>
