This note describes accessing a MySQL database from MUSHclient, using Lua scripting, and the LuaSQL interface. The advantage of doing this is it does not involve COM objects, which can be hard to get right, and would also be cross-platform compatible.
The simple example below was just run in the Immediate scripting window.
First, I obtained the LuaSQL MySQL interface DLLs from:
The specific file I downloaded was:
This consists of two files: mysql.dll and libmySQL.dll, which I placed in the same directory as the MUSHclient executable. Also in that directory is lua50.dll which mysql.dll requires. This is now the default lua DLL which ships with MUSHclient (version 3.67 onwards).
Now, to the code itself:
The first line loads the MySQL DLL, and adds it to our script space.
The next couple of lines retrieve a MySQL "environment" object and use that to connect to the database, giving a "connection" object called con.
You would replace the names given in the connect statement to relect your database name, username, password, and servername, where appropriate.
Then we can use con::execute to execute arbitrary SQL statements. In this case we drop an existing table and replace it with a new one.
Then we loop around adding a few records, and finally do a SELECT to retrieve all data back again, and display it.
This is a pretty simple example, but hopefully will give you the general idea needed to add MySQL handling to your MUSHclient scripts.
Advantages would be that you can store huge amounts of data without bloating the MUSHclient script space, and the data persists between connections, even if MUSHclient crashes (something that doesn't happen much <grin> ).
Running this code, I see this on my output window:
Then if I switch to the PC with MySQL running (a Unix PC in my case) and use the mysql client, I can confirm that the data was successfully added:
The simple example below was just run in the Immediate scripting window.
First, I obtained the LuaSQL MySQL interface DLLs from:
http://luaforge.net/frs/?group_id=12&release_id=271
The specific file I downloaded was:
luasql-2.0.1-mysql50-win32.zip
This consists of two files: mysql.dll and libmySQL.dll, which I placed in the same directory as the MUSHclient executable. Also in that directory is lua50.dll which mysql.dll requires. This is now the default lua DLL which ships with MUSHclient (version 3.67 onwards).
Now, to the code itself:
-- load the MySQL dll
loadlib ("mysql.dll", "luaopen_luasqlmysql") ()
-- create environment object
env = assert (luasql.mysql())
-- connect to data source
con = assert (env:connect ("databasename", "username", "password", "servername"))
-- empty our table
res = con:execute"DROP TABLE players"
res = assert (con:execute[[
CREATE TABLE players(
name varchar(50),
class varchar(50)
)
]])
-- add a few elements
list = {
{ name="Nick Gammon", class="mage", },
{ name="David Haley", class="warrior", },
{ name="Shadowfyr", class="priest", },
}
for i, p in pairs (list) do
res = assert (con:execute(string.format([[
INSERT INTO players
VALUES ('%s', '%s')]], p.name, p.class)
))
end
-- retrieve a cursor
cur = assert (con:execute ("SELECT * from players" ))
-- print all rows, the rows will be indexed by field names
row = cur:fetch ({}, "a")
while row do
print ("\n------ new row ---------\n")
table.foreach (row, print)
-- reusing the table of results
row = cur:fetch (row, "a")
end
-- close everything
cur:close()
con:close()
env:close()
The first line loads the MySQL DLL, and adds it to our script space.
The next couple of lines retrieve a MySQL "environment" object and use that to connect to the database, giving a "connection" object called con.
You would replace the names given in the connect statement to relect your database name, username, password, and servername, where appropriate.
Then we can use con::execute to execute arbitrary SQL statements. In this case we drop an existing table and replace it with a new one.
Then we loop around adding a few records, and finally do a SELECT to retrieve all data back again, and display it.
This is a pretty simple example, but hopefully will give you the general idea needed to add MySQL handling to your MUSHclient scripts.
Advantages would be that you can store huge amounts of data without bloating the MUSHclient script space, and the data persists between connections, even if MUSHclient crashes (something that doesn't happen much <grin> ).
Running this code, I see this on my output window:
------ new row ---------
class mage
name Nick Gammon
------ new row ---------
class warrior
name David Haley
------ new row ---------
class priest
name Shadowfyr
Then if I switch to the PC with MySQL running (a Unix PC in my case) and use the mysql client, I can confirm that the data was successfully added:
mysql> select * from players;
+-------------+---------+
| name | class |
+-------------+---------+
| Nick Gammon | mage |
| David Haley | warrior |
| Shadowfyr | priest |
+-------------+---------+
3 rows in set (0.00 sec)
