Connecting to a MySQL database
How to connect to a MySQL database via scripts only i.e. without using the Query Builder.
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.
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.
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 mouseUp
Save 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!).
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 mouseUp
Compile 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.
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 mouseUp
This uses a placeholder technique for inserting data into SQL command. When creating the SQL command, use
: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
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.
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.
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 mouseUp
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.