Connecting to a MySQL database
How to connect to a MySQL database via scripts only i.e. without using the Query Builder.
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 open 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 change the button's name to "Connect" and copy the script below into its script.
on mouseUp
-- use a global variable to hold the connection ID so other scripts can use it
global gConnectionID
-- set up the connection parameters - edit these to suit your database
put "livecode.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
put tResult into 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 mouseUpSave the script by clicking "Compile" in the Script Editor, then click the "Connect" button to test.
It should connect to the sample database on livecode.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.
This example is sample code. We recommend never putting usernames and passwords in scripts you distribute (they should be collected from the user!).
Getting data from the database
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
global 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 mouseUpCompile the script and test the Query button. I have put a couple of sample entries into the database, so you should see some information 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
global 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 mouseUpThis 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
This will not 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.
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 its script to the example below:
on mouseUp
global 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 mouseUpSummary
LiveCode does an excellent job of providing the hooks to get into a MySQL database without having to worry about the details.
However you do need to know the basics of SQL in order to retrieve and edit the data once the connection is made.
If you look in the LiveCode Resource Center, in the Database section there is a sample SQLite stack that you can download. Although this deals with SQLite instead of MySQL, the actual SQL syntax is much the same and it provides a good tutorial for learning the basics of SQL as well as an introduction to some more complex queries.
Using the scripts above as a starting point, you can edit the database settings so as to access other MySQL databases, either on your own computer, on another computer on your network or on a server elsewhere.
If I were to make an app for iphone/ipad that uses MySQL to store data from the end-user, can the end-user open my app and see my MySQL database names, table names and username/pw info?
If so, is there a more secure method available?