Connect to a Remote MySQL Database Using Livecode


Livecode is a simple yet powerful development platform which allows you to easily build awesome interactive programs with minimal programming knowledge or experience – even the most novice user can start building the basic “hello world” and “click here to see something happen” applications on the same day they pick up the platform… But what happens when you want to extend the functionality of your project to include accessing / sharing / manipulating data from an outside source? Unless you want everything to happen locally on the user’s computer or smart phone device, using a remotely hosted MySQL database is simply the easiest way to manage this process and to allow large numbers of users to access the same data records with minimal fuss (of course, the data can be easily parsed to display only the information associated with the current user, if required – useful for login accounts, personal records, etc)!

What is a MySQL database?

MySQL databases are the life blood of most interactive application experiences – they allow you to deploy the software to multiple users, while maintaining a centralized record of all the required data to run the program. You can use a database to store user information, photo information, action history, sales records, event information, etc – changing the data in the remote database instantly allows all users of the program to view the updated information. Awesome, right?

Most MySQL databases are hosted on a third party server through companies such as GoDaddy, HostGator, etc – if you want to be able to access the database from outside the server, make sure your hosting provider allows for “Remote Connections” on the MySQL databases, or else you will only be able to access the data from programs running directly on your server (which would not include desktop programs, smart phone apps, etc).  I spent many years with GoDaddy and I was happy in the earlier years, but eventually (for a variety of reasons) I had to make the switch to HostGator, which happens to be one of the few hosting providers that allow Remote Connections to your database server.

For the purpose of this article, the assumption has been made that you already have a valid hosting provider, and you already took the required steps to set up a new database for us to access.

How do I make a connection?

This is all good to know, but how do we actually go about connecting to our database from within a Livecode stack?  Fortunately for us, the process is easy!  The following code will allow you to connect and disconnect from any valid database (once it has been set up) – start by copying the following code and pasting it into the main Stack Script of your application…..  This will allow you to call these handlers any time you want to connect or disconnect from the database.

NOTE: You can successfully connect to a database and access the information you seek, even if you choose not to manually close the connection immediately after you are done with it.  However, most shared server plans only accommodate a particular number of simultaneous connections, so failing to close each connection when you are done could easily cause your programs to start spitting back errors whenever a new attempt it made to connect.  Of course, most servers also forcefully close the connections after a period of time, so you will begin to have issues “some” of the time and maybe not “all” the time…. However, the best practice when accessing a database is to immediately close the connection when you are done, which is why there are two different connection handlers presented.

In the following code, we use a variable called gConnectionID to track the current connection – this should be a GLOBAL variable (so it can be accessed from any card in your stack for the purpose of connecting / disconnecting from the DB), so make sure you place the line “global gConnectionID” at the top of the stack script, before any handlers are written.

CONNECT TO DB HANDLER

Make sure you edit the DB particulars to fit your specific setup – you need to use your own DB Address (I usually use the IP address of the server, which is easily obtainable by logging in to your account on your hosting provider’s website), DB Name, DB Username and DB Password.  If you try to run this script “as is”, it will not work since it is not pointing to any real database at the moment!

on connectToDB

put "XXX.XXX.XXX.XXX:3306" into gDatabaseAddress
put "dbname" into gDatabaseName
put "dbusername" into gDatabaseUser
put "dbpassword" into gDatabasePassword

--put URLEncode(gDatabasePassword) into tDatabasePassword

put revOpenDatabase("mysql", gDatabaseAddress, gDatabaseName, gDatabaseUser, gDatabasePassword) into tConxn

--answer "tConxn:" && tConxn

# it's a good idea, but not required, to check for a successful connection here
if tConxn is a number then
put tConxn into gConnectionID
--answer "DB Connection Success!"
return empty
else
put "Unable to connect to database '" & gDatabaseName & "': " & tConxn into tErrMsg
--answer error tErrMsg as sheet
answer tErrMsg
--put tErrMsg & cr before fld "fldMessage"
put empty into gConnectionID
return tErrMsg
exit to top
end if

end connectToDB

 

DISCONNECT FROM DB HANDLER

When you have accessed the information you need, you should always close the database connection right away – simply call this handler (using the string “closeDB gConnectionID”).

on closeDB pConnectionID
revCloseDatabase pConnectionID

# this ensures that you'll never have a left-over connection number in the global var
if pConnectionID = gConnectionID then
put empty into gConnectionID
end if
end closeDB

 

In terms of connecting to a MySQL database with Livecode and subsequently disconnecting from said database, that’s all there is to it!  I tend to keep these scripts handy and I simply paste them into any new project when I am ready to start interacting with the database.  After you connect (and prior to disconnecting) to a database, you will want to then retrieve information, create or delete data records, etc…  Once you have successfully grabbed the required data, you are free to disconnect as the information can be stored in a global string variable within the app (or you can quickly parse the data into a multi-dimensional array, which sounds intimidating but its really easy to do and makes subsequent data manipulation much easier – I will cover this process in the next article).

If you have any problems or if you know a better way to get this done, please tell me about it in the Comments section, below!  I will do my best to reply to all questions in a timely fashion, but keep in mind that due to a number of circumstances, the response time may vary.  I wish you the best of luck in your future coding endeavors – if you are using Livecode, then you have chosen wisely and will soon be on your way to some great programs, apps and more!  The sky, and your imagination, are the only limits.

COMING SOON: How to view, edit, add and delete information from the database, using Livecode.