Using an SQLite database

I have an SQLite database with four tables which is used throughout the entire life of my program. One table has 2 columns and 4 rows; another 11 columns and 16 rows; yet another has 4 columns and 16 rows; and the largest has 6 columns and 363 rows.

Is it reasonable that I read all the tables into four separate arrays when the program first runs, or is database access fast enough that I read the data as I need it. I don’t ever have a need to write back to the database.

Thanks

Either way. Reading as you need it should be fast enough

Thanks Karen,

I wasn’t sure about loading arrays, so I guess I will give both methods a try.

I wouldn’t load into arrays, load into record sets. Add a global property something like rsMyRecordset of type record set and then query your dB into your record set and use as required.

OK thanks Mike. I think I can see what you mean, and will try it. I suppose I should be able to find something about it in a tutorial or the Docs.

Am I right in assuming that if I load the entire table of 6 columns and 363 rows, it will be there for me to get any part of the table that I want, when I want it, and that I would just work through the record set as if I had just queried the database. Is that correct. If so that would be neat, because I want to query the database at least twice during the life of the program.

The recordset would be in memory when you query it (SELECT * IN TABLE blahblah). If you don’t close it… it will remain available for the life of your program being open.

Be aware that depending on the database engine involved… at recordset is ONE WAY… you can move to the next record but MOVEPREV will not work (SQLite is a prime example)… So it cannot be randomly accessed without going back to the database again

For that small amount of data, I think you would be fine loading the data into memory. I recommend a Dictionary for this (with a class that maps to each table) as lookups are faster than using an array. A RecordSet is not really a data store, it is a way to access the data in a DB.

Dave, MoveNext, MovePrevious, MoveLast and MoveFirst are supported for record sets, see here

[quote=73680:@Cliff Strange]OK thanks Mike. I think I can see what you mean, and will try it. I suppose I should be able to find something about it in a tutorial or the Docs.

Am I right in assuming that if I load the entire table of 6 columns and 363 rows, it will be there for me to get any part of the table that I want, when I want it, and that I would just work through the record set as if I had just queried the database. Is that correct. If so that would be neat, because I want to query the database at least twice during the life of the program.[/quote]

Yes,

[code]Dim rsMyRecordSet as RecordSet //Make rsMyRecordSet global

rsMyRecordSet = myDB.SQLSelect(“SELECT * FROM myTable”)

if rsMyRecordSet.recordcount > 0 then
for n as integer = 0 to rsMyRecordSet.recordcount - 1
msgbox rsMyRecordSet.field(“myFieldAnyofMy6Columns”).StringValue
rsMyRecordSet.moveNext
next n
end if

[/code]

Thanks all for the input. I must admit my head is spinning a little at the moment (first foray into databases with Xojo), but I will try both methods (RecordSet and Dictionary) to gain some experience as I am sure they both have their good points and their not so good. Main thing is, the more I look at Xojo the more I like it.

I did check the link you supplied in the previous post, Mike. I see that SQLite is not specifically named, but REALSQLdatabase is. Are they comparable?

Also for Paul’s suggestion regarding a Dictionary. I haven’t used them yet, so it would be a good opportunity to learn how.

Mike, depending on the database, you may be keeping database resources open so long as you keep the RecordSet around. You may be setting read locks which unnecessarily block write requests. You would need specific plugin and client library source to know if this is happening for sure. I would strongly suggest NOT keeping RecordSets around for longer than you need for copying result data out. This goes hand in hand with the general recommendation to NOT use database transactions as a user-time locking mechanism.

Sorry it is probably an old link. I believe (someone confirm this) REALSQLdatabase was RealSoftware’s implementation of SQLite under RealBasic and is now deprecated. You should use SQLiteDatabase however I believe the functionality is almost identical. Again if someone could confirm what the difference is.

SQLiteDatabase was added with the first Xojo release as a replacement for RealSQLDatabase. Both use SQLite and are mostly comparable. RealSQLDatabase is now deprecated, so you should use SQLiteDatabase.

Please refer to the Lang Ref documents
mySQL Supports only MoveNext method
MS SQL Server Supports only MoveNext method
PostgreSQL Supports only MoveNext method

[quote=73894:@Dave S]Please refer to the Lang Ref documents
mySQL Supports only MoveNext method
MS SQL Server Supports only MoveNext method
PostgreSQL Supports only MoveNext method[/quote]
This thread is about SQLite databases though

Brad, the OP said he had no need to ever write back to the database so locking should not be a problem

That’s right, I never have a need to write back to the database. At the very end of the program I write one bit of information to a Preference file.

Thank you all for this interesting information. Xojo Forums are excellent (I think I am repeating myself from a previous post :slight_smile: ).