How to use the Database Application for Custom SQL

Let's understand how to use the Database Application for Custom SQL with the help of an example.

Let's say you have a Flow service on your environment named DBTest_FS(Flow service) inside DBTest(project). You want to invoke this Flow service and run it.

Note: The Flow service DBTest_FS(flow_service) used in the example is a simple Flow service that uses the Custom SQL action from the database application.

To invoke and run DBTest_FS(Flow service), create an account to connect to the Database. To do so, do the following:

  1. Start creating a Flow service. Select Database from the drop-down list.
  2. Select Add Custom Operation in the drop-down list next to Database.

This will redirect you to the Connect to account configuration screen.

Tip: You can alternatively select an existing action (if any) from the Select Action drop-down list.

Connect to a Database account.

Select an existing Database account (if any) from the drop-down list, provide a suitable name and description for the action you want to create and click Next or select the + button to add a new Database account.

In the Add Account configuration screen, provide the following details:

Name - Provide a suitable name for the account you want to add.

Description - Provide a suitable description for the account you want to add.

Database - Select the supported database to connect to from the drop-down list.

Driver Group - Select the existing driver group from the drop-down list or select the + button to add a new driver group. In the Add Driver configuration screen, provide the following details:

Browse - Select the JDBC driver jar to upload.

Save the details.

Transaction Type - Select the transaction type from the drop-down list.

DataSource Class - Select the datasource class from the drop-down list.

Server Name - Enter the server that hosts the database. For example, sample.adapter.db.com.

User Name - Enter the user name associated with the account on the database server.

Password - Enter the password for the specified user name.

Database Name - Enter the database name to which the connection connects to.

Port Number - Enter the port number.

Truststore Alias - Select the alias name of the IBM® webMethods Integration truststore configuration.

Network Protocol - Enter the network protocol that the connection uses when connecting to the database.

Property Name - Enter the property name. You can select the property from the drop-down list or use the + button to add a new property

Property Value - Enter the property value.

Click Add. This will take you to the Connect to account configuration screen of the Add Custom Action wizard.

Note: Now if you click on the Connect to Database drop-down list, you will see the added account in the list. This account can now be used to execute any Database custom action created under the same project.

Select an account from the Database drop-down list, provide a suitable name and description for the action you want to create, and click Next.

Select the Custom SQL action and click Next.

Type the Custom SQL query. You can pass values at runtime using ? as the value placeholder for each column in the SQL statement.

Here, in our example, we have given the Custom SQL query for table EMP_A where ID =?

Click Next. You will see the input details of the SQL. Here, in our example, we have provided the input field ID.

Note: If inputs are not auto-populated for the SQL query, you can add them explicitly using the Add Input button.

You will see the output fields as per the entered SQL. Here, in our example, we have selected all the columns of the table EMP_A as output.

Click Next.

If outputs are not auto-populated for the SQL query, you can add them explicitly using the Add Output button.

Edit the Execution Parameters and click Next.

This is an optional configuration.

Verify the summary and click Done.

Note: To see the signature, click Show Input/Output.

The Flow service window appears. Click the run icon to run the Flow service.

Here in our example, we have given 1 as the input value for input field ID. You will see the output data for the configured custom action.