Connect to SQL MQ

Connects to a database to use a table as a message queue provider.

Command availability: IBM RPA SaaS and IBM RPA on premises

Description

This command uses a database connection to use a table as a message queue provider. There are additional settings that allow to use query filters and use specific column IDs. You can configure the Queue Provider in the IBM RPA Control Center by enabling the Storaged Configurations parameter, or manually configure a database by leaving it disabled.

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.

connectSqlMQ --queueprovider(String) --connection(DbConnection) --objectname(String) --columnid(String) [--filter(String)] [--orderby(String)] [--fromconfiguration(Boolean)] [--timeout(TimeSpan)] (Boolean)=success (QueueConnection)=value

System Queue provider

SQL MQ cannot be used as a default queue provider and cannot be used as queue for workflow processes as well.

Dependencies

Input parameters

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
Queue Provider queueprovider Required when the Storaged Configurations parameter is enabled Text Connects to a Queue Provider configured in the IBM RPA Control Center. This option only works if the Queue Provider is already registered in the IBM RPA Control Center.
Connection connection Required when the Storaged Configurations parameter is not enabled Database Connection Database connection variable.
Database Name objectname Required when the Storaged Configurations parameter is not enabled Text Name of the database that will be used as a queue. The database must contain table records to use as queue items.
ID Column columnid Required when the Storaged Configurations parameter is not enabled Text Name of the table column that will be used as the ID. This column will be used to identify the items in the queue.

Note:By default, this command uses the database column status to register the status of each item. Do not use status as a column name for anything else.
Query Filter filter Optional Text Filter items in the queue. You can use expressions equivalent to a SQL WHERE statement.
OrderBy Query orderby Optional Text Classify items in the queue. You can use expressions equivalent to a SQL ORDER BY statement. This option rearranges items in the queue according to the classification method chosen: ASC for ascending order or DESC for descending order.
Storaged Configurations fromconfiguration Optional Boolean When enabled, you can select a queue provider previously configured in the IBM RPA Control Center.
Timeout timeout Optional Time Span Maximum waiting time for establishing a connection to the database.

If no value is defined, the default timeout of 5 seconds is used. It can also use the timeout value defined in the Set Timeout (setTimeout) command.

Output parameters

Designer mode label Script mode name Accepted variable types Description
Success success Boolean Returns True if it successfully establishes a connection with the database, or False otherwise.
Connection value Queue Connection Returns a variable with the connection to the queue provider.

Example

Example 1:
The following example creates and connects to a SQLite database with the Connect to SQLite (sqliteConnect) command and uses the Connect to SQL MQ command to use the database as a queue provider. Then it removes the first item from the queue.

defVar --name myDocs --type String
defVar --name clientsDatabase --type DbConnection
defVar --name queueConnection --type QueueConnection
defVar --name clientQueue --type MessageQueue
defVar --name dbExists --type Boolean
defVar --name dbFileName --type String
defVar --name success --type Boolean
// Gets the folder to save the database file
getSpecialFolder --folder "MyDocuments" myDocs=value
// Sets a variable to hold the path to the database file
setVar --name "${dbFileName}" --value "${myDocs}\\clients.db"
// Checks if a file with the same name already exists
ifFile --file "${dbFileName}" dbExists=value
// If the file does not exist, it creates a new SQLite database using SQL statements.
// Otherwise it connects to the existing database file.
if --left "${dbExists}" --operator "Is_True"
	sqliteConnect --connectionString "Data Source=${dbFileName};Version=3;" clientsDatabase=connection
else
	sqliteConnect --createNew  --sql "BEGIN TRANSACTION;\r\nCREATE TABLE IF NOT EXISTS \"Clients\" (\r\n \"name\" TEXT,\r\n \"ID\" INTEGER UNIQUE,\r\n \"car\" TEXT,\r\n \"price\" NUMERIC,\r\n \"status\" TEXT,\r\n PRIMARY KEY(\"ID\")\r\n);\r\nINSERT INTO \"Clients\" (\"name\",\"ID\",\"car\",\"price\",\"status\") VALUES (\'John Doe\',1,\'Field March\',27000,NULL);\r\nINSERT INTO \"Clients\" (\"name\",\"ID\",\"car\",\"price\",\"status\") VALUES (\'James Smith\',2,\'Ponsch 9000\',99000,NULL);\r\nINSERT INTO \"Clients\" (\"name\",\"ID\",\"car\",\"price\",\"status\") VALUES (\'Edna Campbell\',3,\'Tesseract 3300\',21700,NULL);\r\nINSERT INTO \"Clients\" (\"name\",\"ID\",\"car\",\"price\",\"status\") VALUES (\'Steve Bischoff\',4,\'Camarade 3214\',23800,NULL);\r\nINSERT INTO \"Clients\" (\"name\",\"ID\",\"car\",\"price\",\"status\") VALUES (\'Debra Robinson\',5,\'Hummingbird 2021\',24970,NULL);\r\nINSERT INTO \"Clients\" (\"name\",\"ID\",\"car\",\"price\",\"status\") VALUES (\'Lynn Hayes\',6,\'F3 116\',30100,NULL);\r\nCOMMIT;" --path "${dbFileName}" clientsDatabase=connection
endIf
// Connects to the SQLite database using it as a queue provider
connectSqlMQ --connection ${clientsDatabase} --objectname Clients --columnid ID queueConnection=value
// Gets the queue from the database. The queue's name corresponds to the database table name
getQueue --connection ${queueConnection} --name Clients clientQueue=value
--type "Info"
// Dequeues the item from the queue
dequeue --collection "${clientQueue}" success=success
logMessage --message "Item dequeued: ${success}" --type "Info"

Example 2:
The following example is similar to the first one, but it gets two different queues from the database connection and rearranges the queue using the columns name and modelname in ascending order.

defVar --name myDocs --type String
defVar --name clientsDatabase --type DbConnection
defVar --name queueConnection --type QueueConnection
defVar --name clientQueue --type MessageQueue
defVar --name dbExists --type Boolean
defVar --name dbFileName --type String
defVar --name success --type Boolean
defVar --name modelsqueue --type MessageQueue
defVar --name modelsConnection --type QueueConnection
// Gets the folder to save the database file
getSpecialFolder --folder "MyDocuments" myDocs=value
// Sets a variable to hold the path to the database file
setVar --name "${dbFileName}" --value "${myDocs}\\models.db"
// Checks if a file with the same name already exists
ifFile --file "${dbFileName}" dbExists=value
// If the file does not exist, it creates a new SQLite database using SQL statements.
// Otherwise it connects to the existing database file.
if --left "${dbExists}" --operator "Is_True"
	sqliteConnect --connectionString "Data Source=${dbFileName};Version=3;" clientsDatabase=connection
else
	sqliteConnect --createNew  --sql "BEGIN TRANSACTION;\r\nCREATE TABLE IF NOT EXISTS \"Models\" (\r\n \"id\" INTEGER UNIQUE,\r\n \"modelname\" TEXT,\r\n \"price\" NUMERIC,\r\n \"status\" TEXT,\r\n PRIMARY KEY(\"id\")\r\n);\r\nCREATE TABLE IF NOT EXISTS \"Clients\" (\r\n \"name\" TEXT,\r\n \"ID\" INTEGER UNIQUE,\r\n \"car\" TEXT,\r\n \"status\" TEXT,\r\n PRIMARY KEY(\"ID\")\r\n);\r\nINSERT INTO \"Models\" (\"id\",\"modelname\",\"price\",\"status\") VALUES (1,\'Hummingbird 2021\',24970,NULL);\r\nINSERT INTO \"Models\" (\"id\",\"modelname\",\"price\",\"status\") VALUES (2,\'Ponsch 9000\',99000,NULL);\r\nINSERT INTO \"Models\" (\"id\",\"modelname\",\"price\",\"status\") VALUES (3,\'Tesseract 3300\',21700,NULL);\r\nINSERT INTO \"Models\" (\"id\",\"modelname\",\"price\",\"status\") VALUES (4,\'Camarade 3214\',23800,NULL);\r\nINSERT INTO \"Models\" (\"id\",\"modelname\",\"price\",\"status\") VALUES (5,\'F3 116\',30100,NULL);\r\nINSERT INTO \"Models\" (\"id\",\"modelname\",\"price\",\"status\") VALUES (6,\'Field March\',27000,NULL);\r\nINSERT INTO \"Clients\" (\"name\",\"ID\",\"car\",\"status\") VALUES (\'John Doe\',1,\'Field March\',NULL);\r\nINSERT INTO \"Clients\" (\"name\",\"ID\",\"car\",\"status\") VALUES (\'James Smith\',2,\'Ponsch 9000\',NULL);\r\nINSERT INTO \"Clients\" (\"name\",\"ID\",\"car\",\"status\") VALUES (\'Edna Campbell\',3,\'Tesseract 3300\',NULL);\r\nINSERT INTO \"Clients\" (\"name\",\"ID\",\"car\",\"status\") VALUES (\'Steve Bischoff\',4,\'Camarade 3214\',NULL);\r\nINSERT INTO \"Clients\" (\"name\",\"ID\",\"car\",\"status\") VALUES (\'Debra Robinson\',5,\'Hummingbird 2021\',NULL);\r\nINSERT INTO \"Clients\" (\"name\",\"ID\",\"car\",\"status\") VALUES (\'Lynn Hayes\',6,\'F3 116\',NULL);\r\nCOMMIT;\r\n" --path "${dbFileName}" clientsDatabase=connection
endIf
// Connects to the SQLite database using it as a queue provider
connectSqlMQ --connection ${clientsDatabase} --objectname Clients --columnid ID --orderby "name asc" queueConnection=value
connectSqlMQ --connection ${clientsDatabase} --objectname Models --columnid id --orderby "modelname asc" modelsConnection=value
// Gets the Clients queue from the database. The queue's name corresponds to the database table name
getQueue --connection ${queueConnection} --name Clients clientQueue=value
// Dequeues the first item from the Clients queue
dequeue --collection "${clientQueue}" success=success
logMessage --message "Item dequeued: ${success}" --type "Info"
// Gets the Models queue from the database.
getQueue --connection ${modelsConnection} --name Models modelsqueue=value
// Dequeues the first item from the Models queue
dequeue --collection "${modelsqueue}" success=success
logMessage --message "Item dequeued: ${success}" --type "Info"

Limitations

  • The command does not create a new queue. It uses the items in a database table as queue messages.
  • The table must contain a column named status and it is used to record if the item was Dequeued. If the table contains other records in status, they will be deleted. Use a different column name to record other status data.