How to create and use an SQLite database
This lesson will show how quickly and easily you can set up and start using an SQLite database using LiveCode. This example will work equally well on the desktop and mobile with no changes required. Our example database will contain one table holding contact details.
You can download the sample stack from this url: https://tinyurl.com/y6ahmvd5
Creating our stack
In this example we wil be building our stack into an app so open a new Mainstack from the File menu and set its width to 768 and its height to 1024.
For this simple example we only need 5 controls. We need to be able to
1. Connect to the database, and create it if it does not exist
2. Add a single table to the database
3. Populate the database with some content
4. Retrieve the content from the database and display it
To do this we need 4 buttons to perform these actions and a field to display the information we retrieve from the database.
Drag the buttons and field onto the stack from the tools palette and name them appropriately.
Using databases with LiveCode
LiveCode provides a full range of commands and functions allowing communication with external SQL databases. You use SQL queries to specify the parts of the data you want to work with, to get data and to make changes to the database.
LiveCode provides built in drivers for accessing SQLite databases, no additional installation is required.
For more details on LiveCode database functionality see the Dictionary and the User Guide.
Connecting to the database
The first thing we need to do is establish a connection with the database. To do this we use the revOpenDatabase command.
This command establishes a connection with the database and returns the connection id. We store the connection id as we will need it when we want to communicate with the database. The command will create an SQLite database if it does not already exist.
Select the "Connect to database" button , open the script editor and set the script of the button to
on mouseUp databaseConnect end mouseUp
Add the databaseConnect handler to the card script.
command databaseConnect local tDatabasePath, tDatabaseID ## The database must be in a writeable location put specialFolderPath("documents") & "/runrevemails.sqlite" into tDatabasePath ## Open a connection to the database ## If the database does not already exist it will be created put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID ## Store the database id so other handlers can access it setDatabaseID tDatabaseID end databaseConnect
We also need the commnd setDatabaseID, which stores the database connection id in a script local variable, and the function getDatabaseID, which returns the database connection id so we can use it.
Script local variables are defined outside of any handler, they are then accessible to the scripts of all handlers that appear after the variable definition. In this case we will define sDatabaseID in the card script, above the handlers that use it.
local sDatabaseID command setDatabaseID pDatabaseID put pDatabaseID into sDatabaseID end setDatabaseID function getDatabaseID return sDatabaseID end getDatabaseID
We could put all the code in the button script but I prefer to keep all the database handlers together on the card script.
Adding a table to the database
Next we want to add a table to the database. To do this we construct the SQL statement that will create the table and use the revExecuteSQL command to execute the query in the database.
Set the script of the "Add table" button to
on mouseUp databaseCreateTable end mouseUp
Again the databaseCreateTable handler is on the card script
on databaseCreateTable ## Add a contact_details table to the database put getDatabaseID() into tDatabaseID put "CREATE TABLE contact_details (name char(50), email char(50))" into tSQL revExecuteSQL tDatabaseID, tSQL end databaseCreateTable
Inserting data into the database
The next step is to insert some records into the contact_details table we have just created. Just as in the previous step this is done by constructing SQL Insert statements and using the LiveCode database commands to execute them in the database.
Set the script of the Insert Content button to:
on mouseUp databaseInsertContactDetails end mouseUp
As with the previous steps the Insert Content button calls a handler on the card script
on databaseInsertContactDetails ## Insert names and email addresses into the database put getDatabaseID() into tDatabaseID put "INSERT into contact_details VALUES ('Joe Bloggs','[email protected]');" into tSQL put "INSERT into contact_details VALUES ('Jane Doe','[email protected]');" after tSQL put "INSERT into contact_details VALUES ('Tom Smith','[email protected]');" after tSQL put "INSERT into contact_details VALUES ('Mary Brown','[email protected]')" after tSQL revExecuteSQL tDatabaseID, tSQL end databaseInsertContactDetails
Retrieving and displaying data
Now we have a database with records in it, we need to retrieve that data and display it. Just a before we use and SQL query to retrieve the data from the database but in this case we use the revDataFromQuery function as we want it to return data from the database so we can display it in the field.
Set the script of the "Display content" button to
on mouseUp put databaseGetContactDetails() into field "contact details" end mouseUp
and add the databaseGetContactDetails function to the card script
function databaseGetContactDetails ## Query the database for contact details to be displayed in the field put getDatabaseID() into tDatabaseID put "SELECT * from contact_details" into tSQL put revDataFromQuery(tab,return,tDatabaseID,tSQL) into tRecords return tRecords end databaseGetContactDetails
Creating the databse and displaying the data
To create and populate the database and display the content we just need to press each button in turn.
Building a standalone for iOS
For this example we are building an iOS app so open up the Standalone Application Settings and select the iOS panel. Set the Internal App ID (1) and choose your profile (2). Select the SQLite External from the Inclusions pane (3) and Portrait Orientation (4) and choose the rest of your settings (5).
Deploying to an iOS device
Now deploy your app to your chosen device. As we stated at the start this stack will work just as well as a Desktop application with no changes required.
Setting up the standalone settings for Android
To deploy to Android you need to open the Standalone Application Settings. Check Android in the Android pane (1) and check the SQLite External in the Inclusions pane (2).
Deploying to Android
Then select the device or simulator in the Test Target in the Development Menu, click Test in the menubar and your application will be deployed to the device.
Hi. I copied the scripts from the tutorial into a new stack, but when I click the "Connect" button, it fails with the following error:
Handler: can't find handler
Line: setDatabaseID tDatabaseID
It seems like we haven't defined setDatabaseID... or is that built-in and should be working?
Thanks for pointing out that omission. The lesson has now been updated with the setDatabaseID command and getDatabaseID function.
Why does this example not show you how to actually CREATE an SQLite database. Sure, it shows how to make a file but not how to populate one from scratch. How do you create fields? etc. Not load one thats been pre-made. I need it so that my clients can load in a CSV file (because they can't create sql's themselves) that the software will then convert to SQLite for easy Querying. But there are no guides on how to do this in LiveCode or using SQLYoga
This example actually does create an SQLite database from scratch. The first step is to create the empty SQLite database file, we then use SQL statements to create a table and its fields and insert data into the table.
In LiveCode that is how you would create a new SQLite database. You do need to use SQL for this, there are not any LiveCode specific functions and commands for creating tables, adding fields etc. This all has to be done using SQL and the revExecuteSQL command.
I hope that helps.
I didn't try this tutorial until after you fixed the bug David found, but I found another one. It is fixed in your example file, but it is NOT mentioned in your directions.
I built a stack following your instructions explicitly, and running the stack consistently throws this error when you click the Add Table button:
Type revdberr,invalid connection id
Object SQLite Example
Line revExecuteSQL tDatabaseID, tSQL
What is left out of the instructions is definition of the local variable sDatabaseID in the card script outside (i.e., before) all the handlers. If I understand correctly, because the variable is not defined as local to the script initially, it is being defined implicitly within the setDatabaseID command. And that implicit definition makes the variable local to the command, so its value can't be referenced in the getDatabaseID function later in the code.
The requisite script-local variable definition is present in the example code you posted. I managed to figure this out on my own before I downloaded your example, but it took awhile. I post this comment in hopes of helping anyone else attempting this tutorial avoid this particular headache. This may have been obvious to more experienced users, but I wouldn't count a novice like me being able to see it immediately.
Anyway, thanks for so much that is right and does work.
-- An old Hypercard/Supercard user who is happy to find LiveCode.
Thanks for your comment Mike. Not explaining script local variables and including the variable definition when giving the handlers was an oversight and I have updated the lesson to include that explanation.
I'm glad you have found LiveCode and I hope you enjoy working with it.
Very nice example and instructive. I'm new on LiveCode and I learned reproducing the code behind. However, I have a question for a scenario not covered in the example: once you select a row from the database and put the data in a local variable, all data will be there as a big string separated by the TAB character. So, how do you handle the data? I tried to get each column's data using the chunck "word" but it did not work well because if I have a column "address" with contents "3334 SW 234 av" and a column phone as "4056576565" the statements
put word 1 of tList into field field_address
put word 2 of tList into field field_phone
the field_phone will contain "SW", that is the second word inside the string.
So, how data retrieved from a db is handled in LiveCode?
Thanks for your attention,
LiveCode allows you to specify a delimiting character and then select items that are separated by that character. Have a look at "itemDelimiter" in the dictionary.
You would then use the "item" to select values from the string.
Sample code may look something like this:
set the itemDelimiter to tab
put item 1 of tList into field field_address
put item 2 of tList into field field_phone
Hope this helps.
Yes, it worked fine. Thanks.
I inserted the code below to test if the connection works:
if tDatabaseID is not a number then
answer error "Could not connect to the database"
exit to top
This tutorial works in LiveCode i.e. this if statement doesn't thrown an error. However when I send the app to the android emulator, this if statement is true and says that it cannot connect to the database.
Maybe the database file "sqlite" isn't sent to the emulator. How to ensure that the enumator receives the file and where does it receive it?
I tested your code in the context of this lesson and was not able to reproduce the problem you are describing. The emulator does support SQLite, so the code there should behave in the way you would expect it to work on a device.
Try taking a new copy of the lesson and run it again.
I've resolved the issue of grayed-out Test/Test Target.
But now, when I click of Test, I receive a message "Unknown deployment platform." I never got that error the first time I followed this tutorial, yet I followed the same steps.
you would get the message "Unknown deployment platform" when LiveCode cannot find the emulator after you select the "Test" button. You would normally only be able to select the "Test" button when the emulator was detected. However, if you closed the emulator after LiveCode had detected it, then the "Test" button would still be active and you would get the message you describe.
Try closing down LiveCode and the the Android emulator. Then start the emulator and LiveCode again.
You may also want to try restarting the Android Debug Bridge by running:
from the Android SDK platform-tools directory. This restarts the Android Debug Bridge and can help reconnect the emulator again.
You may get this message when LiveCode was started with the Android Simulator running.
Dear Hanson and Elanor,
thankyou so much for the above lesson. I have managed to use it to complete a database based app that also runs on the simulator.
I have one problem.
I need the simulator to either delete its own database, or to realise it has already made one.
At the moment, if it is reopened, it makes another database and adds it to the previous one. This causes problems in the program.
I wonder if you could tell me how to delete - I have tried from the database information in the dictionary, but it didn't work.
Thankyou in advance.
I am loving LiveCode!!
the SQLite database is file based. So all you have to do is test if the relevant database file exists. I have updated the first few lines of the databaseConnect command to test if the database exists. A dialog is launched if the database exists. Alter the conditional text to serve your purposes:
local tDatabasePath, tDatabaseID
## The database must be in a writeable location
put specialFolderPath("documents") & "/runrevemails.sqlite" into tDatabasePath
## Test if a database exists
if there is a file tDatabasePath then
answer "A database already exists" with "Okay"
## Open a connection to the database
## If the database does not already exist it will be created
put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
If you want to delete the database, then have a look at the delete file command in the dictionary.
Thankyou so much for your help.
I found a way to find the database - if it is already there - which works on both Mac and the iPhone simulator and the device.
In case anybody else wants it...it is:-
if TableName is not MyTable then
if databaseGetColumn()= empty then
Where MyTable is the name of my table,
Column is the name of any column,
It turned out to be very simple.
If i was using PHP and MySQL i could refine the data im trying to get like getting just the first names i would use $row['firstname'] in PHP. how would i do something like this in sqlite and livecode
if i have to use
put "SELECT firstname from contact_details" into tSQL
every time then it will get tedious to get all the info i need
It depends what you want to achieve, you may get the result you want using the revQueryDatabase function, this returns a set of records which you can then set through using commands such as revMoveToNextRecord.
Alternatively you could format the results of the query into an array and access the information in that way e.g.
How can I change the example codes to access on my iPad a SQLite table already populated with data and saved on my C:\drive? How can I import the SQLite table on to the iPad and access the records from there?
You won't be able to access a database on your computer as the iPad, whether its a device or the simulator will not have access to your machine.
You can include a pre-populated SQLite database in your app when you build it by including it in the Copy Files pane of the Standalone Application Settings. Your app can access any files included here using specialFolderPath("engine").
However these files are read only, if you want to be able to update the database you will need to copy it to the writeable specialFolderPath("documents") folder.
See the following lesson for more on accessing files on mobile.
Honestly, I think this way to create DBs is not very intuitive. What if I wan to create an app that has pre-establish tables, and the end user will interact with the tables adding, editing, and deleting records. Or browsing only specific records. How do I do that?
I have created databases before and I cannot wrap my mind around the use LC.
This tutorial shows you how to create a new SQLite database, add tables etc. If you already have a database set up you can just connect to it and start interacting with it. You might find this lesson on connecting to a MySQL database useful
An alternative way to communicate with a database is to use a web service
What I don't see is how to use variables in the Insert statement. For example, suppose I wish to use the contents of some fields on a card, using the field names, such as fld "Name", fld "Comment", etc.
You can see how to construct an SQL statement using control properties or variables in this lesson
You can also see and example of using placeholders in the SQL statement in the Dictionary entry for revExecuteSQL.
I hope that helps.
Thanks. Nice Tutorial. I have it working, but when going to install I was surprised to see the following -
LC9.0 Indy > Standalone App Settings > Inclusions Panel > SQLite - IOS is not listed among the operating systems for which the SQLite External will work.
Is this just a mistake with the graphic in the Stand Alone App Settings, or will SQLite really not work on IOS?
Hello Kim. That would be an error in the settings, SQLite is supported on both iOS and Android. I'll have it checked/corrected
Hi all, is there an example as how to do exactly what is explained here but having the DB somewhere on the cloud (could be on LC Hosting services) instead of having it locally. I would like to have several users being able to access such DB via a LC developed App. I would really appreciate any help with this. Regards
does this lesson help?
Thanks Heather, sure it helps (I should have been able to find it myself...)
Since I discovered this (https://community.filemaker.com/docs/DOC-7041 ), it sounds like a possibility to interact with the OSX Calendar App using SQL Lite.
Is it possible?
Could you elaborate some example?
It has been a long time since it was suggested an interaction between LC and the default desktop calendar.
While waiting for an answer, I made some experiments using your excellent lessons.
I uploaded on LC Sample Stack a "lib_OSXCal" stack that does this: read calendar events from the Calendar Cache file, using SQLite.
I don't even know if this is correct or it messes up the calendar file (I guess reading is ok, while writing could be a problem).
I would appreciate some comments or SQL improvement (I am a NOB on this).
What is the proper way to close the database connection?
Hi Steve W
The command to close a database connection is
You can call this when you choose to close the connection. Some options might be from a mouseUp in a button or on closeCard or closeStack.
I hope that helps.
Hi, just downloaded the example stack and noticed "revCloseDatBase" has a typo in it (in command databaseClose)
Hi, thanks for pointing this out. Example stack was updated. Tinyurl was also not working anymore. That was also corrected.
Hello, if I create a database file with sqlite (via the command line) and as soon as I add a generated column to a table, I get a "The database disk image is malformed" error when I try to read the contents of this database with Livecode. The same database with exactly the same tables but without a generated column works perfectly. I suppose this is due to the fact that Livecode is based on sqlite 3.28 whereas the generated columns were introduced with sqlite 3.31? Happy holidays and thank you for your help
You are right, it is because of the version of SQLite. I have added a bug report in our Quality Control Center about updating SQLite in LiveCode. You can add your email address to the CC list on the bug report if you would like to receive an email when the status of the bug is updated.