Execute SQL Instruction
Runs an SQL instruction and returns information regarding the command's execution.
Command availability: IBM RPA SaaS and IBM RPA on premises
Description
To connect to a database, use the following commands:
- Connect to MySQL
- Connect to Oracle
- Connect to SQL Server
- Connect to SQLite
- Connect to PostgreSQL
- Connect to ODBC
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