Overview
AutoPlay Media Studio includes support for several database technologies including MySQL, Oracle, and Sqlite3. This database functionality makes use of the LuaSQL library designed and implemented by the Kepler Project team.
Each supported database technology has a corresponding category of actions. These actions are hidden from the Action Wizard by default, and will only be shown after you enable support for the database in your project.
You can enable or disable support for databases individually on the Databases dialog, which you can access by choosing Project > Databases from the menu.
Note: When you enable support for a database in AutoPlay, the appropriate LuaSQL require statement will automatically be performed for you when the runtime (autorun.exe) launches. For example, if you enable MySQL support you do not need to add require "luasql.mysql" to your scripts, it will be done automatically.
The database actions support two return values. The first return value returns a value that is specific to each action. The second return value is optional, and is used to receive an error string from the database driver if one is available.
Normally the first return variable will contain a value, and the second return variable will be nil.
In the case of an error, the first return value will be nil, and the second return value will be a string containing the error message if one is available. (If there is no error message available, both return values will be nil.)
Note: Use of the second return value is optional -- you may choose not to specify any variable to receive it, and it will simply be ignored.
All of the database actions have nearly the same syntax across the different database technologies.
In the following section, replace the "xxxx" part of each method name with the name of each action category. For example:
|
Where you see this: |
Use this: |
|
xxxx:close() |
MySQL:close(), SQLite3:close(), etc. |
|
xxxxConnection:commit() |
MySQLConnection:commit(), OracleConnection:commit(), etc. |
Some of the actions return an object, for example MySQL:connect() returns an object that represents the connection to the database. Although you may technically name these objects any valid Lua variable name, AutoPlay requires that you use specific names in order to enable script editor integration (Quick Help, intellisense, etc.). So, you should name the first return variable from MySQL:connect() "MySQLConnection" if you want the script editor features to work.
An environment object represents a link with the database driver, and is normally returned from a call to an initialization function. This initialization is automatically performed for you at startup for each database you enable on the Databases dialog. The environment object for each database will also automatically be closed on shutdown.
So, in AutoPlay there is no need to perform a call such as:
env = luasql.mysql()
Instead, the equivalent of the following is automatically performed:
MySQL = luasql.mysql();
In AutoPlay, the environment objects are represented by the following action categories:
MySQL
SQLite3
Oracle
ODBC
PostgreSQL
Connects to a data source specified in sourcename using username and password if they are supplied. The sourcename may vary according to each database type.
For MySQL and PostgreSQL, you may also provide an optional hostname and port:
MySQL:connect(sourcename[,username[,password[,hostname[,port]]]])
PostgreSQL:connect(sourcename[,username[,password[,hostname[,port]]]])
Note: For PostgreSQL, MySQL and SQLite the sourcename is the database name. For ODBC it's the name of the DSN, and for Oracle driver it's the service name.
Returns a connection object that represents the data source connection.
Closes the database environment. Only successful if all connections pertaining to it are closed first.
Returns true if successful. Returns false if the object is already closed.
A connection object contains specific attributes and parameters for a single data source connection. A connection object is created using the xxxx:connect() action.
For full script editor integration you should name your connection objects "xxxxConnection" where "xxxx" is replaced by the database name, e.g. MySQLConnection.
Closes the connection. Only successful if all cursors pertaining to it have been closed and the connection is still open.
Returns true if successful.
Commits the current transaction. This feature might not work on database systems that do not implement transactions.
Returns true if successful. Returns false if the operation could not be performed or is not implemented.
Executes the given SQL statement.
Returns a cursor object if there are results, or the number of rows affected by the command otherwise.
Rolls back the current transaction. This feature might not work on database systems that do not implement transactions.
Returns true if successful. Returns false if the operation could not be performed or is not implemented.
Turns on or off the "auto commit" mode. This feature might not work on database systems that do not implement transactions.
Returns true if successful. Returns false if the operation could not be performed or is not implemented.
A cursor object contains methods to retrieve data resulting from an executed statement. A cursor object is created using the xxxxConnection:execute() action.
For full script editor integration you should name your connection objects "xxxxCursor" where "xxxx" is replaced by the database name, e.g. OracleCursor.
Closes the cursor.
Returns true if successful. Returns false if the object is already closed.
Retrieves the next row of results.
If fetch is called without parameters, the results will be returned directly to the caller. If fetch is called with a table, the results will be copied into that table and the changed table will be returned. In this case, an optional modestring parameter can be used. It is just a string indicating how the resulting table should be constructed. The mode string can contain:
"n" the resulting table will have numerical indices (default)
"a" the resulting table will have alphanumerical indices
The numerical indices are the positions of the fields in the SELECT statement; the alphanumerical indices are the names of the fields.
The optional table parameter is a table that should be used to store the next row. This allows the use of a unique table for many fetches, which can improve the overall performance.
There is no guarantee about the types of the results: they may or may not be converted to adequate Lua types by the driver. In the current implementation, the PostgreSQL and MySQL drivers return all values as strings while the ODBC and Oracle drivers convert them to Lua types.
Returns a table of data, as above, or nil if there are no more rows. Note that this action could return nil as a valid result.
Note: Currently the MySQL driver does not support the optional table or modestring parameters. Its only valid syntax is MySQLCursor:fetch().
Returns a table of column names.
Returns a table of column types.
Returns the number of rows in the query result.
Only available for MySQL, Oracle and PostgreSQL.
Here is an example of basic use of the database actions.
SQLite3Connection, err = SQLite3:connect("test-db");
if not SQLite3Connection and err then
Dialog.Message("Error", err);
end
SQLite3Connection:execute("DROP TABLE people");
SQLite3Connection:execute("CREATE TABLE people(name varchar(50),email varchar(50))");
-- add a few elements from a Lua table into the database table
list = {
{ name="Jose das Couves", email="[email protected]" },
{ name="Manoel Joaquim", email="[email protected]" },
{ name="Maria das Dores", email="[email protected]" }
}
for i, p in pairs(list) do
SQLite3Connection:execute(string.format("INSERT INTO people VALUES ('%s', '%s')", p.name, p.email));
end
-- retrieve a cursor
SQLite3Cursor = SQLite3Connection:execute("SELECT name, email from people");
-- print all the rows
row = SQLite3Cursor:fetch({},"a")
local s = "";
while row do
s = s..(string.format("Name: %s, Email: %s\n", row.name, row.email))
-- get the next row, reusing the same table
row = SQLite3Cursor:fetch(row,"a");
end
Dialog.Message("The 'people' table contains", s);
-- close everything
SQLite3Cursor:close()
SQLite3Connection:close()
For MySQL, the above example would need to be modified a bit since MySQL currently doesn't support the optional fetch() parameters.
-- print all the rows
local s = "";
for i = 1, MySQLCursor:numrows() do
local name,email = MySQLCursor:fetch();
s = s.."Name: "..name..", Email: "..email.."\n";
end
Dialog.Message("The 'people' table contains", s);
You can also iterate over the results using an iterator function:
function rows (connection, sql_statement)
local cursor = assert(connection:execute (sql_statement))
return function ()
return cursor:fetch()
end
end
MySQLConnection = MySQL:connect("mydatabase");
local s = "";
for name, email in rows(MySQLConnection, "select * from contacts") do
s = s .. string.format("Name: %s, Email: %s\n", name, email);
end
Dialog.Message("The 'people' table contains", s);
The database functionality makes use of the LuaSQL library designed and implemented by the Kepler Project team.
Copyright © 2003-2007 The Kepler Project.
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
PostgreSQL Data Base Management System
Portions Copyright (c) 1996-2009, PostgreSQL Global
Development Group
Portions Copyright (c) 1994-1996 Regents of the University of California
Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.
IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.