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

MUSHclient scripting

Description of MUSHclient world function: world.DatabasePrepare


Name DatabasePrepare
Type Method
Summary Prepares an SQL statement for execution
Prototype long DatabasePrepare(BSTR DbName, BSTR Sql);

View list of data type meanings

Description

Prepares (compiles) an SQL statement, ready to be executed with DatabaseStep.

You need to supply the database id of an existing opened database, and the SQL statement to be executed. Only one statement should be supplied. If multiple statements are supplied, only the first will be used. (eg. "drop table a; drop table b" - in this case only the first "drop table" would be executed).

If there is a syntax error in your SQL, that will be reported by a non-zero return code from DatabasePrepare. The most recent error message string (if any) can be obtained by calling DatabaseError.

Note that to include single quotes inside a string literal, in your SQL statement they must be doubled. For example:

INSERT INTO weapons (name, damage) VALUES ('Nick''s sword', 42)

Only one statement can be active at once, so after doing a DatabasePrepare you must do DatabaseFinalize before doing another DatabasePrepare. The exception is that if DatabasePrepare returns an error, then you do not need to do DatabaseFinalize because there is no active statement.

After successfully doing DatabasePrepare, you then need to call DatabaseStep for each row in the query. This is the execution phase. For SQL statements that return multiple rows of data, you need to do DatabaseStep once for each row. For SQL statements that just do one thing (like INSERT INTO) then you only need to call DatabaseStep once.

Also see DatabaseExec if you only need to execute SQL statements (like INSERT INTO) which do not return any results.


Note: Available in version 4.40 onwards.


Lua example
-- insert a record
DatabasePrepare ("db", "INSERT INTO weapons (name, damage) VALUES ('sword', 42)")  --> returns 0 (sqlite3.OK)
DatabaseStep ("db")   -- returns 101 (sqlite3.DONE)
DatabaseFinalize ("db")  -- returns 0 (sqlite3.OK)


-- see the DatabaseStep entry for a more elaborate example.


-- A suitable function for fixing up strings is this:

-- 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
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
Returns 0: Completed OK
-1 : Database id not found
-2 : Database not open
-3 : Already have prepared statement

Otherwise an SQLite return code. For example:

1: SQL error or missing database
5 : The database file is locked
Introduced in version 4.40

See also ...

Function Description
DatabaseClose Closes an SQLite database
DatabaseError Returns an English string describing the most recent SQL error
DatabaseExec Executes SQL code against an SQLite database
DatabaseFinalize Finalizes (wraps up) a previously-prepared SQL statement
DatabaseOpen Opens an SQLite database

Search for script function

Enter a word or phrase in the box below to narrow the list down to those that match.

The function name, prototype, summary, and description are searched.

Search for:   

Leave blank to show all functions.


Return codes

Many functions return a "code" which indicates the success or otherwise of the function.

You can view a list of the return codes


Function prototypes

The "prototype" part of each function description lists exactly how the function is called (what arguments, if any, to pass to it).

You can view a list of the data types used in function prototypes


View all functions

[Back]

Quick links: MUSHclient. MUSHclient help. Forum shortcuts. Posting templates. Lua modules. Lua documentation.

Information and images on this site are licensed under the Creative Commons Attribution 3.0 Australia License unless stated otherwise.

[Home]


Written by Nick Gammon - 5K   profile for Nick Gammon on Stack Exchange, a network of free, community-driven Q&A sites   Marriage equality

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

[Best viewed with any browser - 2K]    [Hosted at FutureQuest]