Executing a SQL Query using JS Code

RPA Automation Studio Pro
1

Is there anyway within the Execute JS Code to make a SQL Connection and subsequently build and execute a SQL query?

I have tried testing something like this, but keep getting a module issue on the require statement for MSSQL? “The module ‘MSSQL’ is not whitelisted”

Any help in the right direction would be appreciated.
-----------------Code Example------------------------

let SQLCmd = “SELECT UID FROM [DATABASE] WHERE [FIELD] = ‘12345’”
var SQLService = require(‘MSSQL’);
var SQLConnection = SQLService.createConnection({
host: config[“SQLServer”],
user: config[“SQLUserName”],
password: config[“SQLPassword”],
database: config[“SQLDatabaseName”]
});

SQLConnection.connect( function(err){
if (err) throw err;
SQLConnection.query(SQLCmd, function (err,result,fields) {
if (err) throw err;
QueryResult = result;
})

}

)

2

Hi @trosevear,
you also can use activities “Connect to database” and “Execute query” from “Databases”.
Why do you need to execute SQL queries within Execute JS code?

3

Thanks d.motta for your response, I am aware we can use the Connect to database and Execute Query functions, this is in fact how I have achieved the goal. Unfortunately the requirement is to update a database with information from multiple spreadsheets, and there are a few 1000 records. When using the functions in a loop, the process is very slow. I was hoping to speed the process up by using a script to loop through the JSON object, check if the record exists and then do an insert or update accordingly. Hope this makes sense.

4

@trosevear At the moment, the functionality you mentioned is implemented by connecting to a database. But we will definitely report your idea for consideration of our colleagues from the product team. Thanks for the feedback!