MySQL Actions Plugin

Actions

MySQL.AutoCommit();

Description:

Sets autocommit mode on if Mode is true, off if Mode is false.

Mode:

(boolean) The commit mode to set.

Returns:

(number) Zero if successful, nonzero if an error ocurred.

MySQL.Close();

Description:

Terminates the connection and frees up resources. You can't run any queries once the connection to the database is closed.

Returns:

(value) 0 if success, 1 otherwise. You can use Application.GetLastError to determine whether this action failed, and why. Additionally, the function MySQL.GetError() can be used to retrieve a verbose error message.

MySQL.Commit();

Description:

Commits the current transaction.

Returns:

(nothing)

MySQL.Connect(string Host, string Username, string Password, string Database, number Port);

Description:

Connects to a MySQL database server.

Host:

(string) The host to connect to. This can be "localhost" or an ip address.

Usernam:

(string) Username to be used in the connection.

Password:

(string) The password of the user account.

Database:

(string) The name of the database to use for queries.

Port:

(number) The optional port number to connect. Can be left blank, so the default port (3306) will be used.

Returns:

(value) 0 if success, 1 otherwise. You can use Application.GetLastError to determine whether this action failed, and why.

Example:

result = MySQL.Connect("localhost", "root", "password", "database");
if (result ~= 0) then
    error = Application.GetLastError();
    Dialog.Message("MySQL Actions Plugin", "Connection failed: (" .. MySQL.GetError() .. ")", MB_OK, MB_ICONSTOP);
end

MySQL.GetError();

Description:

Returns the last error message from the MySQL database server.

Returns:

(string) The error message given by the MySQL server.

Example:

Dialog.Message("MySQL Actions Plugin", "Query failed: (" .. MySQL.GetError() .. ")", MB_OK, MB_ICONSTOP);

MySQL.GetFieldNames();

Description:

Retrieves the names of the columns in the last recordset that was returned.

Returns:

(table) A table containing the names of the column headers, or nil if no records where returned. You can use Application.GetLastError to determine whether this action failed, and why.

Example:

header = MySQL.GetFieldNames();
for col, text in header do
    Grid.SetCellText("Grid1", 0, col-1, text, false); -- fill cells of first row with headers
end

MySQL.GetFields();

Description:

Retrieves the number of columns in the last recordset that was returned.

Returns:

(value) The number of columns in the recordset. If the value returned is zero unexpectedly, you can use Application.GetLastError to determine whether this action failed, and why.

Example:

-- adjust grid to resultset size
Grid.SetColumnCount("Grid1", MySQL.GetFields());

MySQL.GetRows();

Description:

Retrieves the number of rows in the last recordset that was returned.

Returns:

(number) The number of rows in the recordset. If the value returned is zero unexpectedly, you can use Application.GetLastError to determine whether this action failed, and why.

Example:

-- adjust grid to resultset size
Grid.SetRowCount("Grid1", MySQL.GetRows()+1); -- reserve first line for column headers

MySQL.GetServerInfo();

Description:

Retrieves the server version info.

Returns:

(string) A string that represents the server version number, or nil in case of an error. You can use Application.GetLastError to determine whether this action failed, and why.

Example:

-- display MySQL server version
Dialog.Message("MySQL Actions Plugin", "This server is running " .. MySQL.GetServerInfo(), MB_OK, MB_ICONINFORMATION);

MySQL.Ping();

Description:

Checks if a connection to a MySQL server is alive, and that the server is able to execute commands. This command may cause a reconnection.

Returns:

(number) 0 if success, 1 otherwise. You can use Application.GetLastError to determine whether this action failed, and why.

Example:

if (MySQL.Ping() ~= 0) then
    Dialog.Message("MySQL Actions Plugin", "MySQL server is not responding", MB_OK);
end

MySQL.Query(string Query);

Description:

Sends a query to a connected database. Should be used when no resultset is returned (UPDATE, DELETE and INSERT statements).

Query:

(string) The query to be run on the database.

Returns:

(number) Number of rows affected by the command, or -1 in case of an error. You can use Application.GetLastError to determine whether this action failed, and why. Additionally, the function MySQL.GetError() can be used to retrieve a verbose error message if the query fails.

Example:

res = MySQL.Query("DELETE FROM client WHERE id = 666");

MySQL.QueryToTable(string Query, boolean convertBinary=true);

Description:

Sends a query to a connected database. The whole resultset will be returned completely, as a table. The first row will contain the column names, which may be used as headers for the grid. (Should be used with SELECT statements.)

Query:

(string) The query to be run on the database.

convertBinary:

(boolean) Optional argument, set to false if you don't wish to convert binary fields to base64 automatically.

Returns:

(table) A table containing the result, or nil if no records where returned. Each element of the table is a single record, and each element is a table by itself, containing the columns. A resultset containing two records with five columns each would create a table[2][5]. You can use Application.GetLastError to determine whether this action failed, and why. Additionally, the function MySQL.GetError() can be used to retrieve a verbose error message if the query fails.

Note:

Binary fields are returned as Base64-encoded strings, unless you set convertBinary to false. Both BLOB and TEXT fields hold binary data. From the MySQL manual:

"A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types are TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. These differ only in the maximum length of the values they can hold. The four TEXT types are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to the four BLOB types and have the same maximum lengths and storage requirements."

If you need to return binary data, you can recover the original data using Crypto.Base64DecodeFromString(field). A BLOB or a TEXT field will always be binary, it does not matter if you store an image in the field or a text file. Of course, if you just want to store text, a VARCHAR would probably make more sense, unless you need larger storage.

Example:

resultset = MySQL.QueryToTable("SELECT id, name, address FROM client");
error = Application.GetLastError();
if (error ~= 0) then
    Dialog.Message("MySQL Actions Plugin", "Query failed: (" .. MySQL.GetError() .. ")", MB_OK, MB_ICONSTOP);
else
    -- adjust grid to resultset size
    Grid.SetColumnCount("Grid1", MySQL.GetFields());
    Grid.SetRowCount("Grid1", MySQL.GetRows()+1); -- reserve one extra line for the column header

    -- show headers
    header = MySQL.GetFieldNames();
    for j,celltext in header do
        Grid.SetCellText("Grid1", 0, j-1, celltext, false);
    end

    -- show resultset in grid
    for i,row in resultset do
        for j,celltext in row do
            Grid.SetCellText("Grid1", i, j-1, celltext, false);
        end
    end

    -- now display the data in the grid
    Grid.Refresh("Grid1");
end

MySQL.RealEscapeString(string fromString);

Description:

This function is used to create a legal SQL string that you can use in an SQL statement. The string in fromString is transformed into an escaped SQL string, taking into account the current character set of the connection (see MySQL.SetCharacterSet()).

fromString:

(string) The string to be escaped.

Returns:

(string) The encoded string, using the current character set. In caso of an error, an empty string is returned. You can use Application.GetLastError to determine whether this action failed, and why.

Example:

res = MySQL.RealEscapeString("Cote d'Ivoire");
-- res will now contain "Cote d\'Ivoire"

MySQL.Rollback();

Description:

Rolls back the current transaction.

Returns:

(number) Zero if successful, nonzero if an error ocurred.

MySQL.SetCharacterSet(string Charset);

Description:

This function is used to set the default character set for the current connection.

Charset:

(string) The character set to be used.

Returns:

(number) Zero if successful, nonzero if an error ocurred. You can use Application.GetLastError to determine whether this action failed, and why.

Example:

res = MySQL.SetCharacterSet("utf8");

MySQL.Status();

Description:

Returns a character string containing information similar to that provided by the mysqladmin status command. This includes uptime in seconds and the number of running threads, questions, reloads, and open tables.

Returns:

(string) A string that represents the server status information.

Example:

-- display MySQL server status info
Dialog.Message("MySQL Actions Plugin", "Server status: " .. MySQL.Status(), MB_OK, MB_ICONINFORMATION);