How to create and use an SQLite database

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 onlu 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.

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.

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.

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

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.

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','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 databaseInsertContactDetails

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

To create and populate the database and display the content we just need to press each button in turn.

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 (3) and Portrait Orientation (4) and choose the rest of your settings (5).

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.

To deploy to Android you need to open the Standalone Application Settings. Check Android in the Android pane (1) and check the SQLite External (2).

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.