Invoking SQL Integration service flows

Edit online

To integrate with an external database, you can use the SQL Integration service flows that are available in the Business Automation Workflow System Data Toolkit by using Process Designer.

Before you begin

 Traditional: 
 Your Business Automation Workflow administrator must have defined the data sources that are needed for your SQL integrations, by using the admin console.

 Containers: 

 V21.0.3 

To integrate with SQL, update the Liberty configuration and add the driver files to your persistent storage, see Enabling the Liberty feature to integrate with SQL.

.

 V20.0.0.2  Complete the following preparations:
  1. If your database type is not Db2, you must make the JDBC driver available in NFS_storage_directory/db/jdbc. For more information, see Preparing storage . See also IBM Cloud Pak for Business Automation: Preparing storage.
  2. Define a data source in a Liberty server.xml snippet, by using a custom resource. For more information, see Customizing runtime server properties and Configuring relational database connectivity in Liberty.
  3. If you use SQL Integration services that read from files or write data to files, for example, SQL Blob to File, SQL Clob to File, SQL File to Blob, or SQL File to Clob, you must prepare a Persistent Volume, as described in the step "Prepare storage for IBM Business Automation Workflow" in Preparing storage. See also IBM Cloud Pak for Business Automation: Preparing storage.

    You then use those files from this Persistent Volume when starting the various SQL Integrations.

About this task

The System Data toolkit includes SQL Integration capabilities that enable you to easily integrate with external databases.

The SQL Integration service flows support common database interactions, including support for parameterized queries. These service flows can automatically map query results directly into the relevant variable type. The SQL Integration service flows enable you to develop implementations to:

  • Read existing data from a database.
  • Insert new data to a database.
  • Call-stored procedures in your database.

When passing data between Business Automation Workflow and a connected database, the SQL Integration service flows enable you to specify SQL data types such as integers, BLOBs, and CLOBs.

Important: The SQL Integration capabilities in the System Data toolkit do not participate in the transaction of the surrounding service flow or process instance.
The following SQL integration capabilities are available.
  • SQL Execute Statement
  • SQL Execute Statement (SQLResult)
  • SQL Execute Multiple Statements
  • SQL Execute Multiple Statements (SQLResult)
  • SQL Execute Script (SQLResult)
  • SQL Call Stored Procedure
  • SQL Call Stored Procedure (SQLStatement)
  • SQL Call Stored Procedure (SQLResult)
  • SQL Call Multiple Stored Procedures
  • SQL Call Multiple Stored Procedures (SQLResult)
  • SQL File to Blob
  • SQL File to Blob (SQLStatement)
  • SQL Blob to File
  • SQL Blob to File (SQLStatement)
  • SQL File to Clob
  • SQL File to Clob (SQLStatement)
  • SQL Clob to File
  • SQL Clob to File (SQLStatement)
  • SQL Get Database Type
The underlying external service SQLConnector is an undocumented implementation detail.

Procedure

  1. Open the Process Designer.
  2. Open a process application in the Designer view.
  3. Click the Toolkits category to see a list of toolkit dependencies for the current process application.
  4. Click the System Data toolkit to see its contents.
  5. Click the Services category and then click one of the listed SQL service flows. For example,
    • The SQL Execute Statement service flow.
    • The SQL Execute Statement (SQLResult) service flow provides a wrapper for the SQL Execute Statement service flow and returns the results in a fixed return type, SQLResult.
  6. Switch from the diagram view of the service to the Overview tab and read its documentation.
  7. Switch from the overview of the service to the Variables tab.
  8. Click an input or output variable to see its details, such as its type d any default values.

    To use an SQL integration service flow, you must specify the input and output variables that are listed in the following table.

    Table 1. Input and output parameters for the SQL Execute Statement services
    Name Type Description
    sql String This input parameter provides the SQL statement. For example, if the variable tw.local.sqlSelectSatatement contains the SQL statement string SELECT name, place FROM GREETING, enter the variable name tw.local.sqlSelectSatatement for the sql parameter.
    parameters List of SQLParameter This input parameter describes the value, type, and mode information for each '?' parameter that you use in your sql string. For each parameter, specify an SQLParameter object with an SQLType, the value of the parameter, and a SQLParameterMode.

    Specify SQLTypes according to the java.sql.Types of the JDBC specification. SQLParameterMode can be IN, OUT or, INOUT as defined by the JDBC specifications of java.sql.ParameterMetaData.
    maxRows Integer This input parameter limits the number of rows that are returned.
    returnType String This input parameter provides the name of the business object data type that the results are returned in. If you specify SQLResult the results are transformed automatically into a list of SQLResult, In this case, you need to create one variable of type SQLResult and mark it as a list variable.
    dataSourceName String This input parameter is the JNDI name of the data source that your Business Automation Workflow administrator defined, for example "jdbc/greetdb".
    results ANY This output parameter contains the results of the query. The results object is of the type that you specified for returnType.