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@example.com');" into tSQL put "INSERT into contact_details VALUES ('Jane Doe','firstname.lastname@example.org');" after tSQL put "INSERT into contact_details VALUES ('Tom Smith','email@example.com');" after tSQL put "INSERT into contact_details VALUES ('Mary Brown','firstname.lastname@example.org')" 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.