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);

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.

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.

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.Rollback();

Description:

Rolls back the current transaction.

Returns:

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

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);