Execute Script

Example 1

Suppose that you own a coffee shop, and you want to track orders by clients and items. The database must have four tables:

• Clients: This table stores information about your clients, such as their name and email address.
• Orders: This table stores information about your orders, such as the order ID and the client ID.
• Products: This table stores information about your products, such as the product ID, the product's name, and the price.
• Order items: This table stores information about items per order, such as the order ID and the product ID.

You are given the task to get the name and the total purchase amount of the client that spent the highest amount in your shop.

To create, populate and query this database, you can separate the script's logic into different files according to your needs, and use the Execute Script command to call each script from a main script.

To run this example, create four separate WAL script files, and place them in your Desktop:

connectToDb.wal:

defVar --name databasePath --type String --parameter  --required
defVar --name sqliteConnection --type DbConnection --output
defVar --name connectionSuccess --type Boolean
defVar --name pathExists --type Boolean
defVar --name path --type String
ifFile --file "${databasePath}" pathExists=value
if --left "${pathExists}" --operator "Is_True"
	sqliteConnect --connectionString "Data Source=\"${databasePath}\";Version=3;UseUTF16Encoding=True;" sqliteConnection=connection
else
	sqliteConnect --createNew  --path "${databasePath}" sqliteConnection=connection connectionSuccess=success
	sqlExecute --connection ${sqliteConnection} --statement "CREATE TABLE IF NOT EXISTS clients (client_id INTEGER PRIMARY KEY AUTOINCREMENT,first_name TEXT NOT NULL,last_name TEXT NOT NULL,email TEXT NOT NULL);"
	sqlExecute --connection ${sqliteConnection} --statement "CREATE TABLE IF NOT EXISTS products (product_id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,price REAL NOT NULL);"
	sqlExecute --connection ${sqliteConnection} --statement "CREATE TABLE IF NOT EXISTS orders (order_id INTEGER PRIMARY KEY AUTOINCREMENT,client_id INTEGER,FOREIGN KEY(client_id) REFERENCES clients(client_id));"
	sqlExecute --connection ${sqliteConnection} --statement "CREATE TABLE IF NOT EXISTS order_items (order_item_id INTEGER PRIMARY KEY AUTOINCREMENT,order_id INTEGER,product_id INTEGER NOT NULL,quantity INTEGER NOT NULL,FOREIGN KEY(order_id) REFERENCES orders(order_id),FOREIGN KEY(product_id) REFERENCES products(product_id));"
endIf

checkDb.wal:

defVar --name databaseConnection --type DbConnection --parameter  --required
defVar --name result --type Numeric --output
sqlExecuteScalar --connection ${databaseConnection} --statement "SELECT COUNT(*) FROM orders;" result=value

insertData.wal:

defVar --name databaseConnection --type DbConnection --parameter  --required
sqlExecute --connection ${databaseConnection} --statement "INSERT INTO clients (first_name, last_name, email)\r\nVALUES\r\n(\'John\', \'Doe\', \'john.doe@example.com\'),\r\n(\'Jane\', \'Doe\', \'jane.doe@example.com\'),\r\n(\'Peter\', \'Parker\', \'peter.parker@example.com\'),\r\n(\'Bruce\', \'Wayne\', \'bruce.wayne@example.com\'),\r\n(\'Clark\', \'Kent\', \'clark.kent@example.com\');"
sqlExecute --connection ${databaseConnection} --statement "INSERT INTO products (name, price)\r\nVALUES\r\n(\'Espresso\', 3.99),\r\n(\'Latte\', 4.99),\r\n(\'Capuccino\', 5.99),\r\n(\'Macchiato\', 4.50),\r\n(\'Americano\', 3.50),\r\n(\'Iced coffee\', 4.50),\r\n(\'Cold brew coffee\', 5.00),\r\n(\'Frappuccino\', 6.00),\r\n(\'Mocha\', 5.50),\r\n(\'White chocolate mocha\', 6.00);"
sqlExecute --connection ${databaseConnection} --statement "INSERT INTO orders (client_id)\r\nVALUES\r\n(1),\r\n(2),\r\n(3),\r\n(4),\r\n(5);"
sqlExecute --connection ${databaseConnection} --statement "INSERT INTO order_items (order_id, product_id, quantity)\r\nVALUES\r\n(1, 1, 2),\r\n(1, 2, 1),\r\n(2, 3, 3),\r\n(2, 4, 1),\r\n(3, 5, 2),\r\n(3, 6, 1),\r\n(4, 7, 3),\r\n(4, 8, 1),\r\n(5, 9, 2),\r\n(5, 10, 1);"

queryDb.wal:

defVar --name databaseConnection --type DbConnection --parameter  --required
defVar --name dataTable --type DataTable --output
sqlExecuteReader --connection ${databaseConnection} --statement "SELECT first_name, last_name, SUM(quantity * products.price) AS total_spent\r\nFROM clients\r\nJOIN orders ON clients.client_id = orders.client_id\r\nJOIN order_items ON orders.order_id = order_items.order_id\r\nJOIN products ON order_items.product_id = products.product_id\r\nWHERE products.price IS NOT NULL\r\nGROUP BY clients.client_id\r\nORDER BY total_spent DESC\r\nLIMIT 1;" dataTable=value

To use these scripts, create the following script:

defVar --name databasePath --type String
defVar --name path --type String
defVar --name sqliteConnection --type DbConnection
defVar --name result --type Numeric
defVar --name dataTable --type DataTable
defVar --name firstName --type String
defVar --name lastName --type String
defVar --name totalSpent --type Numeric
getSpecialFolder --folder "Desktop" path=value
setVar --name "${databasePath}" --value "${path}\\clients.db"
// Connects to database. Creates the database file if it doesn't exist.
executeScript --name "${path}\\connectToDb.wal" --parameters "{\"databasePath\":\"${databasePath}\"}" --output "{\"sqliteConnection\":\"${sqliteConnection}\"}"
// Checks if the database is populated.
executeScript --name "${path}\\checkDb.wal" --parameters "{\"databaseConnection\":\"${sqliteConnection}\"}" --output "{\"result\":\"${result}\"}"
// If the database is not populated, then it inserts data into the database. This avoids data duplication.
if --left "${result}" --operator "Greater_Than" --right 0 --negate
	//Inserts data into the database. It only does that if the database has no data.
	executeScript --name "${path}\\insertData.wal" --parameters "{\"databaseConnection\":\"${sqliteConnection}\"}"
endIf
// Gets the client that spent the highest amount in your coffee shop, with the amount spent.
executeScript --name "${path}\\queryDb.wal" --parameters "{\"databaseConnection\":\"${sqliteConnection}\"}" --output "{\"dataTable\":\"${dataTable}\"}"
mapTableRow --dataTable ${dataTable} --row 1 --mappings "[{\"Name\":\"first_name\",\"Number\":\"1\",\"Output\":\"${firstName}\"},{\"Name\":\"last_name\",\"Number\":\"2\",\"Output\":\"${lastName}\"},{\"Name\":\"total_spent\",\"Number\":\"3\",\"Output\":\"${totalSpent}\"}]"
// Prints the result of the query.
logMessage --message "${firstName} ${lastName} has spent a total of US$ ${totalSpent} in your coffee shop." --type "Info"
// The output is: "Jane Doe has spent a total of US$ 22.47 in your coffee shop."

This main script calls all of the other scripts to accomplish the following tasks:

1. Connect to the database. If the database does not exist, it creates the clients.db file in the Desktop. The database connection variable is returned by the connectToDb.wal script, and it is used by other scripts to connect to the same database.
2. Checks if the database is populated.
3. If the database is not populated, it inserts data into the database.
4. Queries the database to get the client that spent the highest amount in the shop. It returns the name of the client and the total amount spent.
5. Displays the name of the client with the total amount spent.

Example 2

If an error occurs during runtime, you can capture the error messages with the Handle Error parameter. If this parameter is enabled, the errors that are captured during runtime do not affect the main script. The errors are essentially ignored, and the script proceeds normally until it finishes. The captured error messages can be logged and analyzed later.

Suppose that your coffee shop now has a new product called Espresso Latte and you want to track the new product's sales. To do that, you have to make small changes to your existing scripts. Create the espressoLatteQuery.wal file in your Desktop:

espressoLatteQuery.wal:

defVar --name dbConnection --type DbConnection --parameter
defVar --name result --type String --output
sqlExecuteScalar --connection ${dbConnection} --statement "SELECT SUM(order_items.quantity) FROM AS espresso_latte_quantity FROM order_items JOIN products ON order_items.product_id = products.product_id WHERE products.name = \"Espresso Latte\";" result=value

And edit the executeScript.wal file:

executeScript.wal:

defVar --name databasePath --type String
defVar --name path --type String
defVar --name sqliteConnection --type DbConnection
defVar --name result --type Numeric
defVar --name resultLatte --type String
getSpecialFolder --folder "Desktop" path=value
setVar --name "${databasePath}" --value "${path}\\clients.db"
// Connects to database. Creates the database file if it doesn't exist.
executeScript --name "${path}\\connectToDb.wal" --parameters "{\"databasePath\":\"${databasePath}\"}" --output "{\"sqliteConnection\":\"${sqliteConnection}\"}"
// Checks if the database is populated.
executeScript --name "${path}\\checkDb.wal" --parameters "{\"databaseConnection\":\"${sqliteConnection}\"}" --output "{\"result\":\"${result}\"}"
// If the database is not populated, then it inserts data into the database. This avoids data duplication.
if --left "${result}" --operator "Greater_Than" --right 0 --negate
	//Inserts data into the database. It only does that if the database has no data.
	executeScript --name "${path}\\insertData.wal" --parameters "{\"databaseConnection\":\"${sqliteConnection}\"}"
endIf
// Gets the client that spent the highest amount in your coffee shop, with the amount spent.
executeScript --name "${path}\\espressoLatteQuery.wal" --parameters "{\"databaseConnection\":\"${sqliteConnection}\"}" --output "{\"result\":\"${resultLatte}\"}"
// Prints the result of the query.
logMessage --message "A total of ${resultLatte} Espresso Lattes have been sold in your coffe shop." --type "Info"

However, there's no data related to the new product in the database, and if you try to run the executeScript.wal file it does not return anything, and you only get the message: "A total of Espresso Lattes have been sold in your coffee shop". The script does not fail, and in more complicated situations, this can become a problem that is difficult to find a solution for without an explicit error message.

In this example, to demonstrate the power of error handling and how you can capture errors and deal with them with IBM RPA, you can use the Throw Exception (failTest) command to cause an error in the "espressoLatteQuery.wal", and make it explicit that there was a problem with the query:

defVar --name dbConnection --type DbConnection --parameter
defVar --name result --type String --output
defVar --name errorMessage --type String --value "No Espresso Latte data found!"
defVar --name hasResult --type Boolean
sqlExecuteScalar --connection ${dbConnection} --statement "SELECT SUM(order_items.quantity) FROM AS espresso_latte_quantity FROM order_items JOIN products ON order_items.product_id = products.product_id WHERE products.name = \"Espresso Latte\";" result=value hasResult=hasresult
if --left "${hasResult}" --operator "Is_True" --negate
	failTest --message "${errorMessage}"
endIf

If you try to run the executeScript.wal now, you get an error message that stops the bot from completing the task. You have to enable the Handle error parameter to be able to capture the error messages:

defVar --name databasePath --type String
defVar --name path --type String
defVar --name sqliteConnection --type DbConnection
defVar --name result --type Numeric
defVar --name success --type Boolean
defVar --name errorMessage --type String
defVar --name resultLatte --type String
defVar --name errorLineNumber --type Numeric
defVar --name errorRoutine --type String
defVar --name error --type Error
getSpecialFolder --folder "Desktop" path=value
setVar --name "${databasePath}" --value "${path}\\clients.db"
// Connects to database. Creates the database file if it doesn't exist.
executeScript --name "${path}\\connectToDb.wal" --parameters "{\"databasePath\":\"${databasePath}\"}" --output "{\"sqliteConnection\":\"${sqliteConnection}\"}"
// Checks if the database is populated.
executeScript --name "${path}\\checkDb.wal" --parameters "{\"databaseConnection\":\"${sqliteConnection}\"}" --output "{\"result\":\"${result}\"}"
// If the database is not populated, then it inserts data into the database. This avoids data duplication.
if --left "${result}" --operator "Greater_Than" --right 0 --negate
	//Inserts data into the database. It only does that if the database has no data.
	executeScript --name "${path}\\insertData.wal" --parameters "{\"databaseConnection\":\"${sqliteConnection}\"}"
endIf
// Gets the client that spent the highest amount in your coffee shop, with the amount spent.
executeScript --handleError  --name "${path}\\espressoLatteQuery.wal" --parameters "{\"databaseConnection\":\"${sqliteConnection}\"}" --output "{\"result\":\"${resultLatte}\"}" success=value errorMessage=errormessage errorLineNumber=linenumber errorRoutine=errorsubname error=error
// Prints the result of the query.
if --left "${success}" --operator "Is_True" --negate
	setVar --name "${resultLatte}" --value 0
endIf
logMessage --message "A total of ${resultLatte} Espresso Lattes have been sold in your coffe shop." --type "Info"

The script no longer fails, and continues running even after an error occurred in one of the called scripts. You can now check the error messages in Debug mode by adding a breakpoint in the last line in IBM RPA Studio, or, if your bot is published in the IBM RPA Control Center, you can check the error variables in the bot's details. In this example, the script ignores the error messages and checks if the "espressoLatteQuery.wal" script ran successfully. If it didn't, it assigns the value "0" to the resultLatte variable to avoid bad outputs.