Using the Database application for Custom SQL

Know how to use the Database application for Custom SQL.

About this task

Understand how to use the Database application for Custom SQL. You have a flow service and you want to run the flow service. The flow service uses the Custom SQL action from the Database application.

To run the flow service, create an account to connect to the Database.

Procedure

  1. Start creating a Flow service. Select Database and then select Add Custom Action. The Connect account page appears.
  2. Provide a suitable action name and description and then select an existing Database account. You can also select the + option to add a new Database account.
  3. Provide a suitable name and description for the action you want to create, select an existing Database account, if any, and click Next. Select the + option if you want to add a Database account.
  4. If you select the + option to add a Database account, on the Add account page, 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.
    • Driver group - Select an existing driver group or click the + option to add a driver group.

      Provide the following details on the Add driver page.

      • Database - Select the supported database to connect.
      • Driver Group - Provide a name for the driver group.
      • Select the driver - Select the JDBC driver JAR to upload.
    • Transaction Type - Select the transaction type.
    • Data source Class - Select the data source class.
    • Server Name - Enter the server that hosts the database, for example sample.adapter.db.com.
    • User - Enter the username associated with the account on the database server.
    • Password - Enter the password for the specified username.
    • Database Name - Enter the database name to which the connection connects.
    • Port Number - Enter the port number.
    • Truststore Alias - Select the alias name of the webMethods Integration truststore configuration.
    • Keystore Alias - Select or add a keystore alias.
    • Network Protocol - Enter the network protocol that the connection uses when connecting to the database.
    • Property name - Enter the property name.
    • Property value - Enter the property value.
  5. Save the details.
  6. Now, on the Add custom action page, if you click Connect to account Database, the account is added. You can use this account to run any Database custom action created under the same project.
  7. Select an account from the Connect to account Database and click Next.
  8. Select the Custom SQL action and click Next. The action performs database operation where the column values can be set at run time. You can use ? as the value placeholder for each column in the Custom SQL.
  9. Type the Custom SQL query. You can pass values at runtime by using ? as the value placeholder for each column in the SQL statement.
  10. Click Next. The input details of the SQL appear. If inputs are not auto-populated for the SQL query, you can add them explicitly using the Add Input option. You can see the output fields as per the entered SQL.
  11. Click Next. If outputs are not auto-populated for the SQL query, you can add them explicitly using the Add Output option.
  12. Edit the execution parameters and click Next. This is an optional configuration.
  13. Verify the summary and click Done. To see the signature, click Show input/output. The flow service window appears.
  14. Click the run icon to run the flow service. You can see the output data for the configured custom action.