Execute SQL Instruction

Runs an SQL instruction and returns information regarding the command's execution.

Important:IBM RPA 18.1.0.7 deprecated this command, and starting from IBM RPA 23.0.3, it is removed from the product. For more information, see Removed.

Command availability: IBM RPA SaaS and IBM RPA on premises

Description

To connect to a database, use the following commands:

To run an SQL statement, use the Run SQL Command.

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.

executeSql --connection(String) --command(String) --results(ExecuteSqlResults) (Boolean)=isnull (String)=value (DataTable)=table (Numeric)=rows (Numeric)=columns

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 string connection Required Text Database string connection. It supports SQL Server connections only.
Instruction command Required Text The SQL instruction.
Type of result results Required ExecuteSqlResults The type of result to return.

See the results parameter options.

results parameter options

The following table displays the options available for the results input parameter. The table shows the options available when working in Script mode and the equivalent label in the Designer mode.

Designer mode label Script mode name Description
None None Return value in the Null output parameter.
Scalar Scalar Return value in the Result output parameter.
Data Table DataTable Return value in the Data Table, Rows, and Columns output parameters.

Output parameter

Designer mode label Script mode name Accepted variable types Description
Null isnull Boolean Returns Null if the database is empty.
Result value Text Returns a value.
Data Table table Data Table Returns the data in a data table.
Rows rows Number Returns the number of affected rows in the data table, if any.
Columns columns Number Returns the number of affected columns in the data table, if any.

Example

This command inserts a table in the Database, returning the table and the number of affected rows and columns.

defVar --name connectionString --type String --value "Data Source=127.0.0.1;Initial Catalog=test;User ID=root; Password=\"\";\r\n"
defVar --name sqlCommand --type String --value "INSERT INTO book (price,title,author,publishingcompany,pagesnumber,language) \n VALUES (59.99,\'Hamlet\',\'William Shakespeare\',\'New Horizon\',300,\'EN\');"
defVar --name returnedTable --type DataTable
defVar --name tableRows --type Numeric
defVar --name tableColumns --type Numeric
executeSql --connection "${connectionString}" --command "${sqlCommand}" --results "DataTable" returnedTable=table tableRows=rows tableColumns=columns