Gammon Forum : MUSHclient : Tips and tricks : Accessing a MySQL database from MUSHclient

Entire forum ➜ MUSHclient ➜ Tips and tricks ➜ Accessing a MySQL database from MUSHclient

Accessing a MySQL database from MUSHclient

It is now over 60 days since the last post. This thread is closed. Refresh page

Pages: 1 2

Posted by Nick Gammon Australia (23,120 posts) Bio Forum Administrator

Date

Sat 15 Oct 2005 03:51 AM (UTC)

Amended on Sat 15 Oct 2005 03:53 AM (UTC) by Nick Gammon

Message

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:



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)

- Nick Gammon

www.gammon.com.au, www.mushclient.com