Connecting to MySQL in the system

Using AspenTech Inmation OT DataFabric’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 AspenTech Inmation OT DataFabric 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. Along with the basic Lua language, the following module is included to access DBMSs:

Luasql - More details can be found here and here

In order to access the MySQL database we will create Action Items to house Lua scripts. To do this you must first start WebStudio and login with administrative rights.

Creating an Action Item

To create the Action Item for the Lua script that generates a table in the test schema:

Right-click on the Node object in the I/O Model tree to open the context menu and select Admin New to open the Create Object wizard.
In the Create Object wizard, select Data Processing > Action Item from the left-hand panel.
In the Common property compound, provide an Object 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 Action Item is named CreateTable.
Click on next to Lua Script Body and insert the code from the adequate example below. Lua script examples are available for:
In the script editor, replace the <password> placeholder with the root account password you entered during the MySQL setup.
Click Ok to close the script editor then Create to create the Action Item object in the I/O Model tree.

When the Action Item is created, its script is executed immediately. If the script ran without any problems, the Action Item is in green state. Otherwise the state is red, and an error message is displayed in the faceplate of the Action Item.

For cases where you want to execute a Lua script only once and don’t want to create an object in the I/O Model tree, the Console Tab can be used.

Creating a Table in the MySQL Database

This script creates a new table:

local driver = require"luasql.odbc" --request driver(1)

-- create connection to database(2)
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(3)
assert(con:execute([[
    CREATE TABLE Airport (
        Code varchar(10) NOT NULL PRIMARY KEY,
        Name varchar(50),
        City varchar(10)
    );
]]))

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

Details

1	Import the luasql driver.
2	Create the database connection: First the database environment is created and then a connection to this environment is established, using the 'testMySQL' DSN (that we set up in the previous section) and the root account. Make sure you enter the password that you entered for the root account during the MySQL setup!
3	The SQL command is sent to the MySQL server as a string using the con:execute() function. In this case the command asks the MySQL server to create a table called 'airport', containing columns named 'Code', 'Name' and 'City' - with the 'Code' column as primary key. This table should be visible in the SCHEMAS sidebar section in MySQL Workbench.
4	Close the connection and database environment: This is necessary to allow other connections to the database in the future.

< Back to Creating an Action Item

Writing to the MySQL Database

This script inserts data into the airport table by iterating over a table containing the data:

local driver = require"luasql.odbc" --request driver(1)

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

-- Create a table named 'list' which contains the information for the Airports (3)
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 over the 'list' table and insert entires into the columns of the airport table in the database(4)
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(5)
con:close()
env:close()

Details

The steps are similar to the CreateTable script that we used in the last example: <1> Import the luasql driver. <2> Connect to the MySQL server. <3> Enter values into a table called 'list', filling the table with appropriate data. <4> Insert values into the airport table using SQL command:
A for loop iterates over 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 insert the list[i] values into the appropriate columns in the database airport table. <5> Close the connection and database environment

< Back to Creating an Action Item

Updating the MySQL Database

As you may have noticed there was a mistake in one of the written records in the last example. This script updates the entry for Chicago airport, correcting the name of the airport:

local driver = require"luasql.odbc" --request driver(1)

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

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

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

Details

1	Import the luasql driver.
2	Connect to the MySQL server.
3	A string of the SQL command is submitted using the UPDATE, SET and WHERE SQL commands to change the Name entry for the Chicago airport record. Note that MySQL only allows the use of the UPDATE command for tables that have a column designated as the primary key. In the Create Table example we specified the Code column as the primary key so the UPDATE command can be used here.
4	Close the connection and database environment: Same as in the previous examples

< Back to Creating an Action Item

Reading from the MySQL Database

This script reads from the database and retrieves the record for Chicago airport which is then returned to the ActionItem:

local driver = require"luasql.odbc"  --Call odbc driver from luasql module(1)

--Create a connection to the database using luasql module(2)
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(3)
cur = assert(con:execute("SELECT Code, Name, City FROM airport Where Code = 'ORD'"))
row = cur:fetch({}, "n")

-- close everything(4)
cur:close()
con:close()
env:close()

--return the retrieved data(5)
return row

Details

1	Import the luasql driver.
2	Connect to the MySQL server.
3	The SQL command is submitted, using the SELECT and WHERE SQL commands to request only the Chicago airport record. The fetch() function then retrieves the results of the query.
4	Close the connection and database environment: Same as in the previous examples.
5	Return the retrieved row to the ActionItem where it is displayed in the faceplate.

< Back to Creating an Action Item

Deleting Records from the MySQL Database

This script deletes a record from the database:

local driver = require"luasql.odbc"(1)

-- Set up the ODBC connection and environment(2)
local env = driver.odbc()
local con = env:connect("testMySQL", "root", "password")

-- Submit SQL command(3)
assert (con:execute"DELETE FROM test_db.airport WHERE code = 'DEN'")

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

Details

1	Import the luasql driver.
2	Connect to the MySQL server. The user needs to have the 'DROP privilege' on that database.
3	The SQL command is submitted, using the DELETE FROM and WHERE SQL commands to remove the record for the Denver airport.
4	Close the connection and database environment.

< Back to Creating an Action Item

Dropping Tables from the MySQL Database

The following script removes 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()

Details

1	Import the luasql driver.
2	Connect to the MySQL server. The user needs to have the 'DROP privilege' on that database.
3	The SQL command is submitted, using the DROP TABLE SQL command to delete the whole airport table.
4	Close the connection and database environment.

< Back to Creating an Action Item