Office of Digital Humanities
For this exercise I created a simple template stack called databaseExercise-template.livecode. It can be found in the lesson_materials/Databases folder on the DigHT 310 server. If you don't have access, or just want to throw together your own stack, it is simply a collection of fields and buttons we'll use to try out these database commands and functions. See the screen snapshot below:
On this card there are several fields intended to contain information that you need to establish a connection to the database in a script—host, port, db, userID, and passwd. To the right of those is a larger field in which you can enter SQL commands to be executed by your script.
Below the fields are four buttons where you can write handlers to try out the examples in this exercise.
The LiveCode language includes an extensive library of commands and functions that allow the developer to interact with and maintain databases. A glance at a full listing of LiveCode database commands and functions will reveal a myriad of tools for working with databases. This list can seem daunting to the newcomer. Fortunately, the majority of basic database interactions can be accomplished using only four commands and functions. This exercise will walk you through them.
Open a database connection. This is always the first step in working with a database, accomplished by calling the function revOpenDatabase(). The syntax is:
revOpenDatabase(databaseType,host:port,databaseName,userName,password)
You use it in a statement like:
put revOpenDatabase("mysql","db.server.com:3306","myDB","dbuser","mypasswd") \
into connID
If successful, the function returns an integer that represents your stack's connection to the database server. You should save this id number in a safe place—for example, a global variable, custom property or a field—where you can refer to it in subsequent commands and functions. In this exercise, we'll put it into a field provided for that purpose.
If the function call is unsuccessful, instead of an integer it will return an error message that gives information about why it failed. See if you can induce an error by introducing some kind of syntax error into your function parameters.
How might you control for errors like this in your scripting?
Close a database connection. This is always the last step—you should always close the connection after you have done what you need to do so there aren't a bunch of unused connections left open. The syntax is:
revCloseDatabase connectionID
where connectionID is the id of a previously opened database connection.
If this command fails it will generate an error message that can be read by checking the result function. The most common way for it to fail is passing it an invalid connection ID. Try passing it a valid and an invalid connection ID and note the result. (An easy way to do this is to close a connection once, then immediately try to close it again.)
To avoid errors when closing database connections you can first check to make sure the connection is valid by calling the revOpenDatabases() function. It takes no parameters and returns a comma separated list of all the open connections. So if you wanted to be cautious, before closing a database connection you could make sure it's still open like this:
if connID is among the items of revOpenDatabases() then
revCloseDatabase connID
end if
Another use of the revOpenDatabases function is to close all of the open connections at once, maybe a good safeguard as you're closing the stack:
on closeStack
put revOpenDatabases() into dbConnList
repeat for each item tConn in dbConnList
revCloseDatabase tConn
end repeat
end closeStack
Of course it's not that useful to just open then immediately close the database connection. Let's look at how to extract data from a database, along with how to add, delete or change data.
Bring data from the database into LiveCode. The simplest way to do this is with the revDataFromQuery function, which simply sends the SQL query to the database and returns the data that matches the query into a container (field, variable, etc.) of your choice. Once it's there you can use the data to do whatever you want with it. The basic syntax for this function is:
revDataFromQuery(columnDelim,rowDelim,databaseID,SQLQuery)
Once again, since this is a function you must use it in a statement:
put revDataFromQuery(tab,return,connID,"SELECT * FROM people") \ into fld "result"
Once you've done a successful query try inducing an error and see what value is returned.
Execute other SQL commands. The revDataFromQuery function is only used to execute SQL SELECT statements. To execute any other SQL command (e.g., INSERT, DELETE, UPDATE) you have to use the revExecuteSQL command. Its basic syntax is straightforward:
revExecuteSQL databaseID,SQLStatement
That might look something like this in your script:
revExecuteSQL connID,fld "sqlQuery"
If the command is successful, the result function will contain an integer that reflects the number of rows (records) in the database that were affected by the command. If unsuccessful, the result will contain an error message.
The revExecuteSQL command will execute any correctly formed SQL statement, other than a SELECT statement. You can review examples of these commands from our Creating Databases exercise.