world.DatabaseExec

MUSHclient script function (Method) — introduced in version 4.40

Executes SQL code against an SQLite database

Prototype

long DatabaseExec(BSTR DbName, BSTR Sql);

Data type meanings

Description

This executes SQL on a database previously opened with DatabaseOpen.

You need to supply the database id of an existing opened database, and the SQL statements to be executed. More than one statement may be supplied, separated by semicolons. If one statement causes an error, execution will be terminated. The most recent error message string (if any) can be obtained by calling DatabaseError.

After successful execution, DatabaseExec will return 0. For more explanation if zero is not returned, call DatabaseError.

You cannot call DatabaseExec if you have called DatabasePrepare but not DatabaseFinalize, as a statement is currently being processed.

DatabaseExec is not suitable for executing SQL code that queries the database for data (like a SELECT statement), as there is no provision for getting the results back. In that situation use DatabasePrepare / DatabaseStep / DatabaseFinalize.

To obtain a single row and value, call DatabaseGetField.

Lua example

DatabaseOpen ("db", GetInfo (66) .. "mytestdb.sqlite", 6)

DatabaseExec ("db", [[
DROP TABLE IF EXISTS weapons;
CREATE TABLE weapons(
        weapon_id INTEGER NOT NULL PRIMARY KEY autoincrement,
        name  TEXT NOT NULL,
        damage INT default 10,
        weight REAL
      );
      ]])
      
DatabaseClose ("db")  -- close it

Lua notes

The return codes are available in the sqlite3 table in Lua, as follows:

sqlite3.OK = 0
sqlite3.INTEGER = 1
sqlite3.INTERNAL = 2
sqlite3.PERM = 3
sqlite3.ABORT = 4
sqlite3.BUSY = 5
sqlite3.LOCKED = 6
sqlite3.NOMEM = 7
sqlite3.READONLY = 8
sqlite3.INTERRUPT = 9
sqlite3.IOERR = 10
sqlite3.CORRUPT = 11
sqlite3.NOTFOUND = 12
sqlite3.FULL = 13
sqlite3.CANTOPEN = 14
sqlite3.PROTOCOL = 15
sqlite3.EMPTY = 16
sqlite3.SCHEMA = 17
sqlite3.TOOBIG = 18
sqlite3.CONSTRAINT = 19
sqlite3.MISMATCH = 20
sqlite3.MISUSE = 21
sqlite3.NOLFS = 22
sqlite3.FORMAT = 24
sqlite3.RANGE = 25
sqlite3.NOTADB = 26
sqlite3.ROW = 100
sqlite3.DONE = 101

-- ---------------------------------
-- Suggested helper functions for handling errors, and fixing up SQL with quotes inside it are:
-- ---------------------------------

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

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

Return value

0: Executed OK
-1 : Database id not found
-2 : Database not open
-3 : Already have prepared statement