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

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

David Haley USA (3,881 posts) Bio

Date

Reply #15 on Thu 06 Nov 2008 11:59 PM (UTC)

Message

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.

David Haley aka Ksilyan
Head Programmer,
Legends of the Darkstone

http://david.the-haleys.org

Top

Posted by

Katherina (19 posts) Bio

Date

Reply #16 on Fri 07 Nov 2008 02:28 AM (UTC)

Message

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

Top

Posted by

Nick Gammon Australia (23,140 posts) Bio Forum Administrator

Date

Reply #17 on Fri 07 Nov 2008 05:50 AM (UTC)

Message

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.

- Nick Gammon

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

Top

Posted by

Orogan (23 posts) Bio

Date

Reply #18 on Fri 07 Nov 2008 08:12 AM (UTC)

Amended on Fri 07 Nov 2008 08:15 AM (UTC) by Orogan

Message

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.

Top

Posted by David Haley USA (3,881 posts) Bio

Date Reply #19 on Fri 07 Nov 2008 05:05 PM (UTC)

Message

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.

David Haley aka Ksilyan
Head Programmer,
Legends of the Darkstone

http://david.the-haleys.org

Top

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

Date Reply #20 on Sat 08 Nov 2008 02:52 AM (UTC)

Message

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

- Nick Gammon

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

Top

Posted by Orogan (23 posts) Bio

Date

Reply #21 on Sat 08 Nov 2008 11:02 AM (UTC)

Amended on Sat 08 Nov 2008 11:03 AM (UTC) by Orogan

Message

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.

Top

Posted by

David Haley USA (3,881 posts) Bio

Date

Reply #22 on Sat 08 Nov 2008 08:02 PM (UTC)

Message

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. :-)

David Haley aka Ksilyan
Head Programmer,
Legends of the Darkstone

http://david.the-haleys.org

Top

Posted by Prattler Lithuania (15 posts) Bio

Date

Reply #23 on Fri 09 Jan 2009 05:18 PM (UTC)

Amended on Fri 09 Jan 2009 05:22 PM (UTC) by Prattler

Message

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!

Top

Posted by

WillFa USA (525 posts) Bio

Date

Reply #24 on Fri 09 Jan 2009 05:47 PM (UTC)

Message

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"

Top

Posted by

Nick Gammon Australia (23,140 posts) Bio Forum Administrator

Date

Reply #25 on Fri 09 Jan 2009 09:11 PM (UTC)

Message

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

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

Top

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

Date Reply #26 on Tue 21 Sep 2010 06:28 AM (UTC)

Message

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

- Nick Gammon

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

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.

108,934 views.

This is page 2, 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

top