Database connector actions

To use Database connector, you must select the actions to run. Actions connect to the database and initiate an action on the database from IBM® webMethods Integration.

You call database actions to run database operation on tables, views, or synonyms. The database actions are run by calling JDBC APIs.

Actions are based on templates that are provided with Database connector. Each template represents an SQL statement for running an action on a database. For example, use the select action to retrieve specified information from the database tables.

Creating an action from a template is straightforward. Create an account for creating a new action. Select the action template and configure the action that uses the wizards.

The database connector provides the following action templates -

Select

Retrieves specified information from the database table.

Insert

Inserts new information into a database table.

Update

Updates the existing information in a database table.

Delete

Deletes rows from a database table.

Stored Procedure

Calls a stored procedure. It obtains the stored procedure's input/output parameters by introspecting when you configure the action.

Batch Insert

Inserts new information into a database table. Use this action when you insert a high volume of data into a single table.

Batch Update

Updates the existing information in a database table. Use this action when you update a high volume of data in a single table.

Custom SQL

Defines and runs custom SQL to run database operations. You can run almost any SQL statement that is required, such as data management statements and data definition statements. The table describes the restrictions during SQL query statement creation. Specify the table alias along with the table name if the SQL query contains more than one table.

• select d.deptno, e.empno from dept d,emp e where d.deptno = ?

• Use different column name alias for columns in the SQL query.

  select firstname as f_name, lastname as l_name from emp

• Column names in the SQL query must not be enclosed in quotes.

  select partno as partno from emp where partno=1

• Use the "as" keyword when you specify an alias for a column.

  select city as dummy from emp

• Irrespective of the database type, the query syntax must follow the SQL standards.

  insert into example_default_now set id=?, data=?

• Dynamic SQL configures a dynamic SQL statement, part of which you set at run time by using input fields. At run time, the Dynamic SQL action creates the SQL statement by combining the contents of the input fields and then running it. It is useful when you need the flexibility to set all or part of an SQL statement at run time, instead of at design time. DynamicSQL uses $ to map a part of the SQL statement to the input field. At design time, the action template generates an input field with INPUT_FIELD_NAME. At run time, the Dynamic SQL action parses the statement and replaces the $ with the actual contents of the input field. For variable names, use the ? variable placeholder for each variable. Example.

select employee_name where StaffID = ? and Dept = ?

Example 1

select * from table1 ${where} 

At run time, the where input field is set to where col1 > 100. The final SQL query is -

select * from table1 where col1 > 100 

Example 2

select * from table1 ${where}

At run time, the where input field is set to where col1 > ?. As you are using placeholder ? to receive the input value for col1, you need to configure an input field to set the value for column col1. The final SQL query is -

select * from table1 where col1 > ?

Example 3

<pre><code>select ${output_fields} from table1 ${where}</code></pre><br/>

At run time, the where input field is set to where col1 > 100 and the output_fields input field is set to col1, col2, col3. The final SQL query is -

select col1,col2,col3 from table1 where col1>100