Script function

world.DatabaseExec

Type

Method

Summary

Executes SQL code against an SQLite database

Prototype

long DatabaseExec(BSTR DbName, BSTR Sql);

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.

Available in MUSHclient version 4.40 onwards.

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

See Also ...

Topics

Database (SQLite)
Lua SQLite (database) interface
Scripting

Functions

(DatabaseChanges) Returns a count of the changes to the database by the most recent SQL statement
(DatabaseClose) Closes an SQLite database
(DatabaseColumnName) Find the name of a specified column returned by an SQL statement
(DatabaseColumnNames) Return a table of all the columns returned by an SQL statement
(DatabaseColumns) Find how many columns will be returned by an SQL statement
(DatabaseColumnText) Returns the contents of an SQL column, as text
(DatabaseColumnType) Returns the type of data in an SQL column
(DatabaseColumnValue) Returns the contents of an SQL column, as text, float, integer, or null
(DatabaseColumnValues) Returns the contents of all the SQL columns after a step
(DatabaseError) Returns an English string describing the most recent SQL error
(DatabaseFinalize) Finalizes (wraps up) a previously-prepared SQL statement
(DatabaseGetField) Returns a single field from an SQL database
(DatabaseInfo) Returns information about a database
(DatabaseLastInsertRowid) Returns the most recently automatically allocated database key
(DatabaseList) Lists all databases
(DatabaseOpen) Opens an SQLite database
(DatabasePrepare) Prepares an SQL statement for execution
(DatabaseReset) Resets a previously-prepared SQL statement to the start
(DatabaseStep) Executes a previously-prepared SQL statement
(DatabaseTotalChanges) Returns a count of the total changes to the database

(Help topic: function=DatabaseExec)