[Home] [Downloads] [Search] [Help/forum]

Gammon Forum

See www.mushclient.com/spam for dealing with forum spam. Please read the MUSHclient FAQ!

[Folder]  Entire forum
-> [Folder]  MUSHclient
. -> [Folder]  Tips and tricks
. . -> [Subject]  Accessing a MySQL database from MUSHclient
Home  |  Users  |  Search  |  FAQ
Username:
Register forum user name
Password:
Forgotten password?

Accessing a MySQL database from MUSHclient

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


Pages: 1 2  

Posted by Nick Gammon   Australia  (21,322 posts)  [Biography] 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
[Go to top] top

Posted by Yergo   Poland  (20 posts)  [Biography] bio
Date Reply #1 on Sun 24 Dec 2006 08:52 PM (UTC)
Message
What should I do, to create a mysql connection when php scripting is used? In standard install using mysql_connect() function causes errors...

I'm doing my best with English... Sorry ;)
Scripting: php (currently rewriting things to Lua)
[Go to top] top

Posted by Nick Gammon   Australia  (21,322 posts)  [Biography] bio   Forum Administrator
Date Reply #2 on Tue 26 Dec 2006 01:48 AM (UTC)
Message
You haven't said what the errors are, nor the syntax you used for the mysql_connect() function, so it is pretty hard to help. Try reading this post:

http://www.gammon.com.au/forum/bbshowpost.php?id=6150

- Nick Gammon

www.gammon.com.au, www.mushclient.com
[Go to top] top

Posted by Yergo   Poland  (20 posts)  [Biography] bio
Date Reply #3 on Tue 26 Dec 2006 02:10 PM (UTC)
Message
My script tells: "Call to undefined function mysql_connect()". I'm working with php5activescript.dll file only, as it was told to install somewere here.

I'm doing my best with English... Sorry ;)
Scripting: php (currently rewriting things to Lua)
[Go to top] top

Posted by Nick Gammon   Australia  (21,322 posts)  [Biography] bio   Forum Administrator
Date Reply #4 on Tue 26 Dec 2006 06:01 PM (UTC)
Message
Did you read the thread I posted just above? It seems that happened to me too a while back. Maybe the DLL has not been built with MySQL support in it.

I got it to work using odbc_connect as described in the thread.

- Nick Gammon

www.gammon.com.au, www.mushclient.com
[Go to top] top

Posted by Nick Gammon   Australia  (21,322 posts)  [Biography] bio   Forum Administrator
Date Reply #5 on Mon 05 Feb 2007 09:24 PM (UTC)
Message
Under Lua 5.1, I would change the first couple of lines:


-- load the MySQL dll
loadlib ("mysql.dll", "luaopen_luasqlmysql") ()


to:


-- load the MySQL dll
assert (package.loadlib ("mysql.dll", "luaopen_luasqlmysql")) ()


- Nick Gammon

www.gammon.com.au, www.mushclient.com
[Go to top] top

Posted by Nick Gammon   Australia  (21,322 posts)  [Biography] bio   Forum Administrator
Date Reply #6 on Mon 05 Feb 2007 09:28 PM (UTC)

Amended on Mon 05 Feb 2007 09:31 PM (UTC) by Nick Gammon

Message
I also noticed when testing this recently, that the mysql.dll has a dependency on another DLL: libmySQL.dll

In case anyone wants to use mySQL with MUSHclient, using Lua, I have placed a copy of that DLL, zipped, in this location:

http://www.gammon.com.au/files/mushclient/lua5.1_extras/libmySQL.zip

The MD5 sum for this file is:


43b953fe2f93cb98d39aa3ae63776f5d  libmySQL.zip

- Nick Gammon

www.gammon.com.au, www.mushclient.com
[Go to top] top

Posted by Holliday   USA  (3 posts)  [Biography] bio
Date Reply #7 on Thu 21 Jan 2010 03:40 AM (UTC)
Message
Not to bring up an old post but I was attempting to convert a zmud script to MUSH

I have the script doing pretty much everything that it needs to do, but I was having issues outputting data to another (Window/World) with scrollbar

The only other question would be do you have a recommendation on displaying data in a "nicer format"
=================
Mob: <name_col>
Level: <level_col>
=================

Just any direction on the best way to handle this would be great. So far here is the code minus the alias mf <name>


<script>
<![CDATA[
function mfind(strig, sline, wildcards )
assert (package.loadlib ("mysql.dll", "luaopen_luasqlmysql")) ()
-- create environment object
env = assert (luasql.mysql())

-- connect to data source
con = assert (env:connect ("db", "usr", "pass", "server"))

-- retrieve a cursor
cur = assert (con:execute ("SELECT * from mobfinder WHERE name Like ".."\'%"..wildcards[1].."%\'" ))

-- print all rows, the rows will be indexed by field names
row = cur:fetch ({})

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()
end
]]>
</script>
[Go to top] top

Posted by Nick Gammon   Australia  (21,322 posts)  [Biography] bio   Forum Administrator
Date Reply #8 on Thu 21 Jan 2010 09:22 AM (UTC)
Message
Depending on your row names on the database, you would change:


table.foreach (row, print)  


to be something like:


print ("Mob:", row.name)
print ("Level:", row.level)


The "row" variable is a table which has every column in it, indexed by the name of the column.

- Nick Gammon

www.gammon.com.au, www.mushclient.com
[Go to top] top

Posted by Rooklion   (9 posts)  [Biography] bio
Date Reply #9 on Tue 26 Apr 2011 07:11 PM (UTC)
Message
I've tried everything, including downloading the latest LuaSQL version. Oddly enough, NONE of the downloads from LuaForge have dlls that actually function.. only the dlls you have linked as extra files will work.

Anyway, I have a MySQL database created on www.freesql.org. I can connect to this database using the MySQL command line tool. However, when using your example here, I get "Access denied for user."

[string "Plugin"]:942: LuaSQL: Error connecting to database. MySQL: Access denied for user 'aardxaxz'@'184.17.107.100' (using password: YES)
[Go to top] top

Posted by Nick Gammon   Australia  (21,322 posts)  [Biography] bio   Forum Administrator
Date Reply #10 on Tue 26 Apr 2011 10:54 PM (UTC)
Message
So you changed this line:


con = assert (env:connect ("databasename", "username", "password", "servername"))


to be:


con = assert (env:connect ("YOURDATABASENAME", "aardxaxz", "YOURPASSWORD", "freesql.org"))


Is that correct?

- Nick Gammon

www.gammon.com.au, www.mushclient.com
[Go to top] top

Posted by Rooklion   (9 posts)  [Biography] bio
Date Reply #11 on Tue 26 Apr 2011 11:11 PM (UTC)
Message
Actually, shortly before you replied to me I found out it was a problem with the site. Seems my code wasn't a problem. And yes, those were all changed.
[Go to top] top

Posted by Tharius   (29 posts)  [Biography] bio
Date Reply #12 on Thu 18 Jul 2013 09:21 PM (UTC)
Message
This afternoon I attempted to access my database via mush and was met with a problem I don't understand.

Using the original script with the updated assert, mush 4.73 (with Lua 5.1 I presume) and the Lua archive suggested in the earlier posts (rather than trying to figure out what LuaRocks is and how to get it to work in Windows).

I put the 2 dll files in my mushclient.exe directory and edited a new script via the dialogues off the script option of <alt+enter>

Immediate execution
[string "Script file"]:3: The specified module could not be found.

stack traceback:
[C]: in function 'assert'
[string "Script file"]:3: in main chunk
Error context in script:
1 : -- load the MySQL dll
2 : --loadlib ("mysql.dll", "luaopen_luasqlmysql") ()
3*: assert (package.loadlib ("mysql.dll", "luaopen_luasqlmysql")) ()
4 :
5 : -- create environment object
6 : env = assert (luasql.mysql())
7 :


I attempted to put the full path but this also failed. I attempted to use Windows 7 compatibility under Windows 8 when loading Mush but this made no difference. I presume it's something simple I'm doing incorrectly.

Any help is appreciated. If there is a newer thread on this subject I apologize, I didn't see it.
[Go to top] top

Posted by Tharius   (29 posts)  [Biography] bio
Date Reply #13 on Fri 19 Jul 2013 02:10 PM (UTC)
Message
As an aside, I verified that libmySQL.dll was being found by MUSH/Lua by inserting:

assert (package.loadlib ("libmySQL.dll", "luaopen_bogusfunction")) ()

Which of course returns a procedure not found error.

I find it odd that this would be found but the mysql.dll a few files down in the folder isn't.
[Go to top] top

Posted by Nick Gammon   Australia  (21,322 posts)  [Biography] bio   Forum Administrator
Date Reply #14 on Sat 20 Jul 2013 04:18 AM (UTC)
Message
The dependencies I have for mysql.dll (which works for me) are:



I suppose you have lua5.1.dll and not lua51.dll?

- Nick Gammon

www.gammon.com.au, www.mushclient.com
[Go to top] top

The dates and times for posts above are shown in Universal Co-ordinated Time (UTC).

To show them in your local time you can join the forum, and then set the 'time correction' field in your profile to the number of hours difference between your location and UTC time.


39,263 views.

This is page 1, subject is 2 pages long: 1 2  [Next page]

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

Go to topic:           Search the forum


[Go to top] top

Quick links: MUSHclient. MUSHclient help. Forum shortcuts. Posting templates. Lua modules. Lua documentation.

Information and images on this site are licensed under the Creative Commons Attribution 3.0 Australia License unless stated otherwise.

[Home]


Written by Nick Gammon - 5K   profile for Nick Gammon on Stack Exchange, a network of free, community-driven Q&A sites   Marriage equality

Comments to: Gammon Software support
[RH click to get RSS URL] Forum RSS feed ( https://gammon.com.au/rss/forum.xml )

[Best viewed with any browser - 2K]    [Hosted at FutureQuest]