Create an externalDBGateway

Printer-friendly version

Needed Software/Downloads

Tutorial

The external database gateway can be used to populate task and net variables using database connections or update database fields from output parameters. The prerequisites for this tutorial are a correctly configured database and having the corresponding drivers in the "lib" folder of your YAWL-installation. For this particular example, I will be using the Microsoft SQL Server Express 2008 R2 and Microsoft's official JDBC driver to connect to the database. For this configuration I have to make sure, that SQL-authentication is activated on the server and the connecting user has the appropriate rights to access the database.

Let's say we have a list of customers, that we want to display in a task. I have created a database on the server called "DB-NAME" and a very simple table "Customers":

 

To display these three customers in a task, I have created a new datatype in the data type definition of the YAWL workflows specification file using the external database gateway:

  <xs:complexType name="Customers">
    <xs:sequence>
      <xs:element name="Customer" type="xs:string" maxOccurs="unbounded" minOccurs="0" />
    </xs:sequence>
  </xs:complexType>

Now I will show how to create your own implementation of the externalDBGateway. In the IDE of your choice create a new project and add the YAWL library. Next create a class extending the "AbstractExternalDBGateway". Depending on how you want to use the database gateway, you will need to at least implement one of the following methods:

  • public Element populateTaskParameter(YTask task, YParameter param, Element caseData) {}
  • public void updateFromTaskCompletion(String paramName, Element outputData, Element caseData) {}
  • public Element populateCaseData(YSpecificationID specID, String caseID, List<YParameter> inputParams, List<YVariable> localVars, Element caseDataTemplate) {}
  • public void updateFromCaseData(YSpecificationID specID, String caseID, List<YParameter> outputParams, Element updatingData) {}

When assigned to a variable, the first method will be called at the start of a task if the database gateway is seleceted for the input mapping of a task variable. The second method will be called on completion of a task if the database gateway is assigned to the output mapping of a task variable. The third and fourth methods are the same as the first and second, but are intended for net variables.

In this tutorial I only want to display the database fields and do not intend to update the databse with new values, so I will only work with the "populateTaskParameter" method:

    public Element populateTaskParameter(YTask task, YParameter param,
            Element caseData) {
        
        Element result = new Element(param.getName());
      if (configure()) {
          List resultSet = _dbEngine.execSQLQuery("SELECT CAST(Customer as varchar) FROM Customers");
          
          for (Object row : resultSet) {
              Element content =
                  (Element) JDOMUtil.stringToElement(StringUtil.wrap(row.toString(), "Customer")).clone();
              result.addContent(content);
          }
       }
      return result;
    }

The Select-query gives me a list of Customers from the table shown above. It is also possible to use hibernate query language to access the database with the method "execQuery(String sql)" of the _dbEngine object, but to keep the tutorial simple I chose SQL. As you can see, I cast the column with the customer's name to varchar, because the Microsoft JDBC-Driver has problems with the type "nchar". This results in simple queries being rolled back and not getting any data. Back to the code, I simply transform each row to string and add the xml-tags with the StringUtil.wrap method. Additionally the returned element has to be of the type element so the JDOMUtil.stringToElement-method is used. Remember to use the clone()-method on the element as well or it won't work. The "Customers" data type in YAWL is designed, so that it can display any amount of rows and hence the loop will add any row to the result element.

If you look closely at the code you will see that an additional method to configure the database is needed, boolean configure(){}:

    private boolean configure() {
        if (! configured) {
            configureSession("org.hibernate.dialect.SQLServerDialect",
                            "com.microsoft.sqlserver.jdbc.SQLServerDriver", "jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=DB-Name",
                            "yawl", "yawl", null);
            configured = true;
        }
        return configured;
    }

The configure()-method makes sure the session to configured and a connection to the database is possible. You will need to call the method "configureSession" to be able to access the database. Its first argument is the database dialect, which depends on the database you use. Second is the driver used to connect to the database, followed by the url of the database server in the third argument. The url string is different on every configuration and should be checked with extra care. The fourth and fifth arguments are username and password needed to connect to the database. The last argument is an optional list of classes, if you are using hibernate.

When the class is compiled, you will need to place it in the correct folder of the YAWL-installation, in my case "[YAWL-dir]\engine\apache-tomcat-6.0.18\webapps\yawl\WEB-INF\classes\org\yawlfoundation\yawl\elements\data\external". After starting the engine, it will automatically detect the file and make it available to the YAWL-editor, if the editor has a connection to the engine.

To select a variable as being populated by an external database gateway, you need to right-click on a task or net in the YAWL-editor, choose "Update parameter mappings..." and there if you either create a new mapping or choose to update an existing mapping you will be see the "Data Gateway" tab showing all available data gateways:

Select the data gateway and hit "Done" and when the task of this workflow is started, the engine will try to connect to the database using the selected database gateway.

The result can be seen here as all three entries in the Customers table are shown in this task:

Attached to this tutorial are the *.class and *.java files of my implementation. The class is modeled on Michael Adams' "SimpleExternalDBGatewayImpl", but working with a concrete Mssql-database.

 

(Notice: This tutorial is based on  YAWL version 2.3.x)