SQL Procedure
Runs a stored procedure 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.
sqlProcedure --connection(DbConnection) --name(String) [--parameters(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. |
Name | name |
Required |
Text |
Name of a stored procedure. You must provide a procedure that exists in the database. |
Parameters | parameters |
Optional |
Text |
A parameter declared in the procedure. You must to fill the following fields: Parameters: The name of the parameter. You might need to add the parameter prefix @ at the beginning of the parameter name.Value: The value of a parameter. |
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 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 are00:00:01.001
and rounds up to00:00:02
60020
milliseconds are00:01:00.020
and rounds up to00: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 |
---|---|---|---|
Count | value |
Number |
Return the number of rows affected after running the stored procedure. |
Example
The following code example demonstrates how to run a stored procedure to retrieve a specific recording. Consider a database table called Books. This table stores books and its prices to sell. The stored procedure viewPrice
receive
as parameter the identifier of a book to be retrieved.
defVar --name dbConnection --type DbConnection
defVar --name affectedRows --type Numeric
defVar --name pathMyDocuments --type String
defVar --name databaseFile --type String
defVar --name serverAddress --type String
// Gets the My Documents folder path.
getSpecialFolder --folder "MyDocuments" pathMyDocuments=value
// Assigns the 'sample.mwb' folder path to the 'databaseFile' variable.
setVar --name "${databaseFile}" --value "${pathMyDocuments}\\sample.mwb"
// Assigns the 'localhost' value to the 'serverAddress' variable.
setVar --name "${serverAddress}" --value "localhost"
// Connects to the database management system (DBMS). You must provide a valid connection string to connect with the database before you run this example.
mysqlConnect --connectionstring "Server=${serverAddress};Database=${databaseFile};Uid=root;Pwd=\'\';" dbConnection=connection
// Creates a table called 'Books' and inserts data into it. It also creates a stored procedure to query a book price.
sqlExecute --connection ${dbConnection} --statement "CREATE TABLE books (\r\n identifier INT NOT NULL AUTO_INCREMENT,\r\n book VARCHAR(255),\r\n price DECIMAL(4,2),\r\n PRIMARY KEY (identifier)\r\n);\r\n\r\nINSERT INTO books (book, price) VALUES (\'First book\', 40.00);\r\nINSERT INTO books (book, price) VALUES (\'Second book\', 25.00);\r\n\r\nCREATE PROCEDURE viewPrice (\r\n IN idBook INT\r\n)\r\n\r\nBEGIN\r\n SELECT price \r\n FROM books\r\n WHERE identifier = @idBook;\r\nEND" --timeout "00:00:52"
// Runs the 'viewPrice' stored procedure passing the value 1 to the parameter.
sqlProcedure --connection ${dbConnection} --name viewPrice --parameters "@idBook=1" --timeout "00:00:10" affectedRows=value
// Logs the number of rows affected after running the procedure.
logMessage --message "Numer of affected rows: ${affectedRows}" --type "Info"
Limitations
If you use the MySQL database, you must add the parameter prefix @
at the beginning of the parameter name. Otherwise, parameter name without the prefix might not be recognized and the SQL instruction might not replace the SQL variable
with the mapped parameter values.