Chapter 20. Connecting to SQL databases

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.

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

Procedure

1. Click the database connection for the database that 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 that 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.

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.