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

Gammon Software Solutions forum

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

[Folder]  Entire forum
-> [Folder]  MUSHclient
. -> [Folder]  Lua
. . -> [Subject]  Lua, MS Access databases?
Home  |  Users  |  Search  |  FAQ
Username:
Register forum user name
Password:
Forgotten password?
(New message)
Subject: Lua, MS Access databases?
Name:
Your forum user name.
Register forum user name
Password:
Your forum password.
Forgotten password?
Message:
Message to be posted (in English, please).
Forum codes:
Check this if your message uses 'forum codes' or templates (auto-detected for new posts).
Forum codes Templates

Save this message ...

Subject review (reverse sequence)

Pages: 1 2  

Posted by Nick Gammon   Australia  (18,772 posts)  [Biography] bio   Forum Administrator
Date Tue 21 Sep 2010 06:28 AM (UTC)  [ quote  ]
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
[Go to top] top
Posted by Nick Gammon   Australia  (18,772 posts)  [Biography] bio   Forum Administrator
Date Fri 09 Jan 2009 09:11 PM (UTC)  [ quote  ]
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
[Go to top] top
Posted by WillFa   USA  (517 posts)  [Biography] bio
Date Fri 09 Jan 2009 05:47 PM (UTC)  [ quote  ]
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"
[Go to top] top
Posted by Prattler   Lithuania  (15 posts)  [Biography] bio
Date Fri 09 Jan 2009 05:18 PM (UTC)  [ quote  ]

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!
[Go to top] top
Posted by David Haley   USA  (3,881 posts)  [Biography] bio   Moderator
Date Sat 08 Nov 2008 08:02 PM (UTC)  [ quote  ]
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
[Go to top] top
Posted by Orogan   (23 posts)  [Biography] bio
Date Sat 08 Nov 2008 11:02 AM (UTC)  [ quote  ]

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.
[Go to top] top
Posted by Nick Gammon   Australia  (18,772 posts)  [Biography] bio   Forum Administrator
Date Sat 08 Nov 2008 02:52 AM (UTC)  [ quote  ]
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
[Go to top] top
Posted by David Haley   USA  (3,881 posts)  [Biography] bio   Moderator
Date Fri 07 Nov 2008 05:05 PM (UTC)  [ quote  ]
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
[Go to top] top
Posted by Orogan   (23 posts)  [Biography] bio
Date Fri 07 Nov 2008 08:12 AM (UTC)  [ quote  ]

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.

[Go to top] top
Posted by Nick Gammon   Australia  (18,772 posts)  [Biography] bio   Forum Administrator
Date Fri 07 Nov 2008 05:50 AM (UTC)  [ quote  ]
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
[Go to top] top
Posted by Katherina   (19 posts)  [Biography] bio
Date Fri 07 Nov 2008 02:28 AM (UTC)  [ quote  ]
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
[Go to top] top
Posted by David Haley   USA  (3,881 posts)  [Biography] bio   Moderator
Date Thu 06 Nov 2008 11:59 PM (UTC)  [ quote  ]
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
[Go to top] top
Posted by Orogan   (23 posts)  [Biography] bio
Date Thu 06 Nov 2008 11:10 PM (UTC)  [ quote  ]
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.
[Go to top] top
Posted by Katherina   (19 posts)  [Biography] bio
Date Sat 25 Oct 2008 04:07 AM (UTC)  [ quote  ]
Message
I love you
[Go to top] top
Posted by Nick Gammon   Australia  (18,772 posts)  [Biography] bio   Forum Administrator
Date Sat 25 Oct 2008 03:54 AM (UTC)  [ quote  ]
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
[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.

19,997 views.

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

[Reply to this subject]  Reply to this subject   [New subject]  Start a new subject   [Refresh] Refresh page

Go to topic:           Search the forum

[Go to top] top

[Home]

Written by Nick Gammon - 5K

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

[Best viewed with any browser - 2K]    [Internet Contents Rating Association (ICRA) - 2K]    [Web site powered by FutureQuest.Net]