SQLite implementation question

[glook]

Hi forum members,

It's my first post :sign0144:- not only a raw newbie to Basic4pp but also new to mobile device development and SQLite - please forgive naïve questions and point me in the right direction if this has already been covered!

I'm writing an application that needs to be able to browse and show records in a large database (adjacent records may be of interest to the user). In this implementation what is the best way to achieve this? Is there a way of creating a "recordset" that enables move forward/back and findfirst/next? (as in VB/DAO and AFAIK some other SQLite wrappers).

I have a working preliminary B4ppc program that uses an invisible Table control populated by ExecuteTable, then just track or give the Row Index. Jumping to a specific row is done by simply string searching the column holding the key field. It works, but is it the most efficient way?

Any advice or opinions?

Geoff.

 

[Erel]

Using ExecuteTable is fine if the table is filled with a small number of rows (couple of hundred rows).
There are other alternatives:
- You could use a Reader object to read the query result one row after another.
- Create a 'key' column in the SQL database and use SELECT ... WHERE key = 2323.
- Use the built in key column ROWID to read a specific row: http://www.b4x.com/forum/showthread.php?t=978

 

[glook]

Thanks Erel - good tips.

A Reader object on its own wouldn't work - it needs forward and backward movement plus jumping to specific records.

I also thought using a key column could help - tried tracking just the existing primary keys in a Table control, then selecting single records.

ROWID is new to me - very handy! I am trying this in place of the of the primary key, which is a name field.

I've also changed to viewing to small batches of up a few hundred records at a time (usually much fewer), instead of the whole main table.

All of the methods work, but still much too slowly on the device (runs great on the desktop!). I'm still using Table controls to keep track of the ROWIDs - easy to code but would it be faster to use arrays instead? Individual SELECT WHERE ROWID = is also slow. The current SELECT statement has 5 JOINs to small simple tables - could this cause bad performance with a big DB file?

The DB size is obviously a factor - is select by key or ROWID greatly effected by physical file size? Should I split tables with BLOBs to separate files? (as in my desktop JET app).

Geoff.

 

[Erel]

Can you explain a little bit what data are you trying to show the user and with what interface?

 

[glook]

The database basically holds reference data for plants. I'm attempting to write a handheld implementation of a mature desktop app, info at http://www.deeproot.co.uk

First, I must say that the ppc work is strictly a prototype and the final design may have reduced data and functions, depending on my findings. The progress made in just over one week is a fantastic testimony to Basic4ppc :sign0098:

The main table has 46 columns, mixture of text, numeric and boolean/bit - most can be directly shown on the form, 5 are numeric codes needing look-up tables for the text. 3 columns are for internal program use. Primary key is the Latin name, records are presented to the user in "logical" alphabetic order, which is not quite the same as the key. Separate table holds pictures, any number per plant. The desktop app has 16 tables in all.

Records are shown one per screen with the info grouped in panels/tabs and any images as small thumbnails (limited to 5 on the ppc, clicking opens bigger picture). Records can be found in several ways, usually by partial name search on the key or 2 other name columns. The ability to "browse" records of similar name or plant genus is important. Clickable name lists will be used for more complex search results and user saved lists.

Sorry about the lengthy description!! I know the limitations of the device will lead to compromises. I think the size of the image data will be an issue. Testing is being done on an iPaq 6915 with a 1Gb SD card. The DB file is about 250Mb.

Geoff.