Gammon Forum : MUSHclient : Lua : Lua, MS Access databases?

Register forum user name Search FAQ

Gammon Forum

Entire forum

MUSHclient

Lua

Lua, MS Access databases?

Lua, MS Access databases?

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

Pages: 1 2

Posted by

Trevize (21 posts)

Date

Thu 09 Feb 2006 09:59 PM (UTC)

Message

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.

Posted by

David Haley USA (3,881 posts)

Date

Reply #1 on Thu 09 Feb 2006 10:09 PM (UTC)

Message

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/

David Haley aka Ksilyan
Head Programmer,
Legends of the Darkstone

http://david.the-haleys.org

Posted by

Nick Gammon Australia (22,928 posts)

bio Forum Administrator

Date

Reply #2 on Fri 10 Feb 2006 01:53 AM (UTC)

Message

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

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

Posted by Nick Gammon Australia (22,928 posts) bio Forum Administrator

Date

Reply #3 on Sat 11 Feb 2006 11:36 PM (UTC)

Amended on Sat 11 Feb 2006 11:43 PM (UTC) by Nick Gammon

Message

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

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

Posted by Nick Gammon Australia (22,928 posts) bio Forum Administrator

Date Reply #4 on Sat 08 Sep 2007 02:47 AM (UTC)

Message

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

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

Posted by

Nick Gammon Australia (22,928 posts)

bio Forum Administrator

Date

Reply #5 on Sat 08 Sep 2007 02:51 AM (UTC)

Message

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

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

- Nick Gammon

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

Posted by

Katherina (19 posts)

Date

Reply #6 on Thu 23 Oct 2008 02:09 PM (UTC)

Message

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.

Posted by Nick Gammon Australia (22,928 posts) bio Forum Administrator

Date Reply #7 on Fri 24 Oct 2008 05:32 AM (UTC)

Message

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

- Nick Gammon

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

Posted by

Katherina (19 posts)

Date

Reply #8 on Fri 24 Oct 2008 02:14 PM (UTC)

Amended on Fri 24 Oct 2008 04:00 PM (UTC) by Katherina

Message

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.

Posted by

Katherina (19 posts)

Date

Reply #9 on Sat 25 Oct 2008 01:16 AM (UTC)

Message

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.

Posted by Nick Gammon Australia (22,928 posts) bio Forum Administrator

Date Reply #10 on Sat 25 Oct 2008 01:36 AM (UTC)

Message

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?

- Nick Gammon

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

Posted by

Katherina (19 posts)

Date

Reply #11 on Sat 25 Oct 2008 01:50 AM (UTC)

Amended on Sat 25 Oct 2008 01:56 AM (UTC) by Katherina

Message

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.

Posted by Nick Gammon Australia (22,928 posts) bio Forum Administrator

Date Reply #12 on Sat 25 Oct 2008 03:54 AM (UTC)

Message

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.

- Nick Gammon

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

Posted by

Katherina (19 posts)

Date

Reply #13 on Sat 25 Oct 2008 04:07 AM (UTC)

Message

I love you

Posted by Orogan (23 posts) bio

Date Reply #14 on Thu 06 Nov 2008 11:10 PM (UTC)

Message

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.

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.

91,248 views.

This is page 1, subject is 2 pages long: 1 2

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

Go to topic: Search the forum

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.

Comments to: Gammon Software support
Forum RSS feed ( https://gammon.com.au/rss/forum.xml )