How to use IBM App Connect with JDBC

The Java™ Database Connectivity (JDBC) API is Java-based technology that provides access to data sources such as database management systems and files. IBM® App Connect provides a JDBC connector that offers database-independent connectivity. You can use this connector to connect to a number of supported databases to run custom SQL (create, retrieve, update, and delete) statements.
Availability:
  • A local connector in a Designer instance of IBM App Connect in containers (Continuous Delivery release)Local connector in containers (Continuous Delivery release) 11.0.0.10-r1 or later
  • A local connector in a Designer instance of IBM App Connect in containers (Extended Update Support)Local connector in containers (Extended Update Support release)
  • A local connector in a Designer instance of IBM App Connect in containers (Long Term Support)Local connector in containers (Long Term Support release)

You can use App Connect to pass key data from an application into a JDBC custom query that calls out to a database endpoint. The data that is returned from the database server can then be passed into other applications. The following information describes how to use App Connect to establish JDBC connections to database servers, and how to construct and pass SQL queries.

What to consider first

Before you use JDBC to connect to a database server, take note of the following considerations.

  • App Connect supports JDBC connections to the following databases: IBM Db2® Database editions that run on premises on either Linux®, UNIX, and Windows (LUW) or IBM i, IBM Db2 on Cloud, Oracle, and PostgreSQL.
  • Ensure that your database server is running and that a database connection exists for the database that you want to use with App Connect.

Connecting to JDBC

To create an integration flow that passes key data between your database server and other applications, you must connect App Connect to each app in the flow. You can add an account to connect to the database either from the App Connect Catalog page, or when you add a JDBC action to a flow in the flow editor.

To connect, you need the following database connection details. If you're not the owner or administrator of your database instance, you can obtain this information from your database administrator.

Database type

Select the type of database to connect to.

  • IBM Db2 Linux, UNIX, or Windows (LUW) - client managed (the default)
  • IBM Db2 Linux, UNIX, or Windows (LUW) - IBM Cloud
  • IBM Db2 for i
  • Microsoft SQL Server

    Support for Microsoft SQL Server is available in App Connect Designer 12.0.3.0-r1 or later.

  • Oracle

    Supports Oracle Database (non-RAC) and Oracle Real Application Clusters (RAC). Support for Oracle RAC is available in App Connect Designer 12.0.3.0-r1 or later.

  • PostgreSQL
Database name

Specify the name of the database, as set up in your system.

Database hostname

Specify the fully qualified hostname of the database server, or its IP address, for example, myserver.abc.com or 192.0.2.24.

Database port

Specify the port on which the database server listens.

Username

Specify a username that is authorized to access the database.

Password

Specify a password for the username.

Maximum pool size

Specify the maximum number of database connections that are allowed within a single connection pool. This value is used when an App Connect Designer flow is exported as a BAR file and deployed to an integration server or integration runtime in an App Connect Dashboard instance. If unspecified, the value defaults to 0 (zero).

Additional parameters

Specify extra parameters to control how to connect to the database. Use name-value pairs (with a semicolon separator) in the following format:
name1=value1;name2=value2.

For example, autoCommit=true;maxActive=20

Mandatory additional parameters are needed for the following database types.
Database type Required additional parameter

Microsoft SQL Server

By default, SQL Server Authentication is used as the authentication mode, and requires no additional parameters.

To connect through Windows Authentication, you must specify either of these additional parameters to indicate which NTLM version the endpoint supports.

  • AuthenticationMethod=ntlm2java
  • AuthenticationMethod=ntlmjava

Oracle

If you're using Oracle Real Application Clusters (RAC), the following additional parameter is needed to define the Oracle RAC service name.

ServiceName=oracleRACserviceName
Private network connection (optional)

Select the name of a private network connection that App Connect uses to connect to your private network. This list is populated with the names of private network connections that are created from the Private network connections page in the Designer instance. You see this field only if a switch server is configured for this Designer instance. For information about creating and configuring private network connections, see Connecting to a private network from App Connect Designer. (In App Connect Designer 12.0.10.0-r1 or earlier instances that include this field, the display name is shown as Agent name.)

  • Example of completed fields for connecting using JDBC

To connect to a JDBC endpoint from the App Connect Designer Catalog page for the first time, expand JDBC, then click Connect.

Tip:

Before you use the account that is created in App Connect in a flow, rename the account to something meaningful that helps you to identify it. To rename the account on the Catalog page, select the account, open its options menu (⋮), then click Rename Account.

General considerations for using JDBC in App Connect

The JDBC connector provides a single Custom query action that contains a single Custom SQL query multi-line field for specifying your custom SQL. Notice that the Custom SQL query field uses a database query notation SQL icon to indicate the type of input that's needed.

Custom SQL query field for the JDBC Custom query action
  • You can write your own custom SQL query, so you can use a single action to run a query against multiple schemas and database objects (such as tables) in the database.
  • The custom SQL can be any Data Query Language (DQL) or Data Manipulation Language (DML) statement. Ensure that the username and password you connect with has the necessary privileges for the type of queries that you want to run.
  • You can write complex queries that include JOINs and subqueries.
  • To eliminate the possibility of SQL injections (of forbidden strings or malicious code that might potentially corrupt the database) in your custom queries, a special type of mapping is provided to convert your query to a parameterized query (or prepared statement). In the parameterized query, placeholders are used for the parameters and the parameter values are supplied at execution time.
    • If you are typing the query, you can choose a mapping (in the usual way) from a previous node as the value of a parameter value in a filter clause, either by entering the first few characters of the field name or by clicking the mapping icon Insert a mapping icon to open the Available mappings list. The mapping, which would typically be inserted in this sample format Sample of a typical mapping, is instead converted to a mapping in parameterized form: Sample mapping from question mark.
    • When you type the query, you can also use a question mark ? to select a mapping for any value that you want to set for a parameter in the WHERE clause. After you type the comparison operator (for example, the equal sign =), add a space character and then type ? to open the list of available mappings and make a selection.

      Using a question mark to select a mapping

    • You can copy and paste a query into the Custom SQL query field. In the query that you copy, you can use a ? (with a leading space character) to denote a potential mapping, as shown in the following example.
      update contact set firstName= ? , lastName= ? , email= ? , title= ? , company= ? ,  contact_created_date= CURRENT DATE Where contactid= ?

      When you paste the query in the Custom SQL query field, all the ? characters (except for the last one) are highlighted within red boxes for your attention. The focus is on the last ? in the query, which is shown within a blue box, with an expanded list of mappings for you to choose from. You can then click the remaining ? in turn to replace them with mappings in parameterized form.

      Result of pasting a query

The JDBC Toolkit flow

The JDBC connector comprises a standard LoopBack® component, and a Java component that is implemented as an App Connect Enterprise Toolkit flow for the JDBC connector. In App Connect Designer and the App Connect Dashboard, flows and integration servers or integration runtimes that include a JDBC action incorporate this JDBC Toolkit flow.

  • When the first JDBC account is created in your App Connect Designer instance, a JDBC Toolkit flow is created internally and deployed as an integration server in the pod that contains the Designer runtime container. One JDBC Toolkit flow is created per Designer instance. If the deployment of this internal integration server fails for any reason, errors are displayed, and the logs can be collected and sent to IBM Support for investigation.
  • When you deploy an exported BAR file as an integration server or integration runtime in the App Connect Dashboard, a JDBC Toolkit flow is packaged automatically with that BAR file for deployment, and runs in the integration server or integration runtime when the deployment completes. The JDBC connector is deployed to the runtime container (which contains the App Connect Enterprise integration server) in the pod to establish connections to the database and run the operations.

    For example, if you export an API flow that includes a JDBC node from Designer, and then deploy the exported BAR file as an integration server or integration runtime in the Dashboard, you can click the integration server or integration runtime tile to open the deployed integration, and then view the contents. You can see a tile for the generated JDBC Toolkit flow, which is called gen.jdbcconnector.

    Example of a deployed integration server that contains a JDBC node, and the underlying integration
    Contents of the integration including the JDBC toolkit flow

    If you view the policy projects for the integration, you can also see a tile that is named gen.jdbcConnectorPolicies for the generated JDBC connector policy project.

    Policy projects for the integration, including a tile for the JDBC connector policies

Events and actions

JDBC events

These events are for changes in this application that trigger a flow to start completing the actions in the flow.

Note: Events are not available for changes in this application. You can trigger a flow in other ways, such as at a scheduled interval or at specific dates and times.

JDBC actions

Your flow completes these actions on this application.

Custom query