9.0

Edit

Connect to MS SQL

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.

MS SQL

Let's demonstrate the concept on an example of MS SQL database.

To enable connectivity to an external MS SQL databases:

  1. Download a suitable JDBC driver.

  2. Add the JDBC driver to WorkFusion Control Tower. Put the JAR file with the MS SQL driver into the following directory:

    $INSTALL_DIR/apps/webapps/tomcat/lib

    important

    The changes performed on the server require configuration management. Addition of custom JDBC drivers to Control Tower is considered a customization of the product. The customizations require separate handling (promotion of the configuration change to higher and DR environment, re-applying the change after product upgrades, and so on).

  3. Implement the logic. Now, you can connect to MS SQL databases from Bot Tasks to execute queries against.

Using 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>

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;
}
← PreviousNext →