Chapter 24. Connecting to SQL databases

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:

24.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. 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. How you specify the database schema varies for each type of database. Details are in the next section: How to specify the schema in a database connection.
  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.

24.2. How to specify the schema in a database connection

In Fuse Online, when you create a database connection, the user interface prompts you to specify the database’s schema. How you specify the schema varies for each database. The table below shows what each database requires.

DatabaseExample connection configurationNotes

Apache Derby

Connection URL (JDBC URL): jdbc:derby:memory:testdb;create=true
Username: sa
Password: any value
Schema: sample

For testing purposes only. The default username is sa and the password can be anything but you must enter a value. In this example configuration, the connection uses the sample schema, which is like a namespace. For example, you can reference the mytable table with sample.mytable.

MS SQLServer

Connection URL (JDBC URL): jdbc:sqlserver://localhost:1433/sampledb
Username: kurt
Password: mypw
Schema: sampledb

Upload the driver by using the extension mechanism. Then create a connection. Connection verification fails if you have not already uploaded the driver. You must specify the same schema at the end of the connection URL and in the Schema field.

MySQL

Connection URL (JDBC URL): sql-connector.url=jdbc:mysql://mysql-host:3307/test?allowPublicKeyRetrieval=true&useSSL=false&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
Username: kurt
Password: mypw
Schema:

MySQL does not support schemas. When you create a connection to a MySQL database, leave the Schema field blank. MySQL expects all configuration in the connection (JDBC) URL.

Oracle DB

Connection URL (JDBC URL): jdbc:oracle:thin:@10.192.212.255:1521:xe
Username: kurt
Password: mypw
Schema:

Use the Fuse Online extension mechanism. to upload an Oracle database driver. Then create the connection. Connection verification fails if the driver has not been uploaded.
The schema is the database name. You must have permission to access this database instance, which is referred to as a schema in Oracle terminology. Leave the Schema field blank. In the Connection field, you must reference the Oracle system identifier (SID) of this database instance (xe in this example).

PostgreSQL

Connection URL (JDBC URL): jdbc:postgresql://syndesis-db:5432/sampledb
Username: kurt
Password: mypw
Schema: sampledb

You must specify the database in the connection (JDBC) URL. T If you want to use a schema you must specify it in the Schema field. A query can refer to a table in the form database.schema.table. When a reference specifies only a table name, the connection first searches the schema that you specify when you configure the connection. If the table is not found, then the connection searches public resources for the specified table. For more details, see PostgreSQL schema documentation.

24.3. 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.

      Note that the Database Connector accepts most basic SQL syntax. If you want to use a complex SQL statement, you should use a stored procedure instead since the Database Connector does not validate the SQL syntax in a stored procedure.

    • 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.

24.4. 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.

24.5. 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.

      Note that the Database Connector accepts most basic SQL syntax. If you want to use a complex SQL statement, you should use a stored procedure instead since the Database Connector does not validate the SQL syntax in a stored procedure.

    • 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.

24.6. 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.