Populating large tables Of SQLite DataBases with LC

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller

Post Reply
danielrr2
Posts: 3
Joined: Mon Aug 25, 2025 9:41 am

Populating large tables Of SQLite DataBases with LC

Post by danielrr2 » Mon Aug 25, 2025 9:58 am

Is it possible to use LiveCode to populate a table in an SQLite database? If so, what are some best practices or tips for writing the script when the table needs to handle more than 20 million records?

Best,
Daniel

dunbarx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 10354
Joined: Wed May 06, 2009 2:28 pm

Re: Populating large tables Of SQLite DataBases with LC

Post by dunbarx » Mon Aug 25, 2025 2:49 pm

Danielrr2, are you a bot?

Happy to reply if you are not.

Craig

danielrr2
Posts: 3
Joined: Mon Aug 25, 2025 9:41 am

Re: Populating large tables Of SQLite DataBases with LC

Post by danielrr2 » Mon Aug 25, 2025 11:06 pm

Hi Craig,

As Marina and the Diamonds would say, I am not a robot. However, I’ve been having some trouble posting in this forum (it has happened to me before). The server either didn't recognize my username, my email, or my password — or it seemed to recognize them but then complained that my account had been ‘inactive.’ The only solution it suggested was to contact an administrator, but it doesn’t tell me how. :( But now I think I'm OK.

So, the bottomline: I am not a robot. I'd appreciate your help on this (as I did in the past)

stam
Posts: 3105
Joined: Sun Jun 04, 2006 9:39 pm

Re: Populating large tables Of SQLite DataBases with LC

Post by stam » Tue Aug 26, 2025 12:36 am

The Marina and the Diamonds reference is good ;)
However Craig’s point is that this is a common topic and should be no issue googling it. This was pretty much the first result I found: https://lessons.livecode.com/m/4071/c/16767

However you will also find a lot if info in the built in API documentation (the “dictionary”), just search for “database”. There is native support for SQLite, MySQL, Postgres and ODBC. The only difference between these is how you connect.
Last edited by stam on Tue Aug 26, 2025 12:53 am, edited 1 time in total.

stam
Posts: 3105
Joined: Sun Jun 04, 2006 9:39 pm

Re: Populating large tables Of SQLite DataBases with LC

Post by stam » Tue Aug 26, 2025 12:49 am

As for retrieving data I wrote up something short when experimenting with Reddit (I gave it up due to lack of interest). This is the text from that particular post:

Retrieve SQL data in LiveCode
When you connect to the database, you receive a connection ID (an integer - if not a number then contains error information) that you can use to read data from the database or manipulate the data stored therein.

There are two functions for getting information out of the database:

1. The first function is revDataFromQuery, which returns a single variable, with the selected fields ofthe records that fit the criteria in your SQL query in TSV text format.

Example:

Code: Select all

put "SELECT cust_id,cust_name FROM Customers" into tQuery
put revDataFromQuery(return,tab,tConnectionID,tQuery) into tData
put tData into field "Table Field"
You would have a return-and-tab-delimited list like this:
000001<tab>Jane Doe<return>
000002<tab>Jeff Doe<return>
000003<tab>John Doe<return>
This function is great if you're looking to display some data easily and quickly. But it would be hard to parse out individual fields, and that's where the next function comes to play.

2) The second function is revQueryDatabase, which executes the query and returns a cursor ID - this doesn't contain the data itself, it's an integer that points to a 'database cursor' which itself is a collection of pointers to the records in the database that fulfil the SQL query's requirements.

You can think of this as navigating cards in a stack where the stack is identified as the cursor ID.

To determine how many records there are and which is the current one
* revNumberOfRecords(<cursor id>)
* revCurrentRecord(<cursor id>)

To navigate the records in the result set, you use the commands:
* revMoveToFirstRecord <cursor id>
* revMoveToPreviousRecord <cursor id>
* revMoveToNextRecord <cursor id>
* revMoveToLastRecord <cursor id>

To determine what fields are in those records, you use:
* revDatabaseColumnCount(<cursor id>)
* revDatabaseColumnNames(<cursor id>)

To fetch the individual fields of the current record, you use:
* revDatabaseColumnNumbered(<cursor id>,<column number>)
* revDatabaseColumnNamed(<cursor id>,<column name>)

To release the result set from memory, you use:
* revCloseCursor <cursor id>

danielrr2
Posts: 3
Joined: Mon Aug 25, 2025 9:41 am

Re: Populating large tables Of SQLite DataBases with LC

Post by danielrr2 » Tue Aug 26, 2025 11:09 am

Thanks so much for all the info Stam. I'm sorry I missed the obvious places to look for the info. Thanks for your patience as well :)

LCMark
Livecode Staff Member
Livecode Staff Member
Posts: 1234
Joined: Thu Apr 11, 2013 11:27 am

Re: Populating large tables Of SQLite DataBases with LC

Post by LCMark » Tue Aug 26, 2025 2:54 pm

@danielrr: One particularly important thing when using SQLite is to remember to explicitly begin and end a transaction around each 'batch' of SQL commands you are running to insert/modify data.

If you don't then an implicit one will be created around each individual SQL command.

This is relatively expensive (particularly on Windows!) as SQLite has to acquire locks on files whenever a transaction starts - so you only want it to do that when necessary.

Note: You don't have to do this if just using 'SELECT' - as SQLite will only take a lock when it needs to modify or add data/records.

stam
Posts: 3105
Joined: Sun Jun 04, 2006 9:39 pm

Re: Populating large tables Of SQLite DataBases with LC

Post by stam » Tue Aug 26, 2025 4:10 pm

LCMark wrote:
Tue Aug 26, 2025 2:54 pm
@danielrr: One particularly important thing when using SQLite is to remember to explicitly begin and end a transaction around each 'batch' of SQL commands you are running to insert/modify data.
Hi Mark - a couple of years ago I needed to do a large number of SQL inserts. I had these batched in a begin transaction statement and it was very slow. I think either you or Ali recommended I just do these in a loop and with individual transactions and it was magically faster - something about overheads.

Is that not the case anymore?

LCMark
Livecode Staff Member
Livecode Staff Member
Posts: 1234
Joined: Thu Apr 11, 2013 11:27 am

Re: Populating large tables Of SQLite DataBases with LC

Post by LCMark » Wed Aug 27, 2025 2:22 pm

@stam: That would be this thread - viewtopic.php?f=18&t=37824...

Originally you were doing:

Code: Select all

BEGIN
<single insert>
COMMIT
(Which is no different to just doing <single insert> - as that creates an implicit transaction}.

My advice was to do:

Code: Select all

BEGIN
repeat for each insert
    <single insert>
end repeat
COMMIT
The difference being that the lock is taken once before all the inserts and dropped afterwards; rather than being taken before each insert and dropped after each insert.

We also discovered that single insert statements was generally a bit quicker than a large single INSERT statement with lots of records of values.

stam
Posts: 3105
Joined: Sun Jun 04, 2006 9:39 pm

Re: Populating large tables Of SQLite DataBases with LC

Post by stam » Wed Aug 27, 2025 8:36 pm

Thanks Mark :idea:

Post Reply