SQL Adapter Activity

The MDW JDBC SQL Adapter Activity provides the ability to query a database to select values and perform updates. Note: this is not compatible with Oracle JDBC.

Design Attributes

The basic design parameters for configuring a SQL Adapter Activity are illustrated in the screenshot below.

The JDBC DataSource example in the screenshot shows how to specify a simple JDBC URL as your DataSource. While this is acceptable for testing, in a real deployment you should use an actual DataSource that provides database connection pooling.

Queries are executed using JDBC Prepared Statements, and the dynamic parameters used in the query are designated using the question mark syntax. Values for bind variables are populated from a process variable with one of the following types:

java.lang.String
java.lang.Integer
java.lang.Long
java.util.Date
java.lang.String[]
java.lang.Integer[]
java.lang.Long[]
java.lang.Object (as long as its value is one of the supported types, or an Array of supported types)
null (no parameters)

If no parameter variable is set, this behaves the same as if the variable value is null (no dynamic parameters).

When the Query Type is "Select", the JDBC ResultSet is made available as the implicit "response" variable in the adapter Post-Script. Here's an example Groovy script that updates a UserDocument variable based on the ResultSet from the user selection query above:

import java.sql.ResultSet
import com.centurylink.mdw.user.UserDocument
import com.centurylink.mdw.user.UserDocument.User

ResultSet rs = (ResultSet) response;
if (rs.next()) {
  userDocument = UserDocument.Factory.newInstance();
  User user = userDocument.addNewUser();
  user.setCuid(rs.getString("cuid"));
  String name = rs.getString("name");
  String[] names = name.split(" ");
  user.setFirstName(names[0]);
  user.setLastName(names[1]);
}

If the Query Type is "Update", then the response is a java.lang.Integer indicating the number of rows updated.