How to use the Database Application for Dynamic SQL

Let's understand how to use the Database Application for Dynamic 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 on IBM® webMethods Integration.

Note: The Flow service DBTest_FS(Flow service) used in the example is a simple Flow service that uses the Dynamic 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:

Start creating a Flow service. Select Database from the drop-down list.

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:

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

Driver Group - Provide a name for the driver group.

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 run 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 Dynamic SQL action and click Next.

Type the Dynamic SQL query statement, part of which you set at run time using input fields.

Here, in our example, we have given the Dynamic SQL query for table EMP_A, at run time, and the where input field is set to where ID = 2.

Click Next. Input field is added at run time. You need to add the input explicitly, if you have the placeholder ? in the SQL query to receive input to the query at runtime.

Click Next. You will see the output fields as per the entered SQL when the Flow service is run.

Click Next.

Tip: To add the output explicitly, click Add Output, if you have output for SQL statement and you want to add the alias name for output fields.

Edit Execution Parameters and click Next.

Note: 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. Provide the input value and click the run icon to run the Flow service.

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