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:
- Open a connection to the database.
- Get data from the database - query the database.
- Alter the database: add, edit or delete records.
- 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 "runrev.com" 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
else
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 runrev.com 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
else
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."
else
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.
Disconnecting[]
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