Run SQL Command

Runs an SQL statement against a connection with a database and returns the number of rows affected.

Command availability: IBM RPA SaaS and IBM RPA on premises

Script syntax

IBM RPA's proprietary script language has a syntax similar to other programming languages. The script syntax defines the command's syntax in the script file. You can work with this syntax in IBM RPA Studio's Script mode.

sqlExecute --connection(DbConnection) --statement(String) [--timeout(TimeSpan)] (Numeric)=value

Dependencies

You must create a database connection with a valid connection string. Use the following commands according to the database management system (DBMS) you use:

Input parameter

The following table displays the list of input parameters available in this command. In the table, you can see the parameter name when working in IBM RPA Studio's Script mode and its Designer mode equivalent label.

Designer mode label	Script mode name	Required	Accepted variable types	Description
Connection	`connection`	`Required`	`Database Connection`	Database connection variable.
SQL Statement	`statement`	`Required`	`Text`	The SQL statement. See the `statement` parameter section for details.
Timeout	`timeout`	`Optional`	`Time Span`, `Number`, `Text`	The maximum time to wait to run the command. The default timeout is 5 seconds (`5000` or `00:00:05`). See the `timeout` parameter section for details.

The `statement` parameter

Write the statement according to the SQL syntax in the DBMS that you use. You might be able to run queries with the CREATE TABLE, DROP TABLE, INSERT, UPDATE, and DELETE statements.

In Oracle™ databases, don't close SQL statements with semi-colon (;) because it can raise exceptions. Also, you can run only one statement per query.

The `timeout` parameter

You can define the timeout by entering a String representation in milliseconds, or a Time span representation. For example:

String representation: 50000 (fifty seconds)
Time span representation: 2.05:20:10.001 (two days, five hours, twenty minutes, ten seconds, and one millisecond)

For these representations, IBM RPA Studio rounds them up to the nearest in seconds value. For example:

1001 milliseconds are 00:00:01.001 and rounds up to 00:00:02
60020 milliseconds are 00:01:00.020 and rounds up to 00:01:01

The following database systems set the timeout to unlimited if you set its value to zero, which means that the command waits indefinitely to run:

Databases connected through ODBC
Oracle™
SQL Server®
SQLite®

Some database systems use timeouts to estimate how long a query takes to run. For timeout limit exceptions, you can either set the timeout to zero or add greater timeouts.

Output parameter

Designer mode label	Script mode name	Accepted variable types	Description
Value	`value`	`Number`	Returns the number of rows affected.

Example

The following code example demonstrates how to run the CREATE TABLE, INSERT, and UPDATE queries in a database. Consider an empty database file called sample.db. In this example, except by the CREATE TABLE query, after you run the INSERT and UPDATE queries you receive the number of rows that are affected by these statements. Notice that you can also run these statements at once by this command.

defVar --name dbConnection --type DbConnection
defVar --name affectedRows --type Numeric
defVar --name databaseFile --type String
defVar --name pathMyDocuments --type String
// Gets the My Documents folder path.
getSpecialFolder --folder "MyDocuments" pathMyDocuments=value
// Assigns the 'sample.db' folder path to the 'databaseFile' variable.
setVar --name "${databaseFile}" --value "${pathMyDocuments}\\sample.db"
// Connects to the database management system (DBMS). You must provide a valid connection string to connect with the database before you run this example.
sqliteConnect --connectionString "Data Source=${databaseFile};Version=3;UseUTF16Encoding=True;" dbConnection=connection
// Runs the CREATE TABLE query.
sqlExecute --connection ${dbConnection} --statement "CREATE TABLE sample_table(\r\n   sample_column TEXT,\r\n   identifier INTEGER,\r\n PRIMARY KEY(\"identifier\" AUTOINCREMENT)\r\n);" --timeout "00:00:05.2000000"
// Runs the INSERT query.
sqlExecute --connection ${dbConnection} --statement "INSERT INTO sample_table (sample_column) VALUES (\'First text value\');\r\nINSERT INTO sample_table ( sample_column) VALUES (\'Second text value\');" --timeout "00:00:05.2000000" affectedRows=value
// Logs the number of rows affected by the INSERT query.
logMessage --message "Inserting value: ${affectedRows}" --type "Info"
// Runs the UPDATE query.
sqlExecute --connection ${dbConnection} --statement "UPDATE sample_table\r\nSET sample_column = \'Changing the first text value\'\r\nWHERE sample_column = \'First text value\';" --timeout "5.18:53:20" affectedRows=value
// Logs the number of rows affected by the UPDATE query.
logMessage --message "Updating value: ${affectedRows}" --type "Info"
// Ends the connection with the database.
sqlDisconnect --connection ${dbConnection}