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.

Attached Files

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

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.

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

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

Comments (20)

Doug Sunday Jun 12 at 08:58 AM

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

Elanor Buchanan Monday Jul 25 at 06:19 PM

Hi Doug

Thanks for pointing out that omission. The lesson has now been updated with the setDatabaseID command and getDatabaseID function.

Elanor

Sean Cole Friday Nov 04 at 01:01 AM

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

Elanor Buchanan Friday Nov 04 at 06:27 PM

Hi Sean

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.

Kind regards

Elanor

Mike Walker Monday Feb 13 at 03:32 PM

Hi, Eleanor,
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
Hint revExecuteSQL

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.

Elanor Buchanan Monday Feb 13 at 07:15 PM

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.

Kind regards

Elanor

paulo Tuesday Mar 13 at 03:06 AM

Hi Eleanor,
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,
Paulo

Hanson Schmidt-Cornelius Tuesday Mar 13 at 06:05 PM

Hi Paulo,

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.

Kind Regards,

Hanson

paulo Thursday Mar 15 at 12:08 PM

Hi Hanson,

Yes, it worked fine. Thanks.

Paulo

Jean Saturday Jun 02 at 01:46 AM

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
end if

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?

Hanson Schmidt-Cornelius Wednesday Jun 06 at 06:27 PM

Hi Jean,

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.

Kind Regards,

Hanson

Jean Thursday Jun 07 at 03:41 AM

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.

Jean

Hanson Schmidt-Cornelius Friday Jun 08 at 10:35 PM

Hi Jean,

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:

./adb kill-server
./adb start-server

from the Android SDK platform-tools directory. This restarts the Android Debug Bridge and can help reconnect the emulator again.

Kind Regards,

Hanson

You may get this message when LiveCode was started with the Android Simulator running.

Jessamy Goddard Monday Sep 17 at 06:51 AM

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!!

Jessamy.

Hanson Schmidt-Cornelius Monday Sep 17 at 11:56 PM

Hi Jessamy,

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:

command databaseConnect
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"
end if

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

Kind Regards,

Hanson

Jessamy Goddard Tuesday Sep 18 at 08:01 PM

Dear Hanson,
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
databaseCreateTable
end if

if databaseGetColumn()= empty then
databaseInsertMyTable

Where MyTable is the name of my table,
Column is the name of any column,

It turned out to be very simple.
Thankyou again!!

Best wishes,
Jessamy.

Michael Watkins Monday Sep 15 at 03:45 AM

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

Elanor Buchanan Tuesday Sep 16 at 06:25 PM

Hi Michael

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.

tContactDetails[1]["firstname"]

Kind regards

Elanor

Frantz Wednesday Sep 17 at 08:34 AM

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?

Elanor Buchanan Wednesday Sep 17 at 07:12 PM

Hi Frantz

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.

http://lessons.runrev.com/m/4069/l/14301-how-do-i-read-write-to-files-on-mobile

Kind regards

Elanor

Add your comment

E-Mail me when someone replies to this comment