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

Sqlite_Example.livecode

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

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

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

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

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

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

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.

37 Comments

Doug

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

Hi Doug

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

Elanor

Sean Cole

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

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

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

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

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

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

Hi Hanson,

Yes, it worked fine. Thanks.

Paulo

Jean

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

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

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

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

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

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

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

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

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

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

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

oscar

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.

Elanor Buchanan

Hi Oscar

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

http://lessons.runrev.com/m/4071/l/7003-connecting-to-a-mysql-database

An alternative way to communicate with a database is to use a web service

http://lessons.runrev.com/m/4070/l/40932-accessing-web-services-using-livecode

Kind regards

Elanor

David

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.

Elanor Buchanan

Hi David

You can see how to construct an SQL statement using control properties or variables in this lesson

http://lessons.livecode.com/m/4071/l/7003-connecting-to-a-mysql-database

You can also see and example of using placeholders in the SQL statement in the Dictionary entry for revExecuteSQL.

I hope that helps.

Kind regards

Elanor

Kim Duckworth

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?

Regards

Kim

Heather Laine

Hello Kim. That would be an error in the settings, SQLite is supported on both iOS and Android. I'll have it checked/corrected

Simon Schvartzman

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

Heather Laine

does this lesson help?

http://lessons.livecode.com/m/4071/l/11725-how-do-i-create-and-set-up-a-database

Heather

Thanks Heather, sure it helps (I should have been able to find it myself...)

Trevix

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

Trevix

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).
Regards
Trevix

Steve W

What is the proper way to close the database connection?

Elanor Buchanan

Hi Steve W

The command to close a database connection is

revCloseDatabase databaseID

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.

Kind regards

Elanor

Mark

Hi, just downloaded the example stack and noticed "revCloseDatBase" has a typo in it (in command databaseClose)

Matthias Rebbe

Hi, thanks for pointing this out. Example stack was updated. Tinyurl was also not working anymore. That was also corrected.

Philemon63

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

Elanor Buchanan

Hi Philemon63

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.

https://quality.livecode.com/show_bug.cgi?id=23050

Kind regards

Elanor

Add your comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.