Connect to MS SQL · WorkFusion Documentation

JDBC API connection to external database

WorkFusion supports integration with external databases for fetching the data, executing stored procedures or executing other database queries.

Connecting to an external database is done using JDBC API. There are JDBC driver implementations available for most of the modern database solutions. Microsoft SQL JBDC driver is now part of the Control Tower configuration.

To enable connectivity to an external MS SQL databases, connect to MS SQL databases from Bot Tasks to execute queries.

Out-of-the-box database plugin

<database connection="jdbc:sqlserver://hostname:6501;DatabaseName=database"
          jdbcclass="com.microsoft.sqlserver.jdbc.SQLServerDriver"
          username="user" password="securepassword">

    select first_name from actor
</database>

Script block

Or alternatively inside a script block:

import groovy.sql.Sql

url = 'jdbc:sqlserver://hostname:6501;DatabaseName=database'
user = 'username'
password = 'password'
driver = 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
sql = Sql.newInstance(url, user, password, driver);
rows =  sql.rows('{call dbo.GetDetail}');

dataList = new ArrayList();
for (int b=0; b < rows.size(); b++) {
    def nRow = rows.get(b);
    def inputData = rowAsMap(nRow);
    jsonValueMap = new com.google.gson.Gson().toJson(inputData);
    dataList << jsonValueMap;
}