Red Hat Training

A Red Hat training course is available for Red Hat Fuse

Chapter 18. Connect to SQL databases

In an integration, you can connect to any of the following types of SQL databases:

  • Apache Derby
  • MySQL
  • PostgreSQL

You create a connection to the database that you want to access in an integration and then you add that connection to an integration.

To connect to other types of databases, you must upload a JDBC driver for that database.

See the following topics for details:

18.1. Create a database connection

You create a separate connection for each database that you want to connect to in an integration. You can use the same connection in multiple integrations.

A database connection operates on a database table that you specify or invokes a stored procedure that you specify.

Prerequisite

The database table or the stored procedure must exist when an integration connects to the database.

Procedure

  1. Ensure that the JDBC driver for the database that you want to connect to is on your classpath. If you uploaded a JDBC driver library extension to connect to a proprietary database, then the upload process puts the driver on your classpath. See Creating JDBC driver library extensions.
  2. In Fuse Online, in the left panel, click Connections to display any available connections.
  3. In the upper right, click Create Connection to display Fuse Online connectors.
  4. Click the Database connector.

  5. Configure the connection by entering:

    1. In the Connection URL field, enter the JDBC URL for the database that you want to connect to. For example, enter jdbc:postgresql://ignite-db1234/sampledb.
    2. In the Username field, enter the name of the account that you want to use to access the database. Ensure that the specified password and user name are for the same account.
    3. In the Password field, enter the password associated with the user account you want to use to access the database.
    4. In the Schema field, enter the name of the schema for the database. If the connection URL specifies the schema, ensure that this field indicates the same schema as the connection URL. For example, enter sampledb.
  6. Click Validate. Fuse Online tries to validate the connection and displays a message that indicates whether validation is successful. If validation fails, revise the configuration details as needed and try again.
  7. If validation is successful, click Next.
  8. In the Connection Name field, enter your choice of a name that helps you distinguish this connection from any other connections. For example, enter PostgreSQL DB 1.
  9. In the Description field, optionally enter any information that is helpful to know about this connection. For example, enter Sample PostgreSQL connection that uses my login credentials.
  10. In the upper right, click Create to see that the connection you created is now available. If you entered the example name, you would see that PostgreSQL DB 1 appears as a connection that you can choose to add to an integration.

18.2. Start an integration by accessing a database

To trigger execution of an integration based on the result of invoking a SQL query or a SQL stored procedures, choose a database connection as the integration’s start connection.

Prerequisite

You created a database connection.

Procedure

  1. In the Fuse Online panel on the left, click Integrations.
  2. Click Create Integration.
  3. On the Choose a Start Connection page, click the database connection that you want to use to start an integration.

  4. On the Choose an Action page, click one of the following:

    • Periodic SQL invocation obtains data by periodically invoking the SQL query you specify.
    • Periodic stored procedure invocation obtains data by periodically invoking the stored procedure you specify or select.

  5. If you selected Periodic SQL invocation, in the Query field, enter a SQL SELECT statement to obtain one or more records. For example: SELECT * from my_db_table. The database table that contains the data you want must already exist.

    If you selected Periodic stored procedure invocation, in the Procedure name field, select or enter the stored procedure to invoke to obtain the data of interest. The stored procedure you specify must already exist. The database administrator should have created any stored procedures you need to use in an integration.

  6. In the Period field, enter an integer and indicate whether the unit is milliseconds, seconds, minutes, hours, or days. For example, if you specify 5 minutes then the connection invokes the specified query or stored procedure every five minutes.
  7. Click Done.

Result

Fuse Online tries to validate the connection, which includes checking that a specified SQL query is syntactically correct and confirming that the query or stored procedure target data exists. If verification is successful then Fuse Online adds the start connection to the integration. If verification fails then Fuse Online displays a message about the problem. Update your input as needed and try again.

18.3. Access a database in the middle or to complete an integration

To finish an integration by accessing a database, add a database connection as the finish connection. To access a database in the middle of an integration, add a database connection between the start and finish connections.

Prerequisites

  • You created a database connection.
  • You are creating or editing an integration.
  • You are on the Fuse Online Choose a Finish Connection page or the Choose a Connection page.

Procedure

  1. Click the database connection for the database you want to access.

  2. On the Choose an Action page, click one of the following:

    • Invoke SQL operates on data by executing the SQL statement you specify.
    • Invoke stored procedure operates on data by invoking the stored procedure you specify or select.

  3. If you selected Invoke SQL, in the SQL Statement field:

    • For a middle connection, enter a SQL SELECT statement that obtains one or more records or enter a SQL INSERT, UPDATE, or DELETE statement that operates on one or more records. The database table that contains the data must already exist.
    • For a finish connection, enter a SQL INSERT, UPDATE or DELETE statement to operate on one or more records.

    If you selected Invoke stored procedure, in the Procedure name field, select or enter the name of the stored procedure to invoke to operate on the data of interest. The stored procedure you specify must already exist. The database administrator should have created any stored procedures you need to use in an integration.

    • See the information below about specifying placeholder parameters in queries.
  4. Click Done.

Result

Fuse Online tries to validate the connection, which includes checking that a specified SQL query is syntactically correct and confirming that the query or stored procedure target data exists. If verification is successful then Fuse Online adds the connection to the integration. If verification fails then Fuse Online displays a message about the problem. Update your input as needed and try again.

Specifying parameters in queries

When you access a database in the middle of an integration or to complete an integration, you can specify placeholder parameters in the SQL query or there can be placeholders in the stored procedure. For example: 

INSERT INTO TODO(task, completed) VALUES(:#param_1, :#param_2)
DELETE FROM TODO WHERE task LIKE :#param_3

To specify the values of these placeholders, add a data mapping step to your integration before the database connection. In the data mapping step, map the appropriate source data fields to the target data fields, for example, map source data to the :#param_1, :#param_2, and :#param_3 target fields. See Add a data mapping step.

18.4. Connect to proprietary databases

To connect to a proprietary SQL database, the main tasks that must be accomplished are as follows:

  1. A developer creates a library extension that contains the JDBC driver for the database that you want to access in an integration. See Creating JDBC driver library extensions.
  2. The developer provides a .jar file that contains the library extension.
  3. You upload that .jar file to Fuse Online. See Making extensions available.
  4. You create a connection to your database by selecting the Fuse Online Database connector and specifying the connection URL for your database. See Creating database connections.
  5. In an integration, you add the connection to your database. See Start an integration by accessing a database or Access a database in the middle of an integration or to finish an integration.