Database API¶

The database quest API allows scripts to connect to a MySQL/MariaDB database and perform queries without the need to install LuaSQL or use perl DBI.

Database Resources¶

Ownership of handles that wrap database resources are adopted by the scripting language. The internal resource will be freed when the handle is destroyed or its close() method is called.

Lua is garbage collected so these handles should be closed explicitly when no longer needed. If not closed then Lua will hold database resources open for an indeterminate time until its GC runs. Since errors will halt script execution this can prevent handles from being closed. See the errors section for a workaround.

Perl is reference counted so handles will be closed when its last reference is destroyed by going out of scope (even on errors).

Database Connections¶

Connections can be made manually or by using exported constants to connect to the server databases specified in eqemu_config.

The API uses the current zone connections by default when using a server database constant. An overload is available to create Database objects that make new connections.

Making new database connections avoids possible concurrency issues since the zone database connections might be used in other threads. The connection is synchronized for some prepared statement operations but safety isn't guaranteed. See the developer thread safety section for more info.

New connections also allow the use of unbuffered results with prepared statements. If a prepared statement is executed with buffering disabled, then all results must be fetched or freed before any other queries on that connection can occur. Failure to consume all results before the next query will cause a "Commands out of sync" error. If zone connections were used this would cause all zone queries on that connection to start failing if a script error occured before results could be fetched, or the server tried to perform a query in another thread before the script finished fetching results.

LuaPerl

local db = Database() -- connects to default database using eqemu config credentials
local db = Database(Database.Default)
local db = Database(Database.Content)
local db = Database(Database.Default, false) -- uses zone's database connection
local db = Database("127.0.0.1", "user", "pass", "db", 3306)

my $db = Database::new(); # connects to default database using eqemu config credentials
my $db = Database::new(Database::Default);
my $db = Database::new(Database::Content);
my $db = Database::new(Database::Content, 0); # uses zone's content_db connection
my $db = Database::new("127.0.0.1", "user", "pass", "db", 3306);

Errors¶

Errors in prepared statements will propagate the std::runtime_error exception as a quest error and automatically halt the script. This is similar to the behavior of using assert() in Lua or die in perl to error out of the script.

This means db resources cannot be freed automatically on errors since the script gets halted and will never reach any calls to close() resources. Perl will destroy objects in scope via reference counting so it has less risk of holding resources open. In Lua this is a problem because the GC may not run immediately (if ever). To work around this in Lua, scripts can wrap API calls with pcall() to manually catch db errors and free resources.

Catching Lua Errors¶

local query = "select * from badtable where id = ?"
local db = Database()

local ok, stmt = pcall(function() return db:prepare(query) end)
if not ok then
  db:close()
  if stmt then error("error: " .. stmt) end
end

local ok, err = pcall(function() return stmt:execute({1}) end)
if not ok then
  stmt:close()
  db:close()
  if err then error("error: " .. err) end
end

-- do stuff

stmt:close()
db:close()

Manually catching errors like this should generally not be necessary except for custom handling.

Example¶

Warning

Query strings formed by concatenating the values can be vulnerable to SQL injection. It's recommended to always use value placeholders with prepared statements.

LuaPerl

local db = Database(Database.Content)
local stmt = db:prepare("select * from items where id = ? or name = ?")
stmt:execute({ 1001, "Soul's Eye" })
local row = stmt:fetch_hash()
while row do
  eq.debug(string.format("id %d, name %s", row.id, row.Name))
  row = stmt:fetch_hash() -- next
end
stmt:close()
db:close()

my $db = Database::new(Database::Content);
my $stmt = $db->prepare("select * from items where id = ? or name = ?");
$stmt->execute(1001, "Soul's Eye");
while (my $row = $stmt->fetch_hashref())
{
  quest::debug("id: " . $row->{"id"} . " name: " . $row->{"Name"});
}
$stmt->close();
$db->close();

Lua Iterator¶

It may be convenient to use custom iterators to fetch rows in Lua:

local function array_iter(stmt)
  return function() return stmt:fetch_array() end
end

local function hash_iter(stmt)
  return function() return stmt:fetch_hash() end
end

local stmt = db:prepare("select * from items where id = ? or name = ?")
stmt:execute({ 1001, "Soul's Eye" })
for row in hash_iter(stmt) do
  eq.debug(string.format("id %d, name %s", row["id"], row["Name"]))
end

Passing NULL¶

Lua tables cannot have nil values. Using nil in calls to execute() will not work and result in the wrong number of expected arguments. Passing NULL in execute() requires passing a table instead. Perl can use undef.

LuaPerl

local null = {} -- re-usable
local stmt = db:prepare("INSERT INTO some_table (id, nullable, str) VALUES (?, ?, ?)")
stmt:execute({ 1, null, "value" })
eq.debug("affected: " .. stmt:rows_affected())

my $stmt = $db->prepare("INSERT INTO some_table (id, nullable, str) VALUES (?, ?, ?)");
$stmt->execute(1, undef, "value");
quest::debug("affected: " . $stmt->rows_affected());

BigInt Columns¶

Lua prior to version 5.3 only has 53-bit integer precision because all numbers are stored as double. 64-bit BIGINT column values are pushed as strings when prepared statement rows are fetched to avoid loss of precision. This leaves scripts responsible for either converting the value to a number and losing precision, using a big number library, or using LuaJIT FFI. Big integers larger than 2⁵³ should also be passed as strings when executing prepared statements from scripts.

Note

Lua 5.3 and later support a separate integer type stored as signed 64-bit integer by default. Large unsigned 64-bit integers will overflow into negative signed values in those versions.

In perl 64-bit integers are supported when building for 64-bit targets so no special handling is done when pushing from the quest API.

API¶

Constants¶

Connection constants are used to identify zone database connections.

LuaPerl

Constant	Value	Description
Database.Default	0	Reference the default database connection
Database.Content	1	Reference the content database connection

Constant	Value	Description
Database::Default	0	Reference the default database connection
Database::Content	1	Reference the content database connection

Database¶

Constructors

LuaPerl

Database(): Returns a Database object that connects to the server's default database connection. If the connection fails then a std::runtime_error exception is thrown and script execution halts.
Database(Connection type, bool connect = false): Returns a Database object for the specified server database type specified in the eqemu_config. If connect is true a new connection is made to the database. If connect is false the Database object uses the zone's current active server connection. If the connection fails or the connection type is invalid a std::runtime_error exception is thrown and script execution halts.
Database(string host, string user, string pass, string dbname, int port): Returns a Database object that connects to the specified database. If the connection fails then a std::runtime_error exception is thrown and script execution halts.

Database::new(): Returns a Database object that connects to the server's default database connection. If the connection fails then a std::runtime_error exception is thrown and script execution halts.
Database::new(Connection type, bool connect = false): Returns a Database object for the specified server database type specified in the eqemu_config. If connect is true a new connection is made to the database. If connect is false the Database object uses the zone's current active server connection. If the connection fails or the connection type is invalid a std::runtime_error exception is thrown and script execution halts.
Database::new(string host, string user, string pass, string dbname, int port): Returns a Database object that connects to the specified database. If the connection fails then a std::runtime_error exception is thrown and script execution halts.

Functions

close(): Closes the database connection. The quest object is invalidated and should not be used after this is called.
prepare(string query): Prepares a statement and returns a MySQLPreparedStmt object. Throws std::runtime_error on error which halts the script with a quest error message.

MySQLPreparedStmt¶

close()

Deletes the underlying mysql::StmtResult and mysql::PreparedStmt resources. Closes the prepared statement by calling mysql_stmt_close() which will free any active result set. The quest object is invalidated and should not be used after this is called.

execute()

execute(array values)

Executes the prepared statement with specified input arguments. Results are accessible from the current object. Invalidates any previous result set.

Example

LuaPerl

local stmt = db:prepare("select * from items where id = ? or name = ?")
stmt:execute({ 1001, "Soul's Eye" })
eq.debug("result row count: " .. stmt:num_rows())

my $stmt = $db->prepare("select * from items where id = ? or name = ?");
$stmt->execute(1001, "Soul's Eye");
quest::debug("result row count: " . $stmt->num_rows());

fetch()

Alias for fetch_array.

fetch_array()

Fetches the next row and returns an array of column values. Value types will be either numbers or strings to match field types unless NULL.

LuaPerl

NULL column values will not exist in the returned table. This can leave gaps in table indexes which means ipairs should not be used to iterate it. Returns nil if no more rows to fetch.

NULL column values will have undef values in the array. Returns an empty value if no more rows to fetch.

fetch_arrayref() _{^{Perl only}}

Fetches the next row and returns a reference to an array of column values. NULL column values will have undef values in the array. Other value types will be either numbers or strings to match field types. Returns an empty value if no more rows to fetch.

fetch_hash() _{^{Lua only}}

Fetches the next row and returns a hash table of column values with field name keys. NULL column values will not exist in the returned table. Other value types will be either numbers or strings to match field types. Returns nil if no more rows to fetch.

fetch_hashref() _{^{Perl only}}

Fetches the next row and returns a reference to a hash of column values with field name keys. NULL column values will have undef values in the hash. Other value types will be either numbers or strings to match field types. Returns an empty value if no more rows to fetch.

insert_id()

Returns the last insert id of an AUTO_INCREMENT column generated by an INSERT query.

num_fields()

Returns the number of fields in a result set for a SELECT query.

num_rows()

Returns the number of rows in a result set for a SELECT query. If buffering is disabled for the prepared statement then this will return 0.

rows_affected()

Returns the number of rows affected by an UPDATE, DELETE, or INSERT query.

set_options(hash options)

Sets prepared statement options using a hash table with option name as key. Preserves current value of unspecified options.

Option	Default	Description
`buffer_results`	true	Enable buffering the entire result set of an executed prepared statement
`use_max_length`	true	Enable the `max_length` of fields to be calculated on execution (requires buffering)

Example

LuaPerl

local stmt = db:prepare("select * from table")
stmt:set_options({ ["buffer_results"] = false })

my $stmt = $db->prepare("select * from table");
$stmt->set_options(("buffer_results" => 0));