_{Official ZPE/YASS documentation}zpe.lib.mysql

Introduction

The zpe.lib.mysql plugin provides MySQL database connectivity for ZPE/YASS. It allows scripts to connect to a MySQL server, run queries, retrieve table and column metadata, and execute prepared statements.

The plugin exposes a global function (mysql_connect) which returns a MySQL connection object. The returned connection object can then be used to run queries and prepare parameterised statements.

Installation

Place zpe.lib.mysql.jar in your ZPE native-plugins folder and restart ZPE.

You can also download with the ZULE Package Manager by using:

zpe --zule install zpe.lib.mysql.jar

Usage

Typical usage is:

• Call mysql_connect to create a connection object.
• Call query to run SQL and return a list of results.
• Use prepare and execute for parameterised queries.

Functions

The following global functions are provided by the plugin.

mysql_connect(string host, string database, string user, string password[, number port]) ⇒ object | boolean

Creates a new MySQL connection and returns a ZPEMySQLConnection object.

If the connection fails, returns false.

Default port: if port is not provided, the plugin uses 8889 (as implemented).

Permissions: requires permission level 3.

Objects

ZPEMySQLConnection

Represents an active connection to a MySQL database. You can run queries directly, retrieve table metadata, or create prepared statements.

connect(string host, string database, string user, string password, number port) ⇒ boolean

Connects this connection object to a MySQL database. Returns true on success.

Note: Most scripts will use mysql_connect rather than calling connect directly.

query(string query_str) ⇒ list | boolean

Executes the given SQL query and returns the result as a list.

If the query fails, returns false.

query_to_json(string query_str) ⇒ string | boolean

Executes the given SQL query and returns the result encoded as a JSON string.

Internally, this uses ZenithJSONParser to encode the list result. If the query fails, returns false.

get_tables() ⇒ list | boolean

Returns the names of all tables in the connected database. If the request fails, returns false.

Permissions: requires permission level 3 (as implemented).

get_columns(string table) ⇒ list | boolean

Returns the column names for the given table. If the request fails, returns false.

prepare(string query_str) ⇒ ZPEMySQLPreparedStatement | boolean

Creates a prepared statement object from the given query.

This plugin supports named parameters using a colon prefix (for example :name, :age). Named parameters are internally converted into a JDBC prepared statement with ? placeholders.

Returns a ZPEMySQLPreparedStatement object on success, or false on failure.

ZPEMySQLPreparedStatement

Represents a prepared (parameterised) SQL statement bound to a specific MySQL connection. Prepared statements are recommended whenever you need to insert user input into SQL queries, as they avoid SQL injection vulnerabilities.

prepare(string query_str) ⇒ object

Prepares a new query string on the existing prepared statement object.

Note: In the current implementation, this method returns the native method object rather than the statement object. You should typically call prepare on the connection instead.

execute(map values) ⇒ mixed

Executes the prepared statement using the provided map of parameter values.

The map keys must match the placeholder names exactly, including the leading colon. For example: [=>] { ":name" : "Jamie" }

Returns whatever the underlying MySQL execution function returns (for example a list or boolean), depending on the statement type.

Examples

The following examples demonstrate common usage patterns.

1) Connect and run a query

db = mysql_connect("localhost", "my_database", "root", "password", 3306)

if (db == false)
  print("Could not connect to database")
end if

rows = db.query("SELECT * FROM users")

for (r in rows)
  print(r)
end for

2) Query results as JSON

db = mysql_connect("localhost", "my_database", "root", "password", 3306)

json = db.query_to_json("SELECT id, username FROM users")
print(json)

3) Prepared statement with named parameters

Prepared statements support named placeholders beginning with a colon (for example :id and :name). When executed, you pass a map containing values for each placeholder.

db = mysql_connect("localhost", "my_database", "root", "password", 3306)

stmt = db.prepare("SELECT * FROM users WHERE id = :id")

if (stmt == false)
  print("Statement could not be prepared")
end if

values = [=>]
values[":id"] = 42

rows = stmt.execute(values)

for (r in rows)
  print(r)
end for

Notes and caveats

• Default port: mysql_connect uses port 8889 if none is supplied (as implemented).
• Prepared statement placeholders: placeholder names must include the leading colon (for example :id). The execute map keys must match the placeholders exactly.
• Permissions: mysql_connect requires permission level 3. (Individual object methods may have different permission levels depending on your implementation.)
• Error handling: many methods return false on failure. For detailed error information, refer to the ZPE log output.
• Cross-platform: the plugin reports support for Windows, macOS, and Linux.