Search

Chapter 21. Connecting to SQL databases

download PDF

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

  • Apache Derby
  • MySQL
  • PostgreSQL

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

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

See the following topics for details:

21.1. Creating 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 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. Click Save 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.

21.2. Obtaining database records to trigger integration execution

To trigger execution of an integration based on the result of invoking a SQL statement or a SQL stored procedure, 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 connection page, click the database connection that you want to use to start an integration.
  4. On the Choose an action page, select one of the following:

    • Periodic SQL invocation obtains data by periodically invoking the SQL statement that you specify.
    • Periodic stored procedure invocation obtains data by periodically invoking the stored procedure that you specify or select.
  5. If you selected Periodic SQL invocation, in the Query field, enter a SQL SELECT statement or some other standard SQL statement that obtains 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 statement or stored procedure every five minutes.
  7. Click Next.

Limitations on the use of SQL extensions

SQL statements that you enter in the Query field must be standard SQL constructs. Fuse Online does not recognize or parse SQL extensions such as Procedural Language/PostgreSQL (PL/pgSQL) or Oracle Procedural Language Extensions to SQL (PL/SQL). However, you can use SQL extensions within stored procedures.

Result

Fuse Online tries to validate the connection, which includes checking that a specified SQL statement is syntactically correct and confirming that the statement 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.

21.3. About parameter placeholders and values in SQL statements that update data

A database connection that is in the middle of a flow, or that finishes a simple integration, can execute a SQL statement that updates data. When you add a database connection to an integration, you can specify placeholder parameters in the SQL statement to be executed. During execution, the connection can perform a batch update when the input parameter values are in a collection.

Specifying parameters in SQL statements

When you create or edit an integration and you add a connection that updates a database, you can specify placeholder parameters in the SQL statement that the connection executes at runtime or there can be placeholders in the stored procedure that will be executed at runtime. 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 the flow 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 Adding a data mapper step.

Batch update with a collection of parameter values

At runtime, when a database connection executes a SQL statement that inserts, deletes, or updates data, the SQL statement being executed often specifies one or more placeholder parameters, for example, :#task. When it does, the input to the connection can be a single set of parameters or a collection of parameter sets, where each element in a set defines a value for the corresponding placeholder parameter.

The input to the database connection comes from an earlier connection in the flow, for example, an HTTP request (Webhook) or a request sent to the REST API service for an API provider integration. When the input is a collection, the connection uses batch mode to update the table. For example, consider this SQL statement:

insert into todo (task) values (:#task)

If the input collection contains 3 values, the connection inserts three records, one for each value. Now consider a SQL statement with two placeholder parameters:

insert into todo (task, completed) values (:#task, :#completed)

In the input to the connection that executes this SQL statement, a data shape specification could specify a single element, for example:

{"task": "write some docs", "completed": 0}

Or it could specify a collection, for example:

[{"task": "write doc", "completed": 1},
 {"task": "publish doc", "completed": 0}]

With a collection as input, the connection executes the insert operation once for each set of parameter values. In this example, the connection adds two records to the table, one for each set of parameter values.

21.4. Accessing a database in the middle or to complete an integration

In an integration, you can access a database in the middle of a flow or to finish a simple integration. To do this, add a database connection to the middle of a flow or as a simple integration’s finish connection.

Prerequisites

  • You created a database connection.
  • You are creating or editing a flow and Fuse Online is prompting you to add to the integration. Or, Fuse Online is prompting you to choose a finish connection.

Procedure

  1. On the Add to Integration page, click the plus sign where you want to add the connection. Skip this step if Fuse Online is prompting you to choose the finish connection.
  2. Click the database connection for the database that you want to access.
  3. On the Choose an action page, select 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 that you specify or select.
  4. 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.
    • In the Batch update field, accept No, which is the default, or select Yes.

      The setting of Batch update affects connection behavior when the input to this action is a collection, and the SQL statement inserts, deletes, or updates records. The default behavior (Batch update is No) is that the connection accepts only individual objects and executes the SQL statement once for each object. When Batch update is Yes, the connection accepts a collection as the input to the action. The connection executes the SQL statement once and uses all collection items as input to a batch update operation.

    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.

  5. Click Next.

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 flow. If verification fails then Fuse Online displays a message about the problem. Update your input as needed and try again.

21.5. Connecting 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 a database connection.
  5. In an integration, you add the connection to your database. See Starting an integration by accessing a database or Accessing a database in the middle or to complete an integration.
Red Hat logoGithubRedditYoutubeTwitter

Learn

Try, buy, & sell

Communities

About Red Hat Documentation

We help Red Hat users innovate and achieve their goals with our products and services with content they can trust.

Making open source more inclusive

Red Hat is committed to replacing problematic language in our code, documentation, and web properties. For more details, see the Red Hat Blog.

About Red Hat

We deliver hardened solutions that make it easier for enterprises to work across platforms and environments, from the core datacenter to the network edge.

© 2024 Red Hat, Inc.