Guide to connect to an online MySQL database

[bule]

...all the things I tried, and the solution was so easy and obvious...

This is a guide which will (try to) explain how to connect to, and manipulate, an online MySQL database from your APMS application.

Here's what you need to do it yourself:

• APMS 6 (or 5 Pro)
• lua50.dll (get it here) (not lua 5.1, but 5.0)
• lua mysql binaries (get them here) (download only binares you need; tested with mysql5)

Place the following files from the downloaded stuff in your project's AutoPlay\Scripts folder:

• luasql\mysql.dll (This is a subfolder luasql)
• compat-5.1.lua
• libmySQL.dll
• lua50.dll

Put the following code in your Global functions:

-- Load compat for Lua 5.1 package proposal compatibility
require ("compat-5.1.lua");
package.cpath = _SourceFolder.."\\AutoPlay\\Scripts\\?.dll;"
-- Set the working folder before loading the library
-- so that mySQL can find all appropriate DLLs
local strOldWorkingFolder = Folder.GetCurrent();
Folder.SetCurrent(_SourceFolder.."\\AutoPlay\\Scripts");
require"luasql.mysql"
Folder.SetCurrent(strOldWorkingFolder);

Use this code to initiate a connection with the server and the database:
(add your server details where appropiate)

env = assert (luasql.mysql())
con = assert (env:connect("database_name","username","password","server_address","port"));

Use this code to fetch data from, in this example, table test to a ListBox1 (our table has two columns: id and ime):

ListBox.DeleteItem("ListBox1", -1);	
cur = assert (con:execute"SELECT id, ime FROM test")
row = cur:fetch ({}, "a")
while row do
  ListBox.AddItem("ListBox1", row.ime.."", row.id.."");
  row = cur:fetch (row, "a")
end
cur:close();

This "a" in the code above means to return alphanumerically indexed table.

The same way I used SELECT in the upper example, you can use INSERT, UPDATE and all the other SQL commands supported by MySQL database. Additional info on usage and syntax can be found here: http://luaforge.net/projects/luasql

I would like to send a big thanks for helping me with this to:
- azmanar (pointing me to luaSQL)
- Brett (his guide about LuaSockets, and other things he coded for us)
- LuaSQL developers (obvious for what)
- rest of Lua and APMS commuity (for being so cool)

[bule]

Please note that your MySQL server must be set to allow connections to the database from the computers with the range of IP addresses your application will be running on, in order for your application to sucessfully connect.

[azmanar]

Bule,

CONGRATS !!!!!!!!!!!!!!!

[bule]

This is really some hot stuff... For example, we can now set up a central server with MySQL database and have multiple instances of our APMS application on different computers and all of them can connect to and work with the same database!

By the way, default port for connection to a MySQL server is 3306, but you probably already know that...

[Corey]

Superb!

[Brett]

Good job, Bule. It is a rewarding feeling to get all of those pieces to work together, isn't it? I love that kind of stuff. It warms my heart to see AMS60 users go "outside the box" to create these kinds of solutions. The best part of this is that you did all of that with no system dependencies on the clients and I would guess around 2-3 MB of files. Try doing that with .NET! I am glad to see the potential power of Lua and the great Lua community tapped like this.

[bule]

It is a rewarding feeling to get all of those pieces to work together, isn't it?

I sure is

However, there is some work beyond my capabilities (for now) that could be done with this to make things more smooth; for example, if a target server is offline or unrechable, the luasql returns an error and script processing (lines we wrote in APMS after the env:connect command) stops immediately. This represents a problem, because we can not take countermeasures when something like this occurs, to inform user the way we want, or change variables or object states, or whatever we see fit. I guess that for the next APMS incarnation, IndigoRose development could use the sources of LuaSQL libraries and incorporeate them into APMS, so that they return errors like all the other commands in APMS do (and we could then get them with Application.GetLastError(); command). This just my opinion, not a request or anything. I would just like to hear what does heads in IR think about this proposal

[Intrigued]

bule, nice!

[craigedmonds]

Anyone have a SQL Server 2000 version of this?

[bule]

Anyone have a SQL Server 2000 version of this?

I doubt... for now, I think that only MySQL and PostgreSQL drivers of LuaSQL support remote connection.


By the way, I just realised that you don't have to use assert command in the upper example! You can, for example, issue a connection with this:

env = luasql.mysql();
con = env:connect("database_name","username","password","server_address","port");

This way, if something goes bad, you can check the con variable whether it is nil, and if, do something in that case! This is a great solution for the issue I commented earlier here.

[bule]

This way, if something goes bad, you can check the con variable whether it is nil, and if, do something in that case! This is a great solution for the issue I commented earlier here.

Not only that, but check this out:

con,err = env:connect("test_database","test","test","my_server","3306");

That's right! Two returning variables! If there is no error, err variable will be nil, and if there is, err will contain an error message string!!! This rules, kicks and ownes!

P.S: Actually, con is not a variable but an object...but they are all references afterall...

[bule]

However, there is some work beyond my capabilities (for now) that could be done with this to make things more smooth; for example, if a target server is offline or unrechable, the luasql returns an error and script processing (lines we wrote in APMS after the env:connect command) stops immediately.

...as I said the key words in the quote above.... (for now)... well actually, for then, cause I found a solution just a few seconds ago!

Is there a way I could edit the upper guide to remove assert commands and add an additional returning err variable? Moderators?

[azmanar]

...as I said the key words in the quote above.... (for now)... well actually, for then, cause I found a solution just a few seconds ago!

Is there a way I could edit the upper guide to remove assert commands and add an additional returning err variable? Moderators?

Since it is Lua environment, then why not apply Lua here as well;

con,err = env:connect("test_database","test","test","my_serv er","3306");

if err ~= nil then
Label.SetText ("Label_Error", err );
end

[bule]

I think you misunderstood me azmanar; I know how to change the code;
I want to change the guide I wrote, but I can not edit my post!

[azmanar]

I think you misunderstood me azmanar; I know how to change the code;
I want to change the guide I wrote, but I can not edit my post!

Ahhh.....sorry. Misunderstood.

Put another guide in here...within this thread.