Connecting to MySQL in the system

Using system:inmation’s embedded Lua scripting engine it is possible to use the configured ODBC connection to perform CRUD (Create, Read, Update and Delete) operations on the database. This part of the JumpStart will show you how to use objects in system:inmation to execute Lua scripts to perform these operations with MySQL. For clarity, the scripts are presented here with individual objects executing the Create, Read, Update and Delete functions separately. However, it is possible to combine the scripts and perform actions concurrently (see the LuaCRUD Jumpstart for an example). Along with the basic Lua language, the following module is included to access DBMSs:

  • Luasql - More details can be found here and here

ActionItem running a Lua Script - Creating a Table

In order to access the MySQL database we will create an ActionItem to house the Lua script and create a table in the test schema. To do this you must first start DataStudio and login with administrative rights.

  • Right Click on the Core object in the I/O Model tree to open the context menu and select Admin → New → Data Processing → ActionItem

  • In the Create Object wizard, provide a name for the Action Item (CreateTable for example). The first example will be using a Lua script to create a table in the database so the ActionItem is named CreateTable.

  • Click on “…” next to Lua Script Body and insert the following code exchanging out password for the root account password you entered during the MySQL setup:

local driver = require"luasql.odbc" --request driver
local env = driver.odbc() --create environment
local con = env:connect("testMySQL", "root", "password") --connect to MySQL database and schema

--execute SQL command to create table "airport" in test schema
assert(con:execute([[CREATE TABLE Airport
(Code varchar(10) NOT NULL PRIMARY KEY,
Name varchar(50), City varchar(10));]]))

--Close connection and environment
con:close()
env:close()

  • Click Ok to close the script editor then Create to Create the ActionItem in the I/O Model tree

  • The ActionItem should have a green light beside it in the I/O Model indicating that the script ran without any problems. If the script has not run, there will be an error message displayed in the faceplate of the item. Possible errors are discussed in the following text

This script connects to the test schema created in MySQL in the last section and creates a table called “airport”, containing columns titled “Code”, “Name” and “City” with the “Code” column as primary key. This table should be visible in the SCHEMAS sidebar section in MySQL workbench.

The code carries out three steps which will be common in any Lua scripts involving database connections and operation:

  1. Creates the database connection: The first line requests the "luasql.odbc" module and second creates the database environment. A connection is then made to the environment using the "testMySQL" DSN that we set up in the last section and the “root” account. Make sure you enter the password that you entered for the “root” account during the MySQL setup!

  2. SQL command to MySQL: An SQL command is issued as a string through the con:execute function. In this case the command tells MySQL to create the table in the test database with the appropriate columns and primary key.

  3. Close the connection and database environment: This is necessary to allow other connections to the database in the future.

Writing to MySQL Database

-- Create a connection to the database using luasql module
local driver = require"luasql.odbc" --request driver
local env = driver.odbc()   --create environment
local con = env:connect("testMySQL", "root", "password") --connect to MySQL database and schema

-- Create a table ‘list’ containing information for 2 Airports
list = {
    { Code="DEN", Name="Denver International", City="Denver"},
    { Code="HOU", Name="William P. Hobby", City="Houston"},
    { Code="ORD", Name="Chicago O'Hare", City="Chicago"},
}

-- Iterate through the ‘list’ table and insert entires into the columns of the airport table in the database
for i=1, #list do
    assert (con:execute(string.format([[
        INSERT INTO test_db.airport(Code, Name, City)
        VALUES ('%s', '%s', '%s')]], list[i].Code, list[i].Name, list[i].City)
    ))
end

--Close connection and environment
con:close()
env:close()

  • Click Ok to close the script editor then Create to Create the ActionItem in the I/O Model tree

  • The ActionItem should have a green light beside it in the I/O Model indicating that the script ran without any problems. If the script has not run, there will be an error message displayed in the faceplate of the item.

This script inserts data into the airport table by iterating through a table which contains the data. The steps are similar to the CreateTable script that we used in the last example:

  1. Connect to database: Same as the first example

  2. Enter values into a table called ‘list’: Filling the table with appropriate data

  3. Insert values into airport table using SQL command: A ‘for’ loop iterates through the table ‘list’ and submits a SQL command to enter the data (Using the INSERT INTO SQL command). In this example we use Lua’s string.format function to submit the SQL command and input the list[i] values into the appropriate columns in the database airport table.

  4. Close the connection and database environment: Same as in the CreateTable example

Updating the MySQL Database

In this example we will update a record in the database using Lua. As you may have noticed there was a mistake in one of the written records in the last example that will be corrected with this Update script.

  1. Right Click on the Core object in the I/O Model tree to open the context menu and select Admin → New → Data Processing → ActionItem.

  2. In the Create Object wizard, provide a name for the Action Item. In this example we will be using a Lua script to update a record table in the database so we can name the ActionItem UpdateTable.

  3. Click on “…” next to Lua Script Body and insert the following code, again exchanging out “password” for the “root” account password you entered during the MySQL setup:</font>

-- Create a connection to the database using luasql module
local driver = require"luasql.odbc" --request driver
local env = driver.odbc()    --create environment
local con = env:connect("testMySQL", "root", "password") --connect to MySQL database and schema

-- Submit SQL command to database
assert(con:execute("UPDATE airport SET Name='OHare International' WHERE Code='ORD'")

-- Close connection and environment
con:close()
env:close()

Click Ok to close the script editor then Create to Create the ActionItem in the I/O Model tree

The ActionItem should have a green light beside it in the I/O Model indicating that the script ran without any problems. If the script has not run, there will be an error message displayed in the faceplate of the item.

This script updates the entry for Chicago airport, correcting the name of the airport.

  1. Connect to database: Same as the previous examples

  2. SQL command to MySQL: A string of the SQL command is submitted using the UPDATE, SET and WHERE SQL commands to only change the Name entry for the Chicago airport record. Note MySQL only allows the use of the UPDATE command for tables containing a column set to be primary key. The primary key was set to the Code column in the Create table example, therefore, we are able to use the UPDATE command

  3. Close the connection and database environment: Same as in the previous examples

Reading from the MySQL Database

In this example we will read from the database and retrieve a record from the airport table.

  • Right Click on the Core object in the I/O Model tree to open the context menu and select Admin → New → Data Processing → ActionItem.

  • In the Create Object wizard, provide a name for the Action Item. In this example we will be using a Lua script to read and retrieve a record from the table in the database so we can name the ActionItem ReadTable.

  • Click on “…” next to Lua Script Body and insert the following code, again exchanging out password for the root account password you entered during the MySQL setup:

--Create a connection to the database using luasql module
local driver = require"luasql.odbc"  --Call odbc driver from luasql module
local env = driver.odbc()   --Create MySQL database environment
local con = env:connect("testMySQL", "root", "password") --Connect with "root" account credentials
--Submit SQL command. Use the fetch function to enter results into a table
cur = assert(con:execute("SELECT Code, Name, City FROM airport Where Code = 'ORD'"))
row = cur:fetch ({}, "n")
-- close everything
cur:close()
con:close()
env:close()
--return the retrieved data
return row

This script retrieves the entry for Chicago airport and returns the row.

  1. Connect to database: Same as the previous examples

  2. SQL command to MySQL: A string of the SQL command is submitted using the SELECT and WHERE SQL commands to request only the Chicago airport record. The fetch function retrieves the results of the query

  3. Close the connection and database environment: Same as in the previous examples

  4. Return the retrieved row in the faceplate of the ActionItem

Deleting records and Dropping tables from MySQL Database

In this final example we will delete records from the database and also demonstrate how to remove entire tables from the database.

  • Right Click on the Core object in the I/O Model tree to open the context menu and select Admin → New → Data Processing → ActionItem

  • In the Create Object wizard, provide a name for the Action Item. In this example we will be using a Lua script to delete a record from the table in the database so we can name the ActionItem DeleteODBCRecord.

  • Click on “…” next to Lua Script Body and insert the following code, again exchanging out “password” for the “root” account password you entered during the MySQL setup:

-- Set up the ODBC connection and environment
local driver = require"luasql.odbc"
local env = driver.odbc()
local con = env:connect("testMySQL", "root", "password")
-- Submit SQL command
assert (con:execute"DELETE FROM test_db.airport WHERE code = 'DEN'")
-- Close connection and environment
con:close()
env:close()

The following example demonstrates how to remove the entire airport table from the database.

local driver = require("luasql.odbc")(1)
local env = driver.odbc()
local con = env:connect("testMySQL", "USERNAME", "PASSWORD")(2)

assert(con:execute("DROP TABLE airport"))(3)

con:close()(4)
env:close()(4)
1 Import the luasql driver.
2 Connect to the database, the given user should have the DROP privilege on that database.
3 A string of the SQL command is submitted: using the DROP SQL command the whole airport table is deleted.
4 Close the connection and database environment.