Last year, when I found and started learning Xojo and SQLite, didn’t find the DatabaseRecord and just started using SQLite commands (following some examples from Xojo and Webinars).
I just read: DatabaseRecord and in the section ‘See Also’ there is no mention of SQLiteDatabase but REALSQLdatabase, there it say please use SQLiteDatabase as a replacement.
I recommend reading about SQLiteDatabase and SQLitePreparedStatement. Learn from the examples that come with Xojo.
SQLitePreparedStatement, with some practice, will make sense and will help you avoid sql injection and other things.
Early this year I was creating a program to download several thousand records and load them to a database. I had the for next loop writing each record (like you are doing) and on mac I didn’t see any mayor change from a few hundred to several thousand. This was not the case with windows, the speed difference was big. Wrapping the for next in a transaction (as Dave mention) solved the speed problem.
Basically, you want to start the transaction with:
DB.SQLExecute("BEGIN TRANSACTION") // maybe app.db.SQLExecute with your code
do the for next,
execute the SQL insert (check Adding Data to a Database here)
check for any error, then if no error:
DB.Commit
if you have an error, you can show the error and to a DB.Rollback.
Here is some information about the performance:
[quote]Performance
If you commit after every database change, you force your database to do a lot of work behind the scenes to make the data permanently available. This is not a big problem when dealing with small amounts of data, but it can really add up when dealing with lots of data.
For example, if you are importing thousands of rows of data into a specific table, committing after each row could cause your import to take several minutes because of all the overhead. Switching to a transaction that only commits at the end (or even every 1000 rows) could result in a tremendous improvement. I’ve seen times drop from several minutes down to several seconds when using a transaction in this manner.
Transactions give you better performance.[/quote]
Hope this is helpful.