Livecode Wiki

Database setup[]

LiveCode has set up a sample database which these scripts will use, but to use them for your own database, you will need to know the address of the database, the port number (if different from the default), your user name and password. Basics

There are 4 main operations that you need to be able to do:

  1. Open a connection to the database.
  2. Get data from the database - query the database.
  3. Alter the database: add, edit or delete records.
  4. Close the connection to the database.

When you make a connection to a database, you will be assigned a connection ID. You must use this connection ID in all the other operations to tell your scripts which database connection to use.

Connecting to the database[]

Create a new stack and drag over a new button from the Tools palette. use the Inspector to the button's name to "Connect" and copy the script below into its script. We'll use RevOpenDatabase:

on mouseUp
  put "" into tDatabaseAddress
  put "runrev_test" into tDatabaseName
  put "runrev_example" into tDatabaseUser
  put "example" into tDatabasePassword	
  -- connect to the database
  put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult
  -- check if it worked and display an error message if it didn't
  -- & set the connection ID global
  if tResult is a number then
     # The "g" prefix = Global.
     put tResult into gConnectionID
     #Use a custom property to save the connection ID, this way is available to all
     set the ConnectionID of this stack to gConnectionID  
     answer info "Connected to the database." & cr & "Connection ID = " & gConnectionID
     put empty into gConnectionID
     answer error "Unable to connect to the database:" & cr & tResult
  end if
end mouseUp

Save the script in the Script Editor, then click the "Connect" button to test.

It should connect to the sample database on and tell you when it has succeeded.

If it gives an error check the script ,especially the four variables that hold the connection information.

Note: if you test the button with incorrect settings, it may take a long time to fail, so don't panic if it looks like everything is frozen.

Getting data from the database[]

Example of the stack

To get data from the database, construct a standard SQL query, then use the revDataFromQuery function to retrieve the matching data.

Drag another button from the Tools palette, and use the Inspector to change it's name to "Query".

Now drag over a scrolling text field and call it "Data". The names of the buttons are not so important, but the script is going to refer to field "Data", so make sure it is named.

Copy the script below and use it to set the script of the "Query" button:

on mouseUp
  -- check the global connection ID to make sure we have a database connection
  put the connectionID of this stack into gConnectionID
     if gConnectionID is not a number then
        answer error "Please connect to the database first."
        exit to top
     end if	
     -- construct the SQL (this selects all the data from the specified table) 
     put "Table1" into tTableName	-- set this to the name of a table in your database
     put "SELECT * FROM " & tTableName into tSQL	
     -- query the database
     put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData	
     -- check the result and display the data or an error message
     if item 1 of tData = "revdberr" then
        answer error "There was a problem querying the database:" & cr & tData
        put tData into field "Data"
     end if
end mouseUp

Save the script and test the Query button. I have put a couple of sample entries into the database, so you should see some informations appear in the "Data" field.

Adding a new record to the database[]

This uses a similar technique to the one used for querying the data: construct an SQL command to add the new data and then use a LiveCode command to send this SQL to the database and get a response. In this case, the LiveCode command is revExecuteSQL.

Make a new button as before, call it "Add Record" and copy in the script below:

on mouseUp
  -- check the global connection ID to make sure we have a database connection
  put the connectionID of this stack into gConnectionID
     if gConnectionID is not a number then
        answer error "Please connect to the database first."
        exit to top
     end if	
     -- edit these variables to match your database & table
     -- this assumes a table called Table1 with 3 fields
     put "Table1" into tTableName
     put "firstName, lastName, birthDate" into tFields
     put "Mary" into tFirstName
     put "Smith" into tLastName
     put the short date into tBirthDate	-- this is nonsensical but gives some variation to the data	
     -- construct the SQL - the :1, :2 & :3 placeholders in the SQL will be filled by variables in the revExecuteSQL line
     put "INSERT INTO " & tTableName & " (" & tFields & ") VALUES (:1, :2, :3)" into tSQL	
     -- send the SQL to the database, filling in the placeholders with data from variables
     revExecuteSQL gConnectionID, tSQL, "tFirstName", "tLastName", "tBirthDate"	
     -- check the result and display the data or an error message
     if the result is a number then
        answer info "New record added."
        answer error "There was a problem adding the record to the database:" & cr & the result
     end if
end mouseUp

This uses a placeholder technique for inserting data into SQL command. When creating the SQL command, use :1, :2 etc to show where the variables are going to be.

Then in the revExecuteSQL command, add the names of the variables as extra parameters after the SQL command itself. These are then inserted in order i.e. in the example above, the contents of the variable tFirstName will be used in place of :1 and the contents of tBirthDate will be used wherever :3 appears.

Click this button to test. You will get a dialog reporting success or failure. Then click the "Query" button again and you should see a new record added to the list.

Editing or deleting an existing record[]

I'm not going to go into this in detail, because it is almost exactly the same as adding a record.

To edit an record, the SQL has to be in the following form:

UPDATE Table1 SET birthDate='12/06/1970' WHERE firstName='Mary' AND lastName='Smith'

And for deleting:

DELETE FROM Table1 WHERE firstName='Mary' AND lastName='Smith'

Then use revExecuteSQL and check for errors as before. Generally speaking, if you need to retrieve data, use revDataFromQuery; if you don't need to retrieve data, use revExecuteSQL.


It is always a good idea to disconnect from the database when you have finished. All connections will close automatically when your application quits, but it is still good practice to do it yourself.

Make another button called "Disconnect" and set it's script to the example below:

on mouseUp	
  put the connectionID of this stack into gConnectionID
  -- if we have a connection, close it and clear the global connection ID
  if gConnectionID is a number then
     revCloseDatabase gConnectionID
     put empty into gConnectionID
  end if
end mouseUp

Working with binaries[]

In order to upload binaries, you need to use the variable in the revExecuteSQL.

The SQLStatement may contain one or more placeholders, which are sequential numbers prepended by a colon (:). The revExecuteSQL command substitutes the corresponding item in the variablesList for each of these placeholders. For example, if you have two variables called "valueX" and "valueY", you can use a SQLStatement that includes placeholders as follows:

revExecuteSQL myID, "insert into mytable values(:1,:2)", "valueX","valueY"

when using binaries, you have to insert the *b prefix in variable name; so if you variable containing a binary is "valueX", the correct code is:

revExecuteSQL connID, "insert into mytable values(:1)", "*bvalueX"

Since the revExecuteSQL command strips the binary marker "*b" and passes it to the database as binary data, rather than text data.

In order to get binary you must use revQueryDatabase and revDatabaseColumnNamed (no other way), for example to get the image data store in a database where images data are in a image column:

put revQueryDatabase(connID, "SELECT * FROM images WHERE id=" & field "id" & ";") into tRecordSet
put revDatabaseColumnNamed(tRecordSet, "image", tImage) into tError #tImage contains data
revCloseCursor tRecordSet

Example with UPDATE:

 put "UPDATE flags SET  logo=:1  WHERE ID="& tID &" ;" into tSQL 
 revExecuteSQL connID,tSQL,"*blogo