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.
Adding controls
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 mouseUpAdd 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 databaseConnectWe 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 getDatabaseIDWe 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 mouseUpAgain 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 databaseCreateTableInserting 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 mouseUpAs 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','joe.bloggs@email.com');" into tSQL
put "INSERT into contact_details VALUES ('Jane Doe','jane.doe@email.com');" after tSQL
put "INSERT into contact_details VALUES ('Tom Smith','tom.smith@email.com');" after tSQL
put "INSERT into contact_details VALUES ('Mary Brown','mary.brown@email.com')" after tSQL
revExecuteSQL tDatabaseID, tSQL
end databaseInsertContactDetailsRetrieving 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 mouseUpand 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 databaseGetContactDetailsCreating 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
Hint: setDatabaseID
It seems like we haven't defined setDatabaseID... or is that built-in and should be working?
Thanks!
Doug
Error:
http://skit.ch/bx2x
Code:
http://skit.ch/bx2q