Lua, MS Access databases?

Posted by Trevize on Thu 09 Feb 2006 09:59 PM — 27 posts, 138,559 views.

Trevize Thu 09 Feb 2006 09:59 PM #0

Can you somehow create, load and update MS Access databases in LUA? I found a plugin that's included in the mushclient installation that allows this, but it was made in VBscript and since I've programmed in LUA now for about a year changing is not an option.

David Haley USA Thu 09 Feb 2006 10:09 PM #1

You would need Lua code or a Lua C library that provides such functionality. I don't know of one but that doesn't mean it doesn't exist. Try looking around on the Lua users wiki: http://lua-users.org/wiki/

Nick Gammon Australia Forum Administrator Fri 10 Feb 2006 01:53 AM #2

I found this file in my downloads area (of my hard disk):

luasql-2[1].0.1-odbc-win32.zip

I presume that is the ODBC driver for Lua, if you find that on the LuaForge page you should be able to do it.

Nick Gammon Australia Forum Administrator Sat 11 Feb 2006 11:36 PM #3

To amplify on my previous answer ...

I found the file odbc.dll from the LuaSQL downloads area (the file name given above). I put that DLL in the same directory as MUSHclient.

I created a test database using Access, and made a system DSN using the ODBC control panel.

Now, the following test code successfully created a table, put data into it, and read the results back:


-- load the ODBC dll
assert (loadlib ("odbc.dll", "luaopen_luasqlodbc")) ()

-- create environment object
env = assert (luasql.odbc())

-- connect to data source
con = assert (env:connect ("luatest",     -- DSN name
                           "nick",        -- user name
                           "swordfish"))  -- password

-- empty our table
assert (con:execute"DROP TABLE players")

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
  assert (con:execute(string.format([[
    INSERT INTO players
    VALUES ('%s', '%s')]], p.name, p.class)
  ))
end  -- for loop

-- 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  -- while loop

-- close everything
cur:close()
con:close()
env:close()

Nick Gammon Australia Forum Administrator Sat 08 Sep 2007 02:47 AM #4

In Lua 5.1 (ie. in recent versions of MUSHclient), the loadlib function has moved to the package library, so the first couple of lines in the example above should read:


-- load the ODBC dll
assert (package.loadlib ("odbc.dll", "luaopen_luasqlodbc")) ()

Also, the players table won't exist the first time you try this, so you could remove the assert when deleting the old table, ie.


-- empty our table
con:execute "DROP TABLE players"

Nick Gammon Australia Forum Administrator Sat 08 Sep 2007 02:51 AM #5

The DLL you need for Lua 5.1 is here (27 Kb):

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

Katherina Thu 23 Oct 2008 02:09 PM #6

Hi
I got the access database to work with mushclient and I've been searching for two days for the answers to my questions and come up empty handed. What I need to know is how to test to see if a table already exists, and how to find a certain record and either pull information from it or overwrite it.

Basically when I log in it will ask me what profile I want to load. I enter the character name I want to play it should open the database, search it for the matching character name, if it exists it should fill the variables with the information from the database. If not ask for a new record to be created.

Then when I log off I want it to automatically search for the profile name again and update the record with any variables that changed or just enter in all the information it pulled, updating it.

I have the database working except it appends to it adding a new record even if the character name already exists.

Help please?
And Nick you are awesome I've been waiting to be able to use a database for a long time now.

Nick Gammon Australia Forum Administrator Fri 24 Oct 2008 05:32 AM #7

First, why test for a table's existence? Although this can no doubt be done, surely you have a table of players, and you want to see if a row exists (eg. player Gandalf) rather than a table. You wouldn't have a table per player would you? This would seem a strange design.

Assuming you want to test if a row exists, it would look like this:


-- retrieve a cursor
cur = assert (con:execute ("SELECT * from players WHERE name = 'gandalf' " ))

row = cur:fetch ({}, "a")

if row then

  -- that player exists

end -- if

Katherina Fri 24 Oct 2008 02:14 PM #8

Thank you Nick.
I wanted to test if it existed in case it somehow got deleted or if someone else wanted to use my curing system, it would be a new database with their character info.
Though I got around this by just making a create table alias.

I was going to have it check if it exists when they're trying to add a new profile, if it doesn't it would make the table, if it does it would just open it and check to see if what they are adding already exists.

I need to know if the record exists so that I can overwrite and update it, instead of appending to the database.

Katherina Sat 25 Oct 2008 01:16 AM #9

Well... I've been playing with this all day and I can't hardcode in what to search for, I haven't been able to figure out how to make it work with searching on a variable.
I've tried name = GetVariable ("profilename")
I've tried putting the profile name into a temporary variable and using that, still to no avail.

Nick Gammon Australia Forum Administrator Sat 25 Oct 2008 01:36 AM #10

One approach to whether or not the table exists is to simply create it each time. This will fail if it already exists, eg. change:


assert (con:execute[[
  CREATE TABLE players(
    name  varchar(50),
    class varchar(50)
  )
]])

to:


con:execute[[
  CREATE TABLE players(
    name  varchar(50),
    class varchar(50)
  )
]]

By taking out the assert, the table creation will silently fail if it already is there.

As for searching on a variable, can you post the line you are trying? Is it a syntax error you are getting, or does it not find the player you want?

Katherina Sat 25 Oct 2008 01:50 AM #11

I've tried several different ways kind of trying to hack it, because I can't seem to find any documentation other than here.

With this one
cur = assert (con:execute ("SELECT * from players WHERE charname = 'GetVariable ("profilename")' " ))
I get
Compile error
World: Aetolia Undead Infernal System
Immediate execution
[string "Alias: "]:15: ')' expected near 'profilename'

---

local names = GetVariable ("profilename")
Note ("Show me name ", names)

cur = assert (con:execute ("SELECT * from players WHERE charname = names " ))

I get

Show me name nysala
Run-time error
World: Aetolia Undead Infernal System
Immediate execution
[string "Alias: "]:15: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

and if I use that but put single quotes around names it just says that the record isn't found. Which is correct there are no records containing names as a character name.

What I want to be able to do is to find a profile based on a variable, if it's found pull all the data from the table and put it into variables, then when I quit the mud I want it to find the same record and update the information, taking the information and putting it back into the database.

I have several characters who have items that will overlap but the game needs object numbers that are specific to an object. So if I am envenoming a sword, my system needs to know what the sword number is. I have a paladin and anti-paladin both have the same skill but different sword numbers. That is why I want to be able to load the variables from a record in the database when I log in, and when I log off update it. So if I buy a new sword or get a new mount etc.... it will update it with the new numbers so I don't have to keep doing it manually all the time. All I would need to do is update the variable and then it would be stored in the database and loaded when I load that character profile.

Nick Gammon Australia Forum Administrator Sat 25 Oct 2008 03:54 AM #12

OK, what you are mixing here is a script command inside a literal. You need to concatenate things, like this:


cur = assert (con:execute ("SELECT * from players WHERE charname = '" ..
                       GetVariable ("profilename") .. 
                       "' " ))

It mightn't be easy to see, but I have put a single quote around the name you get from GetVariable. So, for example, if GetVariable ("profilename") returned "Nick" then it would read:


cur = assert (con:execute ("SELECT * from players WHERE charname = '" ..
                       "Nick" .. 
                       "' " ))

Now after concatenation it would be:


cur = assert (con:execute ("SELECT * from players WHERE charname = 'Nick' "))

Which should work.

Katherina Sat 25 Oct 2008 04:07 AM #13

I love you

Orogan Thu 06 Nov 2008 11:10 PM #14

If my table would look like this :

list = { name="blah", class="blah", Level=15,}

What should I change in the next part to make it work?


for i, p in pairs (list) do
  assert (con:execute(string.format([[
    INSERT INTO players
    VALUES ('%s', '%s')]], p.name, p.class)
  ))
end  -- for loop

Probably easy, but I just can't seem to make it work.

David Haley USA Thu 06 Nov 2008 11:59 PM #15

It would be helpful if you gave more information about why it's not working, e.g. error messages, symptoms, if it's doing something but not the right thing, doing nothing at all, etc.

Katherina Fri 07 Nov 2008 02:28 AM #16

Agreed, knowing what the error is would help. Though one thing I noticed right off is that you have three fields, so you need to add another %s and p.level the number of variables need to match the number of fields.
Though I think %s stands for string and level is numeric so it 'might' be %d instead but I don't know for certain. I just assigned all my numerics to strings.

So
for i, p in pairs (list) do
assert (con:execute(string.format([[
INSERT INTO players
VALUES ('%s', '%s')]], p.name, p.class)
))
end -- for loop

Should be
for i, p in pairs (list) do
assert (con:execute(string.format([[
INSERT INTO players
VALUES ('%s', '%s', '%s')]], p.name, p.class, p.level)
))
end -- for loop

Nick Gammon Australia Forum Administrator Fri 07 Nov 2008 05:50 AM #17

And, level doesn't need to be quoted if it isn't a string, although I am not sure if it does harm or not.

Orogan Fri 07 Nov 2008 08:12 AM #18

The error I get is :

[string "Plugin"]:286: bad argument #2 to 'format' (string expected, got nil)

I checked the values of p and i and they seem to be oke.

I also tested a table without numbers just strings witch made no differents at all.

David Haley USA Fri 07 Nov 2008 05:05 PM #19

Well, you need to look at p.name and p.class -- apparently somewhere you are getting nil values.

You need to go investigate why that is, but in the meantime, you can change:

VALUES ('%s', '%s')]], p.name, p.class

to

VALUES ('%s', '%s')]], p.name or "NIL", p.class or "NIL"

Having only two values instead of three will be a problem if your DB table expects three columns. I don't know what the context for that is here, though.

Nick Gammon Australia Forum Administrator Sat 08 Nov 2008 02:52 AM #20

That won't work David, as it will quote NIL, (ie. 'NIL') which isn't what you want.

A helper function could return the string, pre-quoted, or NIL, not quoted, if you wanted to go that way.

Orogan, I think you need a table within a table. See this:


list = { name="blah", class="blah", Level=15,}

require "tprint"

tprint (list)

Output

"class"="blah"
"name"="blah"
"Level"=15

You don't have one entry with three things in it (class, name, level) you have 3 entries, each with one. So the first one will have class "blah" but no name, and the second one name "blah" but no class.

This works better:


list = { 

  -- one item:

    { name="blah", class="blah", Level=15,} ,

     }  -- end list


for i, p in ipairs (list) do
 assert (con:execute(string.format([[
    INSERT INTO players
    VALUES ('%s', '%s')]], p.name, p.class)
  )
end  -- for loop

Orogan Sat 08 Nov 2008 11:02 AM #21

You'r right Nick,the way I done it was this



list = { name= "blah", class="blah", level=15,}
table.insert (t,list)

for i, p in ipairs (t) do
 assert (con:execute(string.format([[
    INSERT INTO players
    VALUES ('%s', '%s', '%s')]], tostring(p.name), tostring(p.class), p.Level)
  )
end  -- for loop

And this works fine as long as there's no ' in the strings.
So what i did is this

change:
tostring(p.name)

into:

string.gsub(tostring(p.name),"'","")

As you can see I just removed the '.
This works for me,but I was wondering if there was an other way around so I could keep the '?

On another note the '%s' doesn't need to be changed into '%d' even when the database is set to only recieve numbers.

As far as the NIl goes I take care of that when then table is constructed, so no problem for me there.;)

Thanks for the help all.

David Haley USA Sat 08 Nov 2008 08:02 PM #22

Actually I did mean to set the value as the string nil, because I wanted to guarantee that the table always had some string in each column, instead of having to worry about null values. By choosing the string "NIL" I was perhaps confusing things by injecting semantic importance where I didn't mean to; "MISSING" would have worked just as well. It was somewhat hackish though, I'll admit. :-)

Prattler Lithuania Fri 09 Jan 2009 05:18 PM #23

For newer versions of odbc.dll use:


-- load the ODBC dll
assert (package.loadlib ("odbc.dll", "luaopen_luasql_odbc")) ()

I was able to connect MUSHclient and MySQL on linux via ODBC. Thanks!

WillFa USA Fri 09 Jan 2009 05:47 PM #24

For newer versions. Place the DLL in a LuaSQL folder under the MushClient Lua directory (C:\Program Files\MushClient\Lua\LuaSQL\odbc.dll) then you can just

require "luasql.odbc"

Nick Gammon Australia Forum Administrator Fri 09 Jan 2009 09:11 PM #25

Quote:

This works for me,but I was wondering if there was an other way around so I could keep the '?

I think with SQL you can escape the quotes, probably with \', so instead of changing ' to nothing change it to \'.

Nick Gammon Australia Forum Administrator Tue 21 Sep 2010 06:28 AM #26

In fact, standard SQL expects quotes to be doubled, not escaped.

So, for example:


Nick's cat

should be:


Nick''s cat

To do that in Lua you can make a helper function to double the quotes, like this:


function fixsql (s)
  return (string.gsub (s, "'", "''")) 
end -- fixsql