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 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

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.

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 "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
		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 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

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 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 infomation 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 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.

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
	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

Summary

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.

36 Comments

Maggie

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?

Jonathan

Can your system cope with complex joins to pull data from related tables?

Elanor Buchanan

Hi Jonathan, yes LiveCode can cope with complex joins. The database commands and functions execute SQL on the database so you need to construct the SQL statement for the join and then use the relevant LiveCode command to query the database.

I hope that helps.

Elanor

Elanor Buchanan

Hi Maggie

No, the user will not be able to see this information once your stack is built into an iOS app.

I hope that helps.

Elanor

Curt

I get an error message: "Unable to connect to the database: Host 'user-XXXX.cable.mindspring.com" (where XXXX is some code that presumably identifies me?) is not allowed to connect to this MySQL server"

Is there something else involved in getting permission to connect?

Hanson Schmidt-Cornelius

Hello Curt,

the message you are getting comes from the MySQL database, so you should be able to track the reason for this down from the MySQL documentation directly.

It looks like you may not have set up an account for the user name and host name you are using when connecting to the database.

Hope this helps.

Thanks,

Hanson

Bill Cavagnaro

I haved followed your example on connecting to a Mysql data base and all parts of the example work correctly execpt the add record exaple. I recive no error but no record is added to the data base. Using a debug stop I am able to read the veriables and find that the data base is open but that no record is added.
The problem seems to be with the VALUES part of the INSERT statement. I have put the debug stop at the revExecuteSQL statemdnt and find that the tSQL veriable contains the table name I am using and the fields I am using but after the value statement only has the place holding numbers and not the values of the parameters they repersent.
can you advise?

Hanson Schmidt-Cornelius

Hi Bill,

there could be a number of reasons for this behaviour.

It is hard to tell what exactly is causing this without the code in front of me and seeing the database table and column names. I am assuming that there may be no data in the variable for which you are using :1, :2, :3 to insert the data.
Would recommend coping and pasting the code again, just in case something got missed out.

Kind Regards,

Hanson

Brad

In connecting to my MySQL database, I need to also pass in the port # in order to access the database. I don't see in the revOpenDatabase function the support parameter for port. How can I add this?

Thanks.

jaap

Editing or deleting an existing 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.

Can anyone send me an example of a script to update?
Can anyone send me an example of a script to delete?

I cant get it to work.

Hanson Schmidt-Cornelius

Hi Brad,

yes you can add the port to access the MySQL database. The syntax for this is as follows:

revOpenDatabase ("mysql",host[:port], databaseName, [userName], [password], [useSSL], [socket], [timeout], [autoReconnect])

You can find more information on this in the LiveCode Dictionary.

Kind Regards,

Hanson

Peter

What is the correct format for SELECT when you need only one record. I have tried many permutations of the SELECT in the example but with no luck. Thank you.

Peter

Hanson Schmidt-Cornelius

Hi Peter,

you should use the standard MySQL syntax for this: LIMIT. Try something like:

SELECT * FROM mytable LIMIT 1

"mytable" is the table from which you would like to extract the data.

Kind Regards,

Hanson

waprothero

can I connect to a mySQL database using SSH? This would require specifying both the port for the database address, and the host's local database port.

Hanson Schmidt-Cornelius

Hi Waprothero,

you can connect to mySQL using SSL on platforms that support the LiveCode SSL library.
Have a look at the dictionary entry for "revOpenDatabase" and the relevant "useSSL" flag.

This functionality only works if you include the relevant custom library in your standalone application. In the inclusion section of the General screen of the Standalone Application Settings window, make sure that "SSL & Encryption" is selected in the list of script libraries.

Kind Regards,

Hanson

David Buckley

I see in some code you use square brackets to get at the spefiic fields but if following this example how do i print out the spefici fields not in one big txt field

Hanson Schmidt-Cornelius

Hi David,

there are several ways to do this. I would consider the following three ways when getting data out of a database:
1. Implement a select statement that only returns the data you need. This is kinder to the database, is potentially faster and can give you exactly what you are looking for.
2. If you do want to get data from a number of tables and return it as one block of data, then you can use the LiveCode string manipulation operation to process the text block.
3. Have a look at cursors to extract data from particular columns. The following lesson looks into cursors in more detail: http://lessons.runrev.com/s/lessons/m/4071/l/16591

Kind Regards,

Hanson

grand lorie

simple question >>

when copy the code from here (or any other we site) and paste it into the livecode script window, it paste it as one long line with strange characters like ?
how to fix that?
cheers

Hanson Schmidt-Cornelius

Hi Grand,

I don't know what operating system you are using, but you may want to try copying the code into a text editor and then copy and paste it from there into the LiveCode Script Editor. This should work with TextEdit on Mac. Other operating systems also have text editors that should help you.

Kind Regards,

Hanson

Dave Mckay

How can I check if the connection ID is still valid? MySQL automatically disconnects me after a certain interval of no access - how can I check if I need to reconnect without throwing an error?

Cheers

Elanor Buchanan

Hi Dave

You can use the revOpenDatabases() function to return a list of the open database connections. If your connect id is in the list the connection is still open, if not you need to reconnect.

I hope that helps.

Kind regards

Elanor

Chris Fourie

Hi

Is it possible to connect to a SQL database and not a MySql database. I have a POS program running on SQL and what i would like to do is design an APP that can give me the stores current sales, or sales history and stock on hand etc.

Many Thanks

Chris

Please bear in mind i have very little experience in development and is training myself through the internet.

Many Thanks

Chris

Elanor Buchanan

Hi Chris

On Desktop LiveCode can connect to MySQL, SQLite, PostgreSQL, Valentina, Oracle and ODBC databases. If your database is one of these types you can connect to it from LiveCode.

You can follow the lesson above and just change the information you pass to the revOpenDatabase function. Have a look at the entry for revOpenDatabase in the LiveCode Dictionary to see some examples of connecting to different database types.

I hope that helps.

Kind regards

Elanor

phaza

How do you create an sql statement that has a variable that comes from a user.example i would have a textbox where a user will enter a name than retrieve information based on that name.using mysql

Elanor Buchanan

Hi Phaza

You can get the contents of a field, place them in a variable and then use the variable as explained in the lesson, e.g.

put field "name" into tFirstName
...

I hope that helps.

Elanor

oscar

Although I can connect to the SQL database in the example, I cannot connect to my databases (tried on 2 different websites). Is there anything that have to be set up on the website for this to work?

Elanor Buchanan

Hi Oscar

You will need to ensure that your database is configured to allow connections.

You might also find this lesson on accessing web service from LiveCode useful.

http://lessons.runrev.com/m/4070/l/40932-accessing-web-services-using-livecode

I hope that helps.

Kind regards

Elanor

Oscar

I figured it out when I created a new database. I can access it now. Thanks

Tracy

The data in my database is Chinese. But when I load in into field. It becomes to "??? ?????". I have already checked that the setting in MySQL is utf8_unicode_ci. So it shows correct in MySQL.
Should I made some command in my EditScript to avoid this problem?

Elanor Buchanan

Hi Tracy

What version of LiveCode are you using? Unicode support was updated in version 7 so if you haven't I suggest updating your version.

If you are on version 7 it might just depend on how you are putting the data into the field, could you post a snippet of the code you are using here?

Kind regards

Elanor

Tracy

Hi, Elanor

Thank you for your reply :)
My Livecode is version 7.
I have already succeed in grabbing Chinese data from my DB.
by using
====================================
-put "SELECT menuName FROM " & tTableName & " WHERE menuType ='"& tType & "' AND menuStyle='"&tStyle&"' AND menuStar='"&tStar&"'" into tSQL
-put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData

-put urldecode (uniencode(tData,"utf8")) into toBeDisplayed

-set the unicodetext of field "DDD" to toBeDisplayed
====================================
But I meet another problem that when the value of my selection command (tType,tStyle,tStar)is Chinese. It can't be used to get correct data from my database. I have already tried to uniencode(tType,"utf8"), but the result is wrong.

The value of tType is from
-put the label of button "TYPE" into tType

The value in option menu is Chinese.

Elanor Buchanan

Hi Tracy

I think you might be doing a little too much work, Unicode support was made more transparent and simpler in LiveCode 7 so some of the examples you might have found don't quite apply.

I think you might just need to do

put "SELECT menuName FROM " & tTableName & " WHERE menuType ='"& tType & "' AND menuStyle='"&tStyle&"' AND menuStar='"&tStar&"'" into tSQL
put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData
put textDecode(tData,"utf8") into field "DDD"

I think that using textDecode/textEncode for the text from menu might help too.

I hope that helps.

Kind regards

Elanor

Tracy

I successfully connect to MySQL database and get correct data and everything goes well while testing in my laptop (Windows).
But when I put my app into my android phone. It can't connect to the remote database.
Does it need any special command to connect while using in mobile.
Here is my connection code
global gConnectionID
-- set up the connection parameters - edit these to suit your database
put "163.25.101.2" into tDatabaseAddress
put "B9829015" into tDatabaseName
put "XXX" into tDatabaseUser
put "XXX" into tDatabasePassword

-- connect to the database

put revOpenDatabase("mysql", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult

Elanor Buchanan

Hi Tracy

You need to inlclude the MySQL External when your build your app. In the Standalone Application Settings go to the Android pane and make sure the MySQL box is ticked in the Externals section.

I hope that helps.

Elanor

Nil rastogi

Hi, actually I am trying to work on the online voting system. And for that I want to connect figer print scanner to my database server. Can you please help me with that. I have a data base of all the fingerprint​ now for varification I want to connect a biometric so that we can verify the person. Please help me with that.

Elanor Buchanan

Hi Nils, its hard to help much as this will depend on the hardware you are using. You may be able to connect the scanner and get the scan data back then compare with your database, this could be potentially be done using LiveCode and LiveCode Builder.

The only built in support we have is for fingerprint scanning on iOS devices, and this can only tell you in the scan was successful matched to the fingerprint stored on the device.

I would recommend asking on our forums

http://forums.livecode.com/

We have a lot of experienced and knowledgeable users there who may have tackled similar projects and can advise.

Kind regards

Elanor

Add your comment

E-Mail me when someone replies to this comment