Posted by
| Nick Gammon
Australia (22,973 posts) bio
Forum Administrator |
Message
| In Lua, the use of [[ ... ]] is a string constant (similar to "...").
You need to put the contents of "aa" into the string.
For example:
weaponType = "staff"
damage = 42
DatabaseExec ("db", string.format (
"INSERT INTO weapons (name, damage) VALUES ('%s', %i);",
weaponType, damage))
There is a slight problem there, even, because if the weaponType has quotes in it, that will mess up the quotes in the SQL statement. So better is:
-- Quote the argument, replacing single quotes with two lots of single quotes.
-- If nil supplied, return NULL (not quoted).
function fixsql (s)
if s then
return "'" .. (string.gsub (s, "'", "''")) .. "'"
else
return "NULL"
end -- if
end -- fixsql
weaponType = "Nick's staff"
damage = 42
DatabaseExec ("db", string.format (
"INSERT INTO weapons (name, damage) VALUES (%s, %i);",
fixsql (weaponType), damage))
The function fixsql doubles quotes (as required by SQL) and thus it lets you have things with quotes in their names.
And to save headaches later on, check the return code like this:
local MUSHclient_Database_Errors = {
[-1] = "Database id not found",
[-2] = "Database not open",
[-3] = "Already have prepared statement",
[-4] = "Do not have prepared statement",
[-5] = "Do not have a valid row",
[-6] = "Database already exists under a different disk name",
[-7] = "Column number out of range",
} -- end of MUSHclient_Database_Errors
-- check for errors on a DatabaseXXXXX call
function dbcheck (code)
if code == sqlite3.OK or -- no error
code == sqlite3.ROW or -- completed OK with another row of data
code == sqlite3.DONE then -- completed OK, no more rows
return code
end -- if code OK
-- DatabaseError won't return the negative errors
local err = MUSHclient_Database_Errors [code] or DatabaseError("db")
DatabaseExec ("db", "ROLLBACK") -- rollback any transaction to unlock the database
error (err, 2) -- show error in caller's context
end -- dbcheck
Now your code should look like this:
dbcheck (DatabaseExec ("db", string.format (
"INSERT INTO weapons (name, damage) VALUES (%s, %i);",
fixsql (weaponType), damage)))
That extra function call (dbcheck) will raise an error if you get the SQL wrong.
For example, in your original code adding the dbcheck like this:
dbcheck (DatabaseExec ("db", "INSERT INTO weapons (name, damage) VALUES (aa, 35);"))
Gives you this error:
Run-time error
World: smaug2
Immediate execution
[string "Immediate"]:66: no such column: aa
stack traceback:
[C]: in function 'error'
[string "Immediate"]:26: in function 'dbcheck'
[string "Immediate"]:66: in main chunk
Now you can see more clearly that it tried to add "aa" to the database, but it didn't exist. |
- Nick Gammon
www.gammon.com.au, www.mushclient.com | top |
|