Connecting to a MySQL database

How to connect to a MySQL database via scripts only i.e. without using the Query Builder.

MySQLExample.livecode

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

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 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!).

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

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

55 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

NURUL AMIRA

i have follow your instruction to connect to the mySQL. it is work but if i want to add new data through the field box into the database, the record was should have in the database is was empty. what should i do? my prob is that only. i want to know the answer as soon as possible.. it is urgent thing

Elanor Buchanan

Hi NURUL AMIRA, I have tested the code and it seems to work correctly. I have added a sample stack which also includes a "Sort" button so you can order the records and see if your record has been added.

Could you try the sample stack and see if that works for you? You could change the name in the code of the "Add" button to make it clearer if your record has been added. If it doesn't work for you please let us know and we will look into it.

Elanor

NURUL AMIRA

Do you have any full coding to add data through textfield to database mysql ..

Elanor Buchanan

Hi,

You would use the same method as in the the lesson but instead of hardcoding the values you would use the values in the fields. So if you had 2 fields named firstName and lastName you would change the code to

put "Table1" into tTableName
put "firstName, lastName, birthDate" into tFields
put field "firstName" into tFirstName
put field "lastName" into tLastName
put the short date into tBirthDate -- this is nonsensical but gives some variation to the data

-- construct the SQL
put "INSERT INTO " & tTableName & " (" & tFields & ") VALUES (:1, :2, :3)" into tSQL

This would insert the contents of the firstName and lastName fields into the database.

I hope that helps.

Elanor

RRI

Is the sample database still online? I cannot connect to it.

Matthias Rebbe

Hi Rainer,
maybe the database is not available anymore. Livecode Ltd. has moved their servers some time ago. Maybe the Database was not moved. The best thing would be to setup a test db on your own MySQL server.

Heather Laine

We've checked here and the database is still online and we can connect to it. What exact error are you getting trying to connect?

RRI

Thanks for the feedback. I tried again right now and it is working without changing the code. Yesterday evening (German time) it wasn't. Thanks for the quick response.

slenderline.com

714/5000
I manage a site in a classic way under PHP MySQL. Everything is encoded in UTF 8.
I created stacks under Livecode which allow me to manage the content of my MySQL tables more easily.
The problem is that of encoding.
Text updated in MySql from Livecode is not correctly read by PHP / HTML and the display on the screen of certain accented characters is not correct. The character "é" gives "Ž" in the database and is read as such by PHP in HTML.
I tried to encode by textEncode (string, "UTF-8) before updating my data in MySQL. In this case, the character" é "appears as" é "in the database.
How to solve the problem?
Thanks in advance.

Elanor Buchanan

Hi slenderline

Using textEncode should be the correct solution. I did a quick test with a local SQLite database and that worked. The code I used was

put "INSERT into people(name) VALUES ('" & textEncode(field "name","UTF8") & "')" into tQuery
revExecuteSQL sDatabaseID, tQuery

Can you paste the relevant part of your code here for us?

Kind regards

Elanor

slenderline

Hi,
I got around the problem by writing a piece of code that performs the coding so that my third-party applications read the data entered in the MySQL tables correctly.
This portion is simple and works well. I will check if the code you offer does this too. It would be lighter and certainly faster than my patch.
Thank you anyway.
slenderline

on codeChaineToMySQL pChaine
-- mémorisation de la chaîne à coder
put pchaine into tchaine
-- initialisation de la chaîne codée
put empty into tchainenew
-- on parcourt la chaîne caractère par caractère
repeat with x=1 to the length of tChaine
-- caractères accentués à transformer
-- é è ê à î â ç ü ï ô û ù ' °
-- È Ë Í ‡ Ó ‚ Á ¸ Ô Ù ˚ ˘ ' ∞
switch char x of tchaine
case "é"
put "È" after tchainenew
break
case "è"
put "Ë" after tchainenew
break
case "ê"
put "Í" after tchainenew
break
case "à"
put "‡" after tchainenew
break
case "î"
put "Ó" after tchainenew
break
case "â"
put "‚" after tchainenew
break
case "ç"
put "Á" after tchainenew
break
case "ü"
put "¸" after tchainenew
break
case "ï"
put "Ô" after tchainenew
break
case "ô"
put "Ù" after tchainenew
break
case "û"
put "˚" after tchainenew
break
case "ù"
put "˘" after tchainenew
break
case "'"
put "'" after tchainenew
break
case "°"
put "∞" after tchainenew
break
default
-- gestion normale des caractères
put char x of tchaine after tchainenew
end switch
end repeat
-- on renvoie la chaine décodée
return tchainenew
end codeChaineToMySQL

KRL29

Hi,
If I developp an application with a MySql database, how can I do after if I want that the user download the database to use the app locally ?
What have I to do to send the package on a market place ?

Elanor Buchanan

Hi

You would not be able to just download a MySQL database to use locally. You could write a tool to download the information from the MySQL database and store it locally, perhaps using an SQLite database. Alternatively you could include the SQLite version of your database with the application. It really depends on what functionality you need.

What you need to do to send the package to a market place will depend on the platform you are developing for and the market places you want to use.

We have lessons on preparing mobile and desktop apps for distribution by signing etc but what you need to do will vary by platform, store, and what your app does.

Kind regards

Elanor

Michael Watkins

I use MySQL community locally with Livecode all the time and its easy as pie. Naturally a person would have to have it installed and all you need to do is supply the setup SQL file they can run to setup user, password, database, permissions, tables, triggers, functions and procedures.

Krl29

Thx Elanor.
I will wait for this lesson ;)
I don't know sqlLite. Only mySql.
I will look at the differences.

Thx Michael. Of course, but it wasn't what I wanted to know.
It was for example for the games we can download. I thought that the updates were to be able to play locally, then they included database in the download. It's out of the question to ask a user to install himself the database and to explain to him how to connect.

Michael Watkins

Aaaahhhh. i see. Yeah i think sqlLite would be needed since it would be wrapped up inside of your package. It is highly limited and i remember hitting quite a few things it couldnt do but for a game you might need a simple database.

idinio

this tutorial doesn't work I use cpanel share hosting and always get an error "Access denied for user ...... using password; Yes. how do i use mysql?

Heather Laine

Idinio, maybe this tutorial will be helpful to you:

https://lessons.livecode.com/m/4071/l/11725-how-do-i-create-and-set-up-a-database

Matthias Rebbe

Idinio,

the lesson Heather linked to in the previous post includes steps for setting up Remote MySQL®, but that doesn't work anymore on many hosting account with cPanel, including LivecodeHosting and HostM due to security reasons/updates.

I've replied already to your post in the forum, but better twice than never. ;) So here it is again.

There could be several reasons why you cannot connect to your MySQL/MariaSQL database on a cPanel shared hosting.

1. cPanel blocks your IP, because it is not allowed to remotely access the DB
cPanel allows/allowed to setup remote ip addresses / hostnames which then were allowed to access the MySQL/MariaSQL database remotely. You can find this in cPanel under Databases -> Remote MySQL®. In some cases you had/have also to inform your provider that you've added something in that section, so the provider could make some additional adjustments to the firewall. This might work on older cPanel systems.

2. Even if you've setup Remote MySQL® correctly, it might be that cPanel is not allowing remote access anymore due to some security updates. That's why i wrote also "allowed to setup". Many hosting providers still show that Remote MySQL® icon in cPanel, although it is not working anymore, for example LivcodeHosting or HostM.

Btw, HostM provides a nice tutorial including Livecode source how to access the DB remotely from your desktop app using some livecode server script.
https://www.hostm.com/tutorials/mariadb/remote-access
This way your desktop app communicates with the Livecode Server script on your webserver and the Livecode Server script does the complete DB stuff.

In case you still need to directly connect from your desktop app to your DB you could use a SSL tunnel.
https://linuxize.com/post/mysql-ssh-tunnel/
To get that working you'll need SSH access to your hosting account. HostM and LivecodeHosting will enable this for you, if you ask for it.

Add your comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.