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','[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 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.
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