Run SQL Scalar

Runs an SQL scalar function.

Command availability: IBM RPA SaaS and IBM RPA on premises


This command runs an SQL scalar function to get and return the value of one specific column on the database table. You need to specify the column name in the SQL query.

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.

sqlExecuteScalar --connection(DbConnection) --statement(String) [--timeout(TimeSpan)] (Scalar)=value (Boolean)=hasresult


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.
Statement statement Required Text The SQL statement.

Write the statement according to the SQL syntax in the DBMS that you use.
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 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 Primitives Returns the value of the specified column in the SQL statement.
Has Result hasresult Boolean Returns True if the SQL statement retrieves a value or False otherwise.


The following code example demonstrates how to run the upper() scalar function in a specific column and row in the database table. Consider an empty database file called sample.db. Before you get the data table values, the Run SQL Command command creates a table and inserts values to it.

defVar --name dbConnection --type DbConnection
defVar --name pathMyDocuments --type String
defVar --name databaseFile --type String
defVar --name scalarValue --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
// Creates one table and inserts sample values to it.
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);\r\n\r\nINSERT INTO sample_table (sample_column) VALUES (\'First text value\');\r\nINSERT INTO sample_table ( sample_column) VALUES (\'Second text value\');\r\n" --timeout "14:26:40"
// Runs the 'upper()' scalar function specifying the 'sample_column' as argument. It selects the values where the identifier is equal to 2.
sqlExecuteScalar --connection ${dbConnection} --statement "SELECT upper(sample_column)\r\nFROM sample_table\r\nWHERE identifier = 2;" --timeout "00:00:52" scalarValue=value
// Logs the value returned from the SQL scalar function used.
logMessage --message "Value: ${scalarValue}" --type "Info"
//  Ends the connection with the database.
sqlDisconnect --connection ${dbConnection}