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.
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.
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]
[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
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.
[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