Skip to main content
Skip table of contents

DB

The DB component is an all-encompassing powerful component which can be used to configure simple and nested queries such as insert, update, delete and select. It can also be used to monitor tables by value, by reference, by using alter tables and by using stored procedures. Monitoring feature can be used for loop detection in replicating databases. The graphic user interface of this component allows designing queries with the application of zero coding effort using the Design mode. However, the SQL mode can also be used to write queries. Syntactical validity of the SQL can be ensured by using the Check Syntax SQL button provided on the SQL configuration panel in the SQL mode. Following are some salient features of the DB component:

  • Query execution - Using this component, simple select, update, insert and delete queries can be executed.
  • Nested Query Execution - The DB component provides one level of nesting for insert, update, delete, and stored procedures.
  • Grouping - DB components support query grouping which is the execution of a set of queries in a pre-defined order.
  • Stored Procedures - The DB component supports execution of Stored Procedures.
  • Failover Queries - These queries are executed when an SQL Statement fails to execute due to an error. Failover queries maintain data consistency even in a case when an unexpected error while executing an SQL Statement is experienced by the component flow.
  • Post Processing - SQL statements for post processing can be defined after a single query execution or after execution of multiple queries.
  • Table Monitoring - The DB component supports monitoring of simple and nested tables for data insertion, updation, and deletion. This component also supports monitoring for updation of selected columns. Multiple tables can be monitored using this component.
  • Customized Transactions - The component can be configured to commit the entire transaction after a row, document, batch, or can be committed automatically.
  • Customized Response Size - The response size for the output of the component can be configured. This allows the processing of multiple records in a single transaction. For example, if only 100 records should be processed in a transaction, it can be set using the Response Size field. This ensures that only 100 records are sent as part of one message. If there are 500 records, 5 responses are sent with 100 records in each.
  • Support for Advanced and Complex Data types - The DB component supports BLOB, CLOB, User Defined Datatypes (UDTs) and different date-time formats.

Points to note

  • It is recommended NOT to use JDBC-ODBC Bridge driver to connect to any RDBMS in your production environment. Please use a commercial JDBC driver instead.
  • The JDBC drivers or the resources must be directly added onto the JDBC system lib and not as resource to the DB component itself. To add JDBC drivers to DB component, please refer to Adding Resources to a Microservice section.

Configuration and Testing

Managed Connection Factory

Connection details are configured in the first panel, which is Managed Connection Factory - MCF of Configuration Property Sheet (CPS).
The figure below shows the panel with the Show Expert Properties checkbox selected.


Figure 1: Managed Connection Factory Panel

Connection Properties

Use Connection Details From Input

Parameters to create the connection can be specified in the input message when this option is selected

Validation errors in the Managed Connection Factory panel of the CPS are treated as warnings. So you can bypass this step without giving valid configuration and complete the configuration of the component. If valid properties are not provided even in the input message, exception will be thrown at runtime.

Database Configuration

Click the Ellipsis

button to add the configuration details.


Figure 2: Database Configuration

Password Encryption Configuration

This property helps to use your own keys and algorithms to encrypt passwords.

Use Custom Encryption

Please refer "Custom Encryption of Passwords used in Event Processes" section in the Security page for details.

Component Configuration
Database

Select the appropriate database in the Database property; the drop-down lists all the supported databases as shown in the figure below. If the required database is not listed, select Other as the database option.


Figure 3: Database Drop-Down List


Driver

Driver class name that should be used to connect to the database. On selecting required database, Driver value is populated with standard value (This can be changed to required values based on driver being used).

jar/zip file containing the driver class should be added as resource to JDBC System Library.

URL

URL at which the database is running. On selecting required database, URL value is populated with standard value (This can be changed to required values based on driver being used). The populated value will have place holders which have to be replaced to point to correct database location, for example, replace <hostname> with the actual IP address where the database is located and provide the name of the database in the respective placeholder.

Example

In Figure 2, jdbc:oracle:thin:@<hostname>:1521:orcl has been edited to replace the placeholders as jdbc:oracle:thin:@192.168.2.54:1521:xe
In this, <hostname> has been replaced with 192.168.2.54 and "orcl" replaced with "xe"

User name

User Name of the particular database.

Password

Password for the respective User Name.

Connection ping sql

A SQL statement which is guaranteed to execute without exception, except when connection to database is lost. When a SQL exception occurs on executing a configured query, this SQL statement is executed. If execution of this SQL statement fails as well, then it is assumed that connection to database is lost and appropriate configured action (say, reconnect) is taken.

Example: "select * from dual" for oracle, "select 1" for MS SQL

Connection Properties

Any driver-specific connection properties which may have to be passed while creating a JDBC connection should be provided against Connection Properties. For example, fixedString=true (click Add and type-in name and value) uses FIXED CHAR semantics for string values in oracle.

Please refer to documentation of driver that is being used for valid name-values for connection properties.


Figure 4: DB Connection Properties

Auto commit

Commit mode that should be used by the JDBC connection.

Connection Pool Params

Here you can specify the details for maintaining the pool of connections in the component. Click the Ellipsis

button for the ConnectionPoolAttributes dialog box to appear.


Figure 5: Connection Pool Attributes

Enable Connection Pool

If selected, the connections created are cached in to a pool and used whenever required and available. This can reduce the time for creating a new connection for every input request. If disabled, a new connection is created for each request and it will be closed after completion of that request.

Max Pool Size

The maximum number of connections that can be allocated for the pool.

Blocking Timeout (in ms)

The time after which the call to fetch a connection from the pool will timeout if there is no unused connection available.

Idle Timeout (in ms)

The time after which the idle connections are returned back to the pool.

Advanced Settings


Figure 6: Advanced Setting section

Query timeout

Time, in seconds (>= 0), after which an exception is thrown if the query execution is not complete. For example, if this value is set to 60 and a query to database does not return within 60 seconds, then an exception is thrown and query execution is stopped.

Fetch size

Number of rows (>=0), which should be fetched from database into the component when iterating through result sets. This value provides a tradeoff between the number of trips on networks and memory requirement. For example, a query results in 1000 rows and fetch size is set to 500, then result set gets all rows from database in two sets of 500 rows each.

If this value is set to 0, all the rows are returned in one turn.

Enable jdbc driver logging

Selecting the checkbox enables logging at the driver level. This is used as a debugging option.

Login Timeout (in seconds))

Time, in seconds (>= 0), after which an exception is thrown if the login process is not complete.

Wrap DB object names

When database object names (viz. table names, column names, schema names) contain spaces, they should be wrapped in database-dependent special characters.

Example

  • " (opening quotation mark) as Start Wrap Character and " (closing quotation mark) as End Wrap Character for Oracle
  • [ (opening square bracket) as Start Wrap Character and ] (closing square bracket) as End Wrap Character for MS Excel

Database object names are wrapped as follows: Start wrap character + object name + End wrap character

Start wrap character

The character which should be used before the object name

End wrap character

The character which should be used after the object name

Interaction Configurations

SQL configuration details and advanced configurations are configured in the second panel (click Next in the Managed Connection Factory dialog box) of CPS, that is, Interaction configurations.


Figure 7: Interaction Configurations

Pre Processing XSL Configuration

Pre Processing XSL configuration can be used to transform request message before processing it. Click the ellipses button against the property to configure the properties.

Refer to the Pre/Post Processing XSL Configuration section in the Common Configurations page for details regarding Pre Processing XSL configuration and Post Processing XSL configuration (below).

Post Processing XSL Configuration 

Post Processing XSL configuration can be used to transform response message before sending it to the output port.

Process Message Based on Property

The property helps components to skip certain messages from processing.

Refer to the Process Message Based On a Property section in the Common Configurations page.

SQL Configuration

Click the SQL configuration Ellipsis

button to launch the wizard which allows configuring queries that have to be executed.


Figure 8: SQL Configurations Panel

SQL Configuration panel allows configuring multiple queries. To configure a query, click Add button and select required type of query from the pop-up menu.

Adding Query Configuration


Figure 9: Adding a Query

Explanation for different types of queries is given in the following table:

Type of QueryDescription

Insert Statement

Inserts/adds data into database table

Update Statement

Modifies existing data in database table. This option also allows to configure upsert queries (explained later)

Delete Statement

Deletes data from database table

Select Statement

Retrieves data from database table

Stored Procedure

Executes stored procedure in database

Monitor Table

Checks for inserts/updates/deletes on a table and reports them

Object Selection

Configuring queries requires selecting database objects on which actions have to be taken. Three kinds of objects are dealt within SQL configuration: tables, stored procedures, and user-defined data types. UI for selecting objects are very similar in appearance and functionality.

Table selection UI appears after selecting a query statement by clicking Add button, first in the SQL Configuration panel and then in the Query Builder panel.


Figure 10: Adding Table from Query Builder


Figure 11: Table Selection Dialog

Click the Refresh <object> (Refresh Tables, for table selection) button to select from the entire list of objects/tables. However, to narrow-down the search to select a particular database object, provide the selection criteria under Table selection criteria as explained below.

Schema

Click the Fetch Schemas button and then select Schema from the Schema drop-down, or type the name in the drop-down box or just type a pattern to find the appropriate Schema and select from the filtered list in the drop-down.

Select <none> to ignore schema while searching. Provide empty value to get objects without a schema.



Figure 12: Selecting a Schema

Object/Table

Type the exact table name or a pattern and click the Refresh <object> (Refresh Tables, for table selection) button to fetch the list of objects matching the criteria specified, and then select the object/table from the Database.
Schema and Object/Table name pattern, used to find objects by their specific names, comprise of SQL wild cards:

  • % represents a set of characters
  • _ represents one character

Examples:

  • S% finds all object names starting with S
  • %S% finds all object names containing S
  • _S% finds all object names whose second character is S

Result can be incrementally searched for the appropriate value by typing in first few characters when the result tree filters as per the criteria. In the below figure, "EMP%" is used in Table field, which has filtered the tables starting with "EMP".


Figure 13: Searching a Table

Response time for fetching the required objects depends on the search criteria, narrower the search criteria—faster the response.

Insert Statement Configuration

Click Add button and select Insert Statement option to launch Insert Query Builder.


Figure 14: Selecting Insert Statement

  • Do not type in the text area before configuring the query.
  • Do modifications only after all other configurations are done.
  • For user-modified values, required input/output details (for example, data types) will not be populated, while it has to be configured manually
Simple Insert Statement

Behavior: Inserts a row in configured table with column values taken from input XML or with constant column values.

  1. Provide a name for the query against Query Name.


    Figure 15: Insert Query Builder

  2. Click Add button to launch Table Selection Dialog panel.
  3. Select required table as explained in Object Selection section. Selected table is added to the easel under Table. Primary key column, if exists, is marked with
    adjacent to column name.


    Figure 16: Selected table added to easel with all columns

  4. Table can be changed by clicking Replace button and removed by clicking Remove button.
  5. If values are never to be inserted into a particular column, then that column can be unchecked (this requires column to have a default value or support null values


    Figure 17: Ignoring column for insertion

  6. To insert a constant value for a particular column, specify the required value in the Column Value column against the required column name.

    • If the value is a string, it should be wrapped in single quotes (' ')
    • If the Column Value is left as "?", the value is taken from input or from the output of another query where possible



    Figure 18: Inserting constant value into a table

    • Select statement is automatically generated and shown in the text editor under SQL Statement. The generated SQL Syntax can be validated by clicking the SQL

        button.

    • This feature only checks for invalid tokens, it does not perform a complete syntax check.


    Figure 19: Generated insert query

  7. Click OK to close the dialog box.

Insert Statement with Select

Behavior: Insert rows in configured table by selecting rows from another table.

  1. Follow the steps from 1 to 6 as described in the Simple Insert Statement section.
  2. Select SELECT Query option against Insert values using and click the Select Query Wizard button to launch Select Query Builder dialog box.


    Figure 20: Option to insert values into a table using select query

  3. Follow the steps as described in the Simple Select Statement section.
  4. As the Select Query Builder dialog box is closed, Insert Statement is automatically generated and shown in the text editor under SQL Statement in the Insert Query Builder dialog box.

    • Select statement is automatically generated and shown in the text editor under SQL Statement. The generated SQL Syntax can be validated by clicking the SQL

        button.

    • This feature only checks for invalid tokens, it does not perform a complete syntax check.

    Figure 21: Generated query to insert values using select

  5. Click OK to close the dialog box.

Insert Statement with failover

Behavior: Insert rows in configured table. If an exception occurs, insert in the exception table.

  1. Follow the steps from 1 to 8 as described in the Simple Insert Statement section.
  2. Click the Exception Table checkbox; the checkbox turns uneditable and Query Name also becomes uneditable holding a default name. This is the table wherein the values that raised exceptions are stored.


    Figure 22: Exception Table Selection

  3. Click Add and configure the exception table following steps from one of the previous Insert Statement sections based on the requirement (Clicking Cancel brings back the normal Insert Query Builder dialog box with the actual Table Name).
  4. Click OK to close the dialog.
Update Statement Configuration

Click Add button and select Update Statement to launch UPDATE Query Builder


Figure 23: Adding Update Statement

  • Do not type in the text area before configuring the query.
  • Do modifications only after all other configurations are done.
  • For user-modified values, required input/output details (for example, data types) will not be populated, while it has to be configured manually
Simple Update Statement

Behavior: Update rows satisfying defined condition in configured table, with column values taken fr98om input XML or with constant values. Condition values can also be taken from input XML or defined as constant values.

  1. Provide a name for the query against Query Name.


    Figure 24: Update Query Builder

  2. Click the Add button to launch Table Selection Dialog.
  3. Select required table as explained in the Object Selection section.
  4. Selected is added to the easel under Table. Primary key column, if exists, is marked with
    adjacent to column name.


    Figure 25: Selected table added to easel with all columns

  5. Table can be changed by clicking Replace button and removed by clicking Remove button.
  6. Select the columns whose values have to be set (Below figure shows that NAME and AGE columns are for update).


    Figure 26: Ignoring column that does not need update

  7. Selected columns automatically reflect under the SET tab.


    Figure 27: Columns added to SET clause

  8. Click the WHERE tab and select a column name on which where condition has to be applied.


    Figure 28: Adding condition on column to WHERE clause

  9. To select multiple columns for where condition, conditions can be combined by using AND or OR under And/Or column; click Add button to add more rows.


    Figure 29: Specifying multiple conditions for WHERE clause

  10. Operator of choice can be selected from the drop-down list under Operator column.


    Figure 30: Selecting operator for a condition



    Figure 31: WHERE tab with conditions and operators selected

  11. Constant values can be set to columns that have to be updated (under SET tab) or for values in where condition (under WHERE tab).
    1. To update a column with a constant value, specify the required value in the Column Value column against the required column name in SET tab.


      Figure 32: Specifying constant value for a column in SET clause

    2. To specify a constant value for where condition on a column, specify the required value in the Column Value column against the required column name in WHERE tab.

      • If the value is a string, it should be wrapped in single quotes (' ')
      • If the Column Value is left as "?", the value is taken from input or from the output of another query where possible



      Figure 33: Specifying constant value for a column in condition for WHERE clause

    3. To specify where condition on a column whose value is equal to value defined in another column, select the required column from the drop-down list in the Column Value column against the required column name in WHERE tab.


      Figure 34: Specifying comparison between columns for WHERE clause condition

    • Select statement is automatically generated and shown in the text editor under SQL Statement. The generated SQL Syntax can be validated by clicking the SQL

        button.

    • This feature only checks for invalid tokens, it does not perform a complete syntax check.


    Figure 35: Generated update query

  12. Click OK to close the dialog.

Update Statement with Failover Insert (aka upsert)

Behavior: Updates a row satisfying defined condition in the configured table with column values taken from input XML. Condition values can also be taken from input XML. If the update fails to update any rows (update count = 0), then insert a row with provided values.

  1. Configure Update Statement following the steps mentioned in Simple Update Statement.
  2. Check UPSERT check box.


    Figure 36: UPSERT Check Box
  • Values are inserted only for columns which are either selected for set or where clause
  • UPSERT fails if a column which has NOT NULL condition is not a part of either set or where clause.
  • UPSERT when placed under SELECT will not work as we do not support multi-level processing
Delete Statement Configuration

Click Add button and select Delete Statement to launch DELETE Query Builder.


Figure 37: Selecting Delete Statement

  • Do not type in the text area before configuring the query.
  • Do modifications only after all other configurations are done.
  • For user-modified values, required input/output details (for example, data types) will not be populated, while it has to be configured manually
Simple Delete Statement

Behavior: Delete rows satisfying defined condition in configured table, with column values taken from input XML or with constant values

  1. Provide a name for the query against Query Name.


    Figure 38: Delete Query Builder

  2. Click the Add button to launch Table Selection Dialog panel.
  3. Select required table as explained in Object Selection section.
  4. Selected table is added to the easel under Table.


    Figure 39: Selected table added to easel with all columns

  5. Table can be changed by clicking Replace button and removed by clicking Remove button.
  6. Specify condition which should be satisfied for deleting a row using WHERE condition. Select a column name on which WHERE condition has to be applied.


    Figure 40: Adding condition on column to WHERE clause

  7. When selecting multiple columns for where condition, conditions can be combined using AND or OR under And/Or column; click Add button to add more rows.


    Figure 41: Specifying multiple conditions for WHERE clause

  8. Operator of choice can be chosen from the drop down under Operator column.


    Figure 42: Selecting operator for a condition



    Figure 43: With conditions and operators selected


  9. To specify a constant value for where condition on a column, specify the required value in the Column Value column against the required column name.

    • If the value is a string value, it should be wrapped in single quotes (' ').
    • If the Column Value is left as "?", the value is taken from input or from the output of another query where possible.



    Figure 44: Specifying constant value for a column in condition for WHERE clause

  10. To specify where condition on a column whose value is equal to value defined in another column, select the required column from drop down in the Column Value column against the required column name in where tab.


    Figure 45: Specifying comparison between columns in condition for WHERE clause

    • Select statement is automatically generated and shown in the text editor under SQL Statement. The generated SQL Syntax can be validated by clicking the SQL

        button.

    • This feature only checks for invalid tokens, it does not perform a complete syntax check.



    Figure 46: Generated Delete Query

  11. Click OK to close the dialog box.

Select Statement Configuration

Click Add button and select Select Statement option to launch Select Query Builder.


Figure 47: Selecting Select Statement

  • Do not type in the text area before configuring the query.
  • Do modifications only after all other configurations are done.
  • For user-modified values, required input/output details (for example, data types) will not be populated, while it has to be configured manually
Simple Select Statement

Behavior: Retrieves data from all columns or from selected columns in a configured database table.

  1. Provide a name for the query against Query Name.


    Figure 48: Select Query Builder

  2. Click Add button to launch Table Selection Dialog.
  3. Select required table as explained in Object Selection section.
  4. Selected table is added to the easel under Table. Primary key column, if exists, is marked with
    adjacent to column name.


    Figure 49: Selected table added to easel with all column

  5. Table can be changed by clicking Replace button and removed by clicking Remove button.
  6. To retrieve values from specific columns in the table, select the required checkboxes under Columns (in the Tables section, right below the table name) to build a Select Query with specific columns.

    If no column is checked, then *SELECT ** is used. Select the columns in the order in which they should appear in select clause



    Figure 50: Ignoring column for selection

  7. Selected columns are shown under Columns tab in the bottom part of the Select Query Builder. Check/Uncheck the check box in Output column against required column name to include/exclude the respective column in the output XML.

    For example, configuration in the following image generates ID in the output XML but does not generate NAME in output XML even though values for both ID and NAME are retrieved from the table.


    Figure 51: Selecting columns for output XML

  8. To define a column alias, provide the alias name under Alias column against the required column name. Aliases are useful when the column name is too long or is not intuitive. When an alias is specified, output XML contains an element with defined alias name instead of the column name.


    Figure 52: Defining Column Alias

  9. Check the DISTINCT check box to return unique rows, ignoring duplicate values.


    Figure 53: Distinct option to return unique values

    • Select statement is automatically generated and shown in the text editor under SQL Statement. The generated SQL Syntax can be validated by clicking the SQL

        button.

    • This feature only checks for invalid tokens, it does not perform a complete syntax check.



    Figure 54: Generated select query

  10. Click OK to close the dialog box.

Select Statement with Filter

Behavior: Retrieves data from all columns or from selected columns in a configured database table after applying specified conditions. Condition values can be provided from input XML or as constant values.

  1. Follow the steps from 1 to 8 as described in the Simple Select Statement section.
  2. Click the WHERE tab, click the Add button, and then select a Column name on which WHERE condition has to be applied.


    Figure 55: Adding condition on column to WHERE clause

  3. When selecting multiple columns for WHERE condition, conditions can be combined using AND or OR under And/Or column; click Add button to add more rows.


    Figure 56: Specifying multiple conditions for WHERE clause

  4. Operator of choice can be selected from the drop-down list under Operator column.


    Figure 57: Selecting operator for a condition



    Figure 58: WHERE tab with conditions and operators selected

  5. Constant values can also be set for values in WHERE condition (under WHERE tab).
    1. To specify a constant value for WHERE condition on a column, specify the required value in the Column Value column against the required column name in WHERE tab.

      • If the value is a string, it should be wrapped in single quotes (' ')
      • If the Column Value is left as "?", the value is taken from input or from the output of another query where possible.



      Figure 59: Specifying constant value for a column in WHERE
      tab

    2. To specify WHERE condition on a Column whose value is equal to value defined in another Column, select the required Column from drop-down list in the Column Value against the required column name in WHERE tab.


      Figure 60: Specifying comparison between columns in condition for WHERE

      • Select statement is automatically generated and shown in the text editor under SQL Statement. The generated SQL Syntax can be validated by clicking the SQL

          button.

      • This feature only checks for invalid tokens, it does not perform a complete syntax check.



      Figure 61: Generated select query with filter

  6. Click OK to close the dialog box.

Select Statement with Sorting

Behavior: Retrieves sorted data from all columns or from selected columns in a configured database table. Data is sorted in configured order on columns configured for sorting.

  1. Follow steps 1 to 8 in the Simple Select Statement section.
  2. To specify columns which have to be sorted, select the appropriate sort order from drop-down list under Order By column. Order By for each columns has one of the following values:

    Order By Value

    Explanation

    Unsorted

    Data is not sorted on values in the column, that is, no order by clause is added in the SQL statement.

    Ascending

    Data is sorted in ascending order on values in the column, that is order by clause is added in the SQL statement as ORDER BY <column name> ASC.

    Descending

    Data is sorted in descending order on values in the column, i.e. order by clause is added in the SQL statement as ORDER BY <column name> DESC.

    Default

    Data is sorted in default order for order by clause on values in the column, that is, order by clause is added in the SQL statement as ORDER BY <column name>.


    Figure 62: Selecting sorting order for column

    An example of SQL statement with different sort orders is shown below.


    Figure 63: SQL Statement with different columns sorted in different order

  3. When multiple columns have to be sorted, sorting priority for each column can be set under Sort Priority. Columns are sorted in order of increasing Sort Priority that is column with minimum value for Sort Priority is order first.

    When values of Sort Priority for multiple columns are same, columns are sorted in the order in which they appear in select clause.


    Figure 64: SQL Statement with Sort Priority

    • Select statement is automatically generated and shown in the text editor under SQL Statement. The generated SQL Syntax can be validated by clicking the SQL

        button.

    • This feature only checks for invalid tokens, it does not perform a complete syntax check.

  4. Click OK to close the dialog box.
Select Statement with Grouping

Behavior: Retrieves data, after applying grouping conditions, from all columns or from selected columns in a configured database table.

Grouping functions are not provided in query builder. Grouping conditions have to be explicitly added by editing the SQL statement either before closing the query builder or by launching.

  1. Follow the steps from 1 to 5 as described in the Simple Select Statement section.
  2. Click on GROUP BY tab and check under Select against the columns under Group By on which group by condition should be applied.


    Figure 65: Selecting columns for grouping condition

  3. To filter the results click on HAVING tab and define required conditions. HAVING tab has functionality similar to WHERE tab (described in Select Statement with filter).


    Figure 66: Adding condition to HAVING clause

  4. Select required columns under Tables


    Figure 67: Selecting required columns

  5. Edit Select and HAVING clauses to apply appropriate grouping condition on selected columns.

    Editing Select and HAVING clauses should be last action before closing the dialog.



    Figure 68: Generated select query with grouping

  6. Click the Ok button to close the dialog.
Select Statement with Multiple Tables

Behavior: Retrieves data from all columns or from selected columns from multiple configured database tables.

  1. Follow the steps from 1 to 5 as described in the Simple Select Statement section.
  2. To add another table, click the Add button to launch Table Selection dialog.

    • Multiple tables can be added by repeating this step.
    • Specify any conditions after selecting all required tables.


    Figure 69: Selecting multiple tables

  3. Add WHERE condition, described in Select Statement with filter section, to perform join on the tables. If no condition is specified, Cartesian product of rows in all selected tables is returned.


    Figure 70: Usining WHERE condition for multiple Tables

  4. To specify the join, in WHERE tab, select the required column from one table under Column and select the required column from another table under Column Value.


    Figure 71: Generated SQL statement with join

  5. To specify filtering, sorting or grouping conditions, refer the previous sections.
  6. Click the Ok button to close the dialog.
Merge Statement Configuration

Click Add > Merge Statement to launch Merge Query Builder.


Figure 72: Adding Merge Statement

Merge statement can only be configured in eStudio CPS.

Behavior: Updates or inserts rows in target table with rows selected from one or more source tables.

  1. Provide a name for the merge query in the Query Name text field.


    Figure 73: Adding name in Merge Query Builder

  2. Select Target Table

    Select the target table using the Object Selection dialog by clicking the Add button under Select Target Table.

    • The added table can be replaced or removed by using Replace and Remove buttons respectively.
    • Columns based on which ON condition clause is created should be unchecked as shown in the figure below. These unchecked columns will not be shown in Update SET table (as shown in Figure: Configuring Post-condition)


    Figure 74: Select Target Table

  3. Select Source Table


    Figure 75: Select Source Table

    • Merge using 'dual': Enabling this option manipulates data in the target table itself without a source table. Resulting MERGE statement would be like"'MERGE INTO <TargetTableName> USING DUAL ON...". Enabling this option would disable Source Alias and Select Query Wizard.

    • Source Alias: Specify a correlation name which is an alias for the SELECT subquery to be referenced elsewhere in the MERGE statement.

    • Select Query Wizard: Select the source subquery using the Select Query Wizard under Select Source Table. Configure the SELECT subquery as detailed in section Select Statement Configuration

  4. Configure Pre-condition


    Figure 76: Configuring Pre-condition
    • Use the Pre-condition option (click Expand
      icon) to specify the condition upon which the MERGE operation either updates or inserts.
      For each row in the target table for which the search condition is true, the component updates the row with the corresponding data from the source table. If the condition is not true for any rows, the component inserts into the target table based on the corresponding row from the source table.
    • The drop-down list under the Column column consists of columns from target table and that of under the Column Value column consists of columns from both target and source tables.

  5. Configure Post-condition


    Figure 77: Configuring Post-condition
    Based on whether Pre-Condition is satisfied or not for a given row in the target table, the query either updates or inserts data in the target table.
    • Qualify column names for UPDATE and INSERT clauses – Select this option to qualify, with the schema and table names, names of the columns in the target table.
    • Add UPDATE clause – Select this option to add UPDATE clause to the MERGE statement.
    • Add INSERT clause – Select this option to add INSERT clause to the MERGE statement.
      Atleast one of UPDATE and INSERT clause should be added.

    1. UPDATE
      • The UPDATE clause specifies the new column values of the target table.
      • Only the columns that are checked under Select Target Table will be shown here.
      • The Configuration of Update SET and Update WHERE are detailed in Update Statement Configuration section.
      • In the Update WHERE table, the drop-down list under the Column column consists of columns from target table and that of under the Column Value column consists of columns from both target and source tables.
    2. DELETE

      Figure 78: Configuring Delete WHERE Clause


      Specify the Delete WHERE clause to clean up data in a table while populating or updating it.

    3. INSERT


      Figure 79: Configuring INSERT clause

    • The INSERT clause specifies values to insert into the column of the target table if the condition of the ON clause is false.
    • Configure INSERT query as detailed in Insert Statement Configuration Section
Points to Note

While configuring for MSSQL

  • After configuring the MERGE query using UI, edit the sql statement and add a semi-colon (:) at the end of the query. Do not make any changes in the UI after adding the semi-colon.
  • DELETE clause is not supported.
  • Un-check the 'Qualify column names for UPDATE and INSERT clauses' option.
Stored Procedure Configuration

Behavior: Executes a stored procedure and returns the result (returns return value or out parameter values).

  • Functions can also be configured
  • Stored Procedure/Function has to be executed at configuration time if it returns a result set to create the output structure.

To configure the Stored Procedure, perform the following steps:

  1. Click the Add button and selectStored Procedure option to launch Stored Procedure Query Builder.


    Figure 80: Adding Store Procedure

  2. Provide a name against Query Name.


    Figure 81: Adding Query name for Stored Procedure
  3. Click Stored Procedure Ellipsis
    button.
  4. Select required procedure as described in Object Selection section.


    Figure 82: Stored procedure details

  5. Parameters and their configurations are automatically populated.

    Stored Procedures feature does not support User-defined Data Types presently.

    Column

    Description

    Parameter

    Name of the parameter for named parameters; blank otherwise.

    Parameter Type

    Type of parameter – IN, OUT, INOUT, UNKOWN, RETURN, RESULT
    Values of type OUT, INOUT, RETURN, RESULT form output structure.

    Data Type

    SQL data type of the parameter.

    Sample Data

    NA

  6. Before closing the Stored Procedure dialog, select Execute checkbox (as in the above figure) to execute the stored procedure to create output structure under the Output tab.

    If not selected, the output structure will not be defined and has to be manually defined.



    Figure 83: Output structure generated for selected stored procedure

  7. Click OK to close the dialog box.
Monitor Table Configuration

Behavior: Monitors a configured table for any changes such as data addition, data removal and data updates.

Monitoring a table requires creation of temporary table/stored procedures and data types and hence is very specific to database in use. This option is not supported when Database selected is "Other" in MCF panel. This option is supported only for the following databases against Database in MCF panel – IBM DB2, HSQL, Kingbase, Microsoft Access, Microsoft SQL Server, Microsoft SQL Server 2005, Mckoi, MySQL, Oracle, Sybase.

In the SQL Configuration Wizard dialog box, click the Add button and select Monitor Table option to launch SQL Creation Wizard.


Figure 84: Adding Merge Statement


Figure 85: Monitor table wizard

Select DB Table
  1. Click Monitor Table Ellipsis
    button and choose the table to monitor (refer to Object Selection section).
  2. Select actions which have to be monitored by choosing the options as below:
    • Insert: Notifies when a row is added to monitored table.
    • Delete: Notifies when a row is deleted from monitored table.
    • Update Of Selected Columns: Notifies when a selected column is updated to new value. Column selection panel appears on the right side when this checkbox is selected.


      Figure 86: Selecting table for monitoring

  3. For each action which has to be monitored, specify conditions which filter changes to be notified, click the Ellipsis
    button for the respective option to configure expression to filter inserted records.
  4. In the Insert Trigger Expression dialog box, define condition on required columns in the same way as in WHERE tab (refer Select Statement with Filter section). Below figure shows configuring a condition – send notification if a row is inserted with ID > 500.

    Do not leave column value as "?" as possible in WHERE tab.




    Figure 87: Specifying filter condition for monitoring

  5. Click OK and then click Next.
Monitor Option


Figure 88: Selecting monitor option

Select one of the following options to monitor actions on table:

Shadow Table

Creates a table containing all columns in the monitored table and a few additional columns (TIF_RECORDID, TIF_OPERATIONTYPE and TIF_STATUS) required for monitoring. This option is supported only on following databases – IBM DB2, Kingbase, Microsoft SQL Server, Microsoft SQL Server 2005, Mckoi, Oracle, Sybase.

Trigger should be allowed by database to use this option.

Alter Main Table

Modifies the monitored table to add TIF_RECORDID, TIF_OPERATIONTYPE and TIF_STATUS columns required for monitoring. This option is supported by all databases that support monitoring.

  • This option should be used with caution as changing table definition might break other applications.
  • TIF_RECORDID, TIF_OPERATIONTYPE and TIF_STATUS columns should be populated externally.

When monitor option is Shadow Table, select one the following methods to create a shadow table.

  • Monitor By REFERENCE
    Shadow table is created with columns TIF_RECORDID, TIF_OPERATIONTYPE, TIF_STATUS and primary key of monitored table.
  • Monitor By VALUE
    Shadow table is created with columns TIF_RECORDID, TIF_OPERATIONTYPE, TIF_STATUS and all columns of monitored table.

Enable Loop Detection: Modifies the monitored table to add an additional column TIF_SOURCE whose value should be NULL for notifications.

  • This option should be used with caution as changing table definition might break other applications.
  • TIF_RECORDID, TIF_OPERATIONTYPE and TIF_STATUS columns should be populated externally.
Polling Options


Figure 89: Selecting polling option

Based on monitor option selected, either shadow table or monitored table should be continuously polled to identify changes done to monitored table and notify. Select one of the following options for polling:

Stored Procedure

This option is supported only on following databases – IBM DB2, Kingbase, Microsoft SQL Server, Microsoft SQL Server 2005, Oracle, and Sybase.
Names for all databases that is created are populated automatically and can be changed.

Select Statement

This option is supported by all databases that support monitoring. It creates an update and a select statement instead of a single stored procedure.

Statements Overview


Figure 90: Statements Overview panel

  • Click View SQLs to check SQLs which create database objects required for monitoring, . These SQLs are by default executed when Finish button is clicked.
  • Click View Cleanup SQLs, to check SQLs which remove all database objects created for monitoring.

    SQLs and Cleanup SQLs are saved at following locations for future reference:

    • SQLs – %ESB_USER_DIR% \ studio \ <build no>\ cache \ components \ DB \ 4.0 \ <monitor table name>_config.sql
    • Cleanup SQLs – %ESB_USER_DIR% \ studio \ <build no>\ cache \ components \ DB \4.0 \ <monitor table name>_cleanup.sql
  • Select Ignore SQL execution errors check box to finish the wizard even if some exceptions occur when executing SQLs to create database objects required for monitoring.

  • Select Do not execute SQLs on Finish check box to finish the wizard without creating database objects required for monitoring.

    For the above two options selected, appropriate database objects should be created by yourself.

    Click Finish after completing the above configuration to save the configuration set.

SQL Statement Details Configuration


Figure 91: SQL Statement Details

SQL Statement Details shows detailed configuration of the selected query:

  • SQL statement in Query tab.
  • Configuration of input parameters which have to be passed to execute the query.
  • Configuration of output parameters which are returned after query execution.

Input and output parameters are automatically populated when a query is configured and connection to database is available. However, the populated values can either be modified or defined manually. To define input/output structure manually, a sound understanding of database objects involved is required. ResultSets, parameters, and columns can be added to input or output structure by right clicking Structure column.


Figure 92: Building output structure manually

Configuring Input Parameters

Basic view of input tab is shown below.


Figure 93: Input tab showing basic view of input structure

Check advanced check box to see advanced configuration details.


Figure 94: Input tab showing advanced view of input structure

Column Name

Description

Structure

This value is used to generate the schema for the query. In the above figure value for IDNO (field name) is changed to IDN. So the schema generated would contain IDN as the first element instead of default populated value, IDNO.

Data Type

This defines the data type of this column in the database table. This should be correctly defined.

Default Value

This value is taken for the column it is defined against, if the node satisfying the XPath, defined in MapTo, in the input XML is not present. Values $EMPTY_STR and $NULL represent empty string and null values respectively.

String literals need not be wrapped in.

Map To

The XPath like expression at which the value for this column is present in the input XML. This can be edited to any value to suit input XML.
In case of child queries (nested/post processing/fail over), value from the result of parent can be passed to input of nested query. Value from parent query which should be mapped can be selected from a drop-down list of proprietary expressions ending with the index of output. Further, in case of nested queries, when parent query result is being passed, the following syntax can be used to configure to pass all,first and last value(s) from list values:

$All[<expression>]
$First[<expression>].
$Last[<expression>]

Changing this value does not change the input schema. So it is not recommended to change this value.

Bind Position

The position in the query where this value is bound to.

Do not change this value.

Java Type

JDBC type which maps to Data Type.

Configuring Output Parameters

Basic view of output tab is shown in the Figure below.


Figure 95: Output tab showing basic view of output structure

Check advanced check box to see advanced configuration details.


Figure 96: Output tab showing advanced view of output structure

Each of the columns in Output tab is explained in the table below:

Column Name

Description

Structure

This value is used to generate the schema for the query. In the above figure value for EMPNO (field name) is changed to EMPN. So the schema generated would contain EMPN as the first element instead of default populated value, EMPNO

Data Type

This defines the data type of this column in the database table. This should be correctly defined.

Default Value

NA for output

Output Name

NA

Include

If the output XML should contain an element corresponding to column check this check box, else uncheck it. E.g. If the check box against COMM is unchecked, the output XML will not contain COMM element for any record

XML

NA

Bind Position

NA

Java Type

JDBC type which maps to Data Type

Configuring Input/Output Parameters for Inner Queries

The component does not recognize the input/output parameters present in the inner query. These parameters should be manually configured.
If the query has an inner query, when the query wizard is finished, an error occurs. To configure input/output parameters manually, ignore this error.


Figure 97: Sample Inner Query

To add Input parameters, click on Input tab in SQL Statement Details window. Right-click on Input under Structure column and select Add In Parameter.


Figure 98: Configuring Input Parameters for Inner Query

Similarly, to add ResultSets, click on Output tab. Right-click the ResultSet structure and select Add ResultSet.


Figure 99: Configuring Output Parameters for Inner Query

Editing Query Configuration
Editing DML Statements
  1. Select a configured query, for example, Update Statement, under SQL Statements.
  2. Click Edit to launch Query Builder in edit mode. This mode is same for all DML statements (Select, Insert, Update, and Delete).


    Figure 100: Editing configured SQL query

  3. Make necessary changes in the SQL Statement.

    • Select statement is automatically generated and shown in the text editor under SQL Statement. The generated SQL Syntax can be validated by clicking the SQL

        button.

    • This feature only checks for invalid tokens, it does not perform a complete syntax check.

  4. When the dialog is closed, the input / output parameters in Input/Output tab in SQL Statement Details Configuration are regenerated.

    If these parameter configurations are previously changed from generated values and should not be lost, check Retain Input Parameters / Retain Output Parameters (as in the above figure) respectively.

  5. Click OK to close the dialog.

Editing Stored Procedure
  1. Select a configured stored procedure under SQL Statements.
  2. Click Edit to launch Query Builder for Stored Procedure and follow steps in Stored Procedure Configuration section.


    Figure 101: Editing stored procedure
  3. Check Execute check box (as in the above figure) before closing Query Builder if the structure of result set returned by stored procedure is changed and Output tab in SQL Statement Details Configuration have to be regenerated.
  4. Click OK to close the dialog box.
Removing Query Configuration

Select the query to remove and click Remove button.


Figure 102: Selecting query to be removed

Testing Query Configuration
  1. A configured query can be tested from SQL Configuration panel. To test a query, select the query and click Execute button.


    Figure 103: Selecting query to be tested

  2. Specify Variable Values dialog box opens.
    The parameters (below figure) represent the query present in the SQL Statement Details section of SQL Configuration dialog box (above figure)


    Figure 104: Input parameters which require user values

  3. Specify values for parameters which require user input (values marked "?"), that is, enter values in the Parameters tab under Value column. Other columns are not editable.


    Figure 105: Specifying values for input parameters

  4. Click Run. Result of the query is shown under Results tab.
  5. Click Commit to commit an insert / update or a delete to database, click Rollback otherwise.
  6. Click Cancel to close the dialog.
Child Queries

For each configured query, different types of child queries can be configured. Different types of child queries are listed below:

  • Nested queries
  • Post processing queries
  • Failover queries
Nested Query

A query which executes once for each record returned from parent query.

  • Nested query should ideally be configured for select statements.
  • Nested query takes values from input.
  • Nested query sends values in output.
  • Nested query can have a failover query as a child query.

Example:

  • For every row in employee table, get the department details to which the employee belongs.
  • For every row in employee table, compute total income (salary + commission) and update in incomes table.
Post Processing Query

A query which is executed after the parent query is executed.

  • Post processing query should ideally be configured as an insert or update or delete statements or as a stored procedure which updates database.
  • Post processing query takes values from input.
  • Post processing query does not send values in output.
  • Post processing cannot have any child query.
Failover Query

A query which is executed when the parent query failed to execute, because of an exception.

  • Failover query should ideally be configured as an insert or update or delete statements or as a stored procedure which updates database.
  • Failover query should be configured to take same value, as the parent query, from the input XML. This can achieved using MapTo column in Output tab of SQL Statement Details Configuration.
  • Output of the parent query and the failover query should match. For example, if both are either insert or update or delete independently, then the output matches (only update count is returned).
  • Failover query cannot have any child query.
Child Query Configuration
  1. Configure any query.
  2. Check advanced check box against SQL Statements.


    Figure 106: Advanced option for SQL Statements

  3. Right-click on the query and navigate to:
    1. Add Nested Query > <query of interest> for Nested Query.
    2. Define Failover Query > <query of interest> for Failover Query.
    3. Add Post Processing Query > <query of interest> for post Processing Query.


      Figure 107: Adding a child query

  4. A query builder is launched. Refer to the appropriate section based on the query that has to be configured. Configured query is shown as a child node to initial query.


    Figure 108: Configured query appearing as the child

    • If the child query requires any input, it is by default configured to be taken from input XML. Schema generated on the input port is computed to take inputs for child query as well.
    • Child query can also take input from the result of parent query.

  5. To configure child query to take input from the result of parent query:
    1. Select the child query.
    2. Click Input tab in SQL Statement Details panel.
    3. Select the Advanced checkbox.
    4. In the MapTo column against the required column name ('DEPTNO' in the figure below), click on the MapTo drop-down list to see a list of entries, representing the corresponding columns present in the parent queries result.
    5. Select the appropriate value from the MapTo drop-down list, which matches the parent output.


      Figure 109: MapTo entries for result of parent query

      For example, below figure shows that department number is the third field (Select SelectEmployee and click Output) in the output of parent query. Hence, choose the entry: $OUT/SelectEmployee/SelectEmployee/3 to map the DEPTNO of parent query (SelectEmployee) to input of child query (DeptDetails) .


      Figure 110: Output of parent query

      $OUT/employee/employee/3 is computed using proprietary formula and should not be modified

      The sequence in which the parameters appear in the output schema can be altered by moving up and down by clicking the Up 

      and Down 
      arrow buttons respectively.

      When parent query returns multiple rows, input for child query can be specified as value at $OUT/employee/employee/3 from first or last row returned by parent query by using $First[<MapTo>] or $Last[<MapTo>] respectively, that is, as $First[$OUT/employee/employee/3] or $Last[$OUT/employee/employee/3]

Miscellaneous Configurations
Request Level Post Processing Query

Post processing query configuration under Child Queries executes once for every execution of parent query.

Request level post processing query is similar to post processing query with respect to input / output and child queries. However:

  • Request level post processing query executes once for each request (input message) after all configured queries are executed, even when multiple queries are configured.
  • Request level post processing query has no parent query.

Steps to configure request level Post Processing query:

  1. Select Advanced check box under SQL Statements section.


    Figure 111: Advanced view showing Post Processing

  2. Right-click Post Processing and navigate to Add Query > <query as required>


    Figure 112: Navigating to choose the required statement

  3. A query builder is launched. Refer to appropriate section based on the query that has to be configured.
Adapter Mode

Adapter mode can be selected from the Adapter Mode drop-down list in SQL Configuration panel as shown below.


Figure 113: Adapter Mode

Publish Results: Component waits for input message and executes when an input message is received.
Scheduler: Component is scheduled and will have no input port. Scheduler configuration can be specified in Scheduler Configurations panel.

Output Options
  1. Select the advanced check box under SQL Statements section and select Execution node.


    Figure 114: Selecting Execution node

  2. Go to Options tab below SQL Statement Details section.
    1. Select Send output immediately after query execution to send output of each configured query in a separate message.
    2. Select Send output after executing all other queries to combine and send output of all queries in one message (as long as total response size does not exceed Max Response Size in Advanced Configuration).


      Figure 115: Options on Execution for sending output
    Post Processing Execution
  1. Check advanced check box under SQL Statement Details and select any top level query node.


    Figure 116: Options on configured query for post processing query execution

  2. Select Send Output check box if the result of the selected query has to be sent in output message, else clear the checkbox.
  3. When response size of a query exceeds Max Response Size in Advanced Configuration, multiple responses are sent for each request. Select Execute post processing after each send operation if configured query level post processing query has to executed once for each output message sent, else select Execute post processing after all send operations.
    Example:
    If a select statement returns 500 rows and Max Response Size in Advanced Configuration is configured as 200 rows. A post processing query, if defined, executes 3 times if Execute post processing after each send operation is selected, else it is executed once.
Advanced Configuration


Figure 117: Advanced Properties

Maximum Response Size

The maximum number of rows to be added in a response and hence the records that each output message can contain.
For example, if a query returns 900 records, and Maximum Response Size is set as 200, then for each request there are 5 responses of which 4 responses contain 200 records each and last response contains 100 records.

Use Batching

Determines whether batching should be used or not. Batching should be used only for insert, update or upsert. After Row commit mode cannot be used when batching is used. The size of the batch can be specified by the property Batch Size.

Batch Size

The Batch size is used for batching. It indicates number of operations of main query that have to be performed in single batch. The value cannot be less than 0. If it is 0, all operations are performed in a single batch. This is valid when the property Use Batching is selected.

Use External Transactions

Determines whether External Transactions should be used or not. When this property is set to "true", the value of the next property, that is, Commit Mode, gets automatically changed to "Transaction" and turns uneditable (this value is otherwise not available under Commit Mode; the possible values are explained in the next section).

Use the below commands effectively:

  • BEGINTRANSACTION and ENDTRANSACTION to begin and end the external transactions respectively.
  • ROLLBACK command will rollback the transactions done upto that point in the current transaction, but this command will not end the current transaction.
  • DB component commits the database operations done in current transaction and ends the transaction on ENDTRANSACTION command.
  • If the commands: ENDTRANSACTION or ROLLBACK, or any other commands are used before BEGINTRANSACTION command, then the component throws an exception of type "INVALID REQUEST ERROR".

When this property is set to "true",

  • Connection pooling should be enabled and number of connections (Max Pool Size) should be set to 1. This is required since a commit or rollback should be done on the same connection object and having more than one connections will not gaurantee same connection received everytime especially if there are multiple sessions enabled on input port resulting in undesired behavior. Even when the connection pool size is set to 1, multiple connections are maintained. Different connections will be distinguished based on the value of the message property ConnectionID. So this property can be used to correlate different messages to transactions.
  • Adapter mode cannot be set to "Scheduler Mode".

To configure the DB component for using External Transaction, refer Using External Transaction for DB Component section.

Commit Mode

Granularity of transaction is determined by the value specified against Commit Mode when Auto Commit is set to no in MCF panel.

Commit Mode

Granularity / Behavior

Auto

Request database is automatically committed by JDBC driver implicitly when an operation is performed on the database.

After Document

Request – Database is committed after all the queries in the request are executed.

After Row

Query Database is committed after each top level query is executed for one input part in the input document. If there are any nested queries, commit is performed after the nested queries are executed. This is not relevant when Use Batching is selected.

After Batch

Request Database is committed after executing n top-level queries when n is the batch size. If there are any nested queries, commit is performed after the nested queries corresponding to top-level are executed. This mode is visible only when Use Batching is selected.

BasedOnInput

Commit is only done when commit instruction is received on the input port.

While using this commit mode, connection pooling should be enabled and number of connections should be set to 1. This is required since a commit or rollback should be done on the same connection object and having more than one connections will not guarantee same connection received every time especially if there are multiple sessions enabled on input port resulting in undesired behavior. Even when the connection pool size is set to 1, multiple connections are maintained. Different connections will be distinguished based on the value of the message property ConnectionID. So this property can be used to correlate different messages to transactions.

AfterEachOutput

Request Database is committed after sending every output message. If this commit mode is selected then the last message from the component (in case of multiple messages for single request) doesn't contain the property "CLOSE_EVENT" set to true.

BeforeEachOutput

Request Database is committed before sending every output message. If this commit mode is selected then the last message from the component (in case of multiple messages for single request) doesn't contain the property "CLOSE_EVENT" set to true.

If an exception / error (which does not require creating new connection like request processing) occurs during the execution, then the action is taken based on the value for property Database action on Exception. If this action is set to Rollback, then a rollback is issued and all queries performed after last commit (based on commit mode) will be rolled back, that is, they will not take any effect. If this action is set to Commit, then all the queries except to the query that resulted in exception, performed until the exception are committed to database.

If a connection error occurs, then it will be equivalent to the rollback action on exception since commit should be performed on the same connection which executed the queries. Since the connection is no longer present, a new connection is created and all the uncommitted transactions are lost.

Example 1:

XML
<?xml version="1.0" encoding="UTF-8"?>
<ns1:SQL_CFG_1 xmlns:ns1="http://www.fiorano.com/fesb/activity/DB1/Request" id="7590437537112108032">
   <ns1:insert>
      <ns1:IDNO>401</ns1:IDNO>
   </ns1:insert>
   <ns1:insert>
      <ns1:IDNO>402</ns1:IDNO>
   </ns1:insert>
   <ns1:insert>
      <ns1:IDNO>403</ns1:IDNO>
   </ns1:insert>
   <ns1:insert>
      <ns1:IDNO>404</ns1:IDNO>
   </ns1:insert>
</ns1:SQL_CFG_1>
  • After Document – commits all 4 inserts at once
  • After Row – commits one insert at a time when use Batching is not selected
  • After Batch – commits after 2 inserts, if batch size is 2 and useBatching is selected
  • BasedOnInput –The transaction will be committed if the following message is received on input port
    <ns1:SQL_CFG_1 xmlns:ns1=\"http://www.fiorano.com/fesb/activity/DB1/Response\" id=\"-4393189459883103232\"><ns1:COMMIT/></ns1:SQL_CFG_1>
    It will be rolled back if the following message is received on input port:
    <ns1:SQL_CFG_1 xmlns:ns1=\"http://www.fiorano.com/fesb/activity/DB1/Response\" id=\"-4393189459883103232\"><ns1:ROLLBACK/></ns1:SQL_CFG_1>
  • AfterEachOutput - commits after sending every message, if Maximum Response Size is set to 1 there will be 4 message from the DB component each contains single output record.
  • BeforeEachOutput - commits before sending every message, if Maximum Response Size is set to 1 there will be 4 message from the DB component each contains single output record.

Example 2:
If we have a insert query q1 and a nested update query nq1 and let us assume the input has details for 4 inputs is as shown below:

XML
<?xml version="1.0" encoding="UTF-8"?>
<SQL_CFG_1>
   <q1>
      <col1>col1value1</col1>
      <col2>col2value1</col2>
      <nq1>
         <ncol1>ncol1value1</ncol1>
         <ncol2>ncol2value1</ncol2>
      </nq1>
   </q1>
   <q1>
      <col1>col1value2</col1>
      <col2>col2value2</col2>
      <nq1>
         <ncol1>ncol1value2</ncol1>
         <ncol2>ncol2value2</ncol2>
      </nq1>
   </q1>
   <q1>
      <col1>col1value3</col1>
      <col2>col2value3</col2>
      <nq1>
         <ncol1>ncol1value3</ncol1>
         <ncol2>ncol2value3</ncol2>
      </nq1>
   </q1>
   <q1>
      <col1>col1value4</col1>
      <col2>col2value4</col2>
      <nq1>
         <ncol1>ncol1value4</ncol1>
         <ncol2>ncol2value4</ncol2>
      </nq1>
   </q1>
</SQL_CFG_1>
  • When commit mode is Auto, 8 commits are done by JDBC driver implicitly, one for each of the inputs for q1 and nq1. If any error occurs when executing nq1 from 3rd input (having value 'ncol1value3' and 'ncol2value3'), then, only the 3rd input nested query will not be present in database irrespective of type of exception and the value for Database action on Exception.
  • When commit mode is AfterDocument, only 1 commit is done by DB component after processing all inputs for q1 and nq1. If any error happens when executing nq1 from 3rd input (having value 'ncol1value3' and 'ncol2value3'), then, if the Database action on Exception is set to commit, then the inputs for first, second and third 'q1' and the inputs for first and second 'nq1' are committed. If the Database action on Exception is set to rollback, nothing will be committed for the entire request.
  • When commit mode is AfterRow, 4 commits are done, this happens after each q1 and nq1 pair are committed successfully to the database. If any error happens when executing nq1 from 3rd input (having value 'ncol1value3' and 'ncol2value3'), then, if the Database action on Exception is set to commit, then the inputs for first, second, third and fourth 'q1' and inputs for first, second and fourth 'nq1' are committed. If the Database action on Exception is set to rollback, then the inputs for first, second and fourth 'q1' and the inputs for first, second and fourth 'nq1' are committed.
  • When commit mode is BasedOnInput, any action will be taken based on input sent. This option is usually used when a commit should be done after processing more than one message.
  • When the commit mode is AfterBatch, commits are done after processing 'n' inputs from input message where 'n' is the value mentioned against Batch Size property. If the Batch Size is 0, then it will be same as AfterDocument. So in the Example 2, if the batch size is 2, then only two commits are done. This happens after first and second 'q1', 'nq1' pairs; and second after third and forth 'q2', 'nq2' pairs. If any error occurs when executing 'nq1' from 3rd input (having value 'ncol1value3' and 'ncol2value3'), then, if the Database action on Exception is set to commit, then the inputs for first, second and third 'q1' and inputs for first and second 'nq1' are committed. If the Database action on Exception is set to rollback, then the inputs for first and second 'q1' and inputs for first and second 'nq1' are committed.
  • When commit mode is AfterEachOutput, if the Maximum Response Size is set to n, then the number of commits will be: 4 commits if n=1, 2 commits if n=2or 3, 1 commit if n is greater than or equal to 4. Commits will be happened after sending every message. If Maximum Response Size is set to 4 and any error happens when executing nq1 from 3rd input (having value 'ncol1value3' and 'ncol2value3'), then, if the Database action on Exception is set to commit, then the inputs for first, second, third and fourth 'q1' and inputs for first, second 'nq1' are committed, if the Database action on Exception is set to rollback, then the inputs for first, second and fourth 'q1' and the inputs for first, second 'nq1' are rollbacked. If any exception occurs while executing a third input of q1 then DB component will not process the fourth input.
  • When commit mode is AfterEachOutput, if the Maximum Response Size is set to n, then the number of commits will be: 4 commits if n=1, 2 commits if n=2or 3, 1 commit if n is greater than or equal to 4. Commits will be happened before sending every message. If Maximum Response Size is set to 4 and any error happens when executing nq1 from 3rd input (having value 'ncol1value3' and 'ncol2value3'), then, if the Database action on Exception is set to commit, then the inputs for first, second, third and fourth 'q1' and inputs for first, second 'nq1' are committed, if the Database action on Exception is set to rollback, then the inputs for first, second and fourth 'q1' and the inputs for first, second 'nq1' are rollbacked. If any exception occurs while executing a third input of q1 then DB component will not process the fourth input.
Add Response GUID

When checked, an additional attribute id – if present in input message on SQL_CFG_1 element, is set onto all output messages for that particular request. If the input message does not contain id attribute, a unique value for each request is generated and set on all output messages for that particular request.

id attribute value can be used to map request with all responses or responses for a particular request.

Generate response for no selected records

When all queries fail to return any data, an empty message is generated if this property is checked else there is no response message coming out.

Example: Assume a DB adapter is configured to get data from tables – table1 and table2 and both table do not have any data in them. If this property is not checked, there is no message from adapter, else following message appears: <SQL_CFG_1/>

Generate result sets for queries returning no records

When one of the queries does not return any results, an empty element is generated if this property is checked; else it is excluded from result.

Example: Assume a DB adapter is configured to get data from tables – table1 and table2 and table1 has some data but table2 does not have any data in it.
If this property is not checked there output is:
<SQL_CFG_1>
<table1>
…..data here…..
</table1>
</SQL_CFG_1>
else following message comes out
<SQL_CFG_1>
<table1>
…..data here….
</table1>
<table2/>
</SQL_CFG_1>

Do not execute nested queries for no resultset

When set to 'true', nested queries are not executed if parent query returns an empty result set. This property is honored only when Generate result sets for queries returning no records property is set to 'true'.

Validate Connection using Dummy Table

Database connectivity, in case of SQL Exception, is validated by querying a dummy table (created for this purpose alone). Value specified against Dummy Table Name is used as the table to query for validating connection failure.
While creating a connection to Database:

  • If this option is checked and a table name is specified against Dummy Table Name, a table with name as value specified against Dummy Table Name is created using the following SQL statement:
    CREATE TABLE <DUMMY TABLE NAME>
  • If a table with this name already exists, then that table is used for validation
  • If a table with this name does not exist and an exception occurs while creating dummy table, then table with this name should be manually created, else any exception is treated as a connection failure exception
  • If this option is checked and table name is not specified against DUMMY TABLE NAME connection creation fails
  • When a SQL exception occurs while executing any query, if this option is checked, connection is validated by executing:
    SELECT COUNT(*) FROM <Start wrap character><DUMMY TABLE NAME><End wrap character>
Dummy Table Name

Name of the table which should be queried to validate connection when a SQL exception occurs while executing any query

Treat zero update count as Exception

For queries returning an update count – insert or update – an update count of 0 is treated as an exception if this option is checked, else the query execution is assumed to be successful.

This should definitely be checked when an upsert query is being used.

Disable Exception Handling for zero update count

This property is used when the property 'Treat zero update count as Exception' is true. If this property is true, exception handling will not be done for NO_ROWS_UPDATED exception.

Enable Native Format

Sends/accepts binary data contained serialized objects. This option should be used only in case where the output format and input format of data is same (that is similar XSDs if this option is not checked)

Example: In case of database synchronization where data read from one table on a database is inserted without any transformation into exactly same table on a different database, check this option. This option provides better performance, since additional transformation is not required.

Treat empty node in input XML as null

Empty nodes in input XML (for example, <empno/>) implies corresponding column value is treated as a null value if this option is checked and treated as empty string value otherwise

Database action on Exception

When auto commit is not turned on and an exception occurs database transaction is committed if this option is checked and rolled back otherwise. This option provides atomicity for transactions when auto commit is not turned on.

Example: Consider a request containing 10 instances of an insert query is to be executed such that either all 10 queries are executed or none of them have to be executed. To achieve this, set Auto Commit to false in MCF panel, Commit Mode to After Document and Database action on Exception to false.

Cleanup database objects after every request

Set the value to 'true' if the database objects need to cleanup after every request.

This is a time consuming action. Prefer this option only if there is a possibility of facing out of memory exceptions while processing.

Enable CSV Output

Select Enable CSV Output property to represent the output of the queries in CSV format.

CSV output is required when all the queries in the instance are either 'Select' queries or 'Stored Procedure' queries which returns single result set.


Figure 118: Enable CSV Output option in Advanced Properties panel

After enabling this property, the below parameters becomes editable. These separators, which act as punctuation in the CSV output,can be customized by providing the respective values:

  • Field Separator
    The value of Column Separator to be used in CSV output.
  • Line Separator
    The value of Row Separator to be used in CSV output.
  • Wrap Character
    The specified character is used to wrap each field that is returned when a query is processed. 

    • This character does not enclose the header(s).
    • This field is not mandatory.

Example: 

When separators used are:

  • Field separator → ;
  • Line separator → ,
  • Wrap Character → "

then the output will be like:

"13579008642";"john";"hyd","124";"joseph";"bang",

Append Header

Append Header option is used to append or not to append column name header to the query responses.


Figure 119: Append Headers option in Advanced Properties panel

The three options available are described below:

  • Append Headers for all messageIf a query returns more records than the Maximum Response Size specified, by selecting Append Headers for all messages, column name headers will be appended in the beginning of all the responses.
  • Append Headers for first message: Column name headers will be appended to the first response only.
  • Do not append headers: Column name headers will not be appended in the response.

The legit values for the Field Separator, Line Separator and Wrap Character includes all the UNICODE characeters ( including-"\n" ,"\t" etc.).

Using Named Configurations

Refer "From CPS of the component" section in the Defining Named Configuration and utilize EPLCM page.

Input Schema

The Input Schema is auto generated based on the Database Configuration provided. Below figures show a sample Input schema:


Figure 120: Input Schema


Figure 121: Input Schema (cont...)

When the property Use Connection details from input is chosen, an additional element ConnectionFactorySettings is added to the input schema, as shown in the figure. Properties that are used to create the connection are present under this element.


Figure 122: Input schema with ConnectionFactorySettings

Output Schema

The output schema is auto-generated based on the configuration provided. Below figure shows a sample Output Schema.


Figure 123: Output schema

Use AutoCommit for configuring stored procedure

Stored procedures have to be executed when configuring to generate the output structure based on the result set returned. Enabling this property 

Validate Input

This property determines whether the input message has to be validated against the schema defined on the input ports.

Refer to the Validate Input section in the Common Configurations page.

Cleanup resources (excluding connection) after each document

If enabled, objects that are not connection-related are not destroyed and are reused for each request.

Refer to the respective section in the Common Configurations page.

Target Namespace

To avoid the clash of elements from different schema, the schema generated by the component use the value provided for this property to compute the namespace for input or output schema.

Refer to the Target Namespace section in the Common Configurations page.

Monitoring Configuration

When monitoring is enabled, it publishes USER_EVENTs containing various statistics to FPS_USER_EVENTS_TOPIC at the configured intervals of time.

Refer to the Monitoring Configuration section in the Common Configurations page.

Elements to Decrypt

Select elements to decrypt in the input.

Elements to Encrypt

Select elements to encrypt in the output.

For more details regarding the Elements to Encrypt/Decrypt, refer to the Configuring Components for Message Encryption property.


Functional Demonstration

Scenario 1

Executing multiple queries using a DB component: The given scenario executes a select query and if successful executes an update query which changes the e-mail address of the same record which was selected.

Configure the DB component as described in the Configuration and Testing section and use feeder and display component to send sample input and check the response respectively.


Figure 124: Demonstrating Scenario 1 with sample input and output

Use Case Scenario

Scenario 1

In a database replication scenario, updates to one database need to be monitored and subsequently updated in another database.


Figure 125: DB replication demonstration

The event process demonstrating this scenario is bundled with the installer.

Documentation of the scenario and instructions to run the flow can be found in the Help tab of flow when open in Studio.

Scenario 2

In DB transaction support scenario, transactions can be done across multiple steps in an event process.

The event process demonstrating this scenario is bundled with the installer.

Documentation of the scenario and instructions to run the flow can be found in the Help tab of flow when open in Studio.

Scheduling

Scheduling helps to execute the configured query at regular intervals for the specified number of times.

In the DB component, scheduling cannot be directly enabled from the Scheduler Configurations panel,  whereas it has to be enabled in the SQL Configuration panel by changing the Adapter Mode value from 'Publish Results' to 'Scheduler'. The scheduling interval and rate has to be configured in the Scheduler Configurations panel.

Adapter Mod is set to 'Scheduler' by default when Monitor Table option is chosen.


Figure 126: 'Scheduler' Adapter Mode option in SQL Configuration panel

Useful Tips

  • Configuring DB adapter to read Ms Office 2007 excel sheet (.xlsx) :
  • To connect to MSSQL server, the following changes have to be done.
    1. sqljdbc_auth.dll must be loaded. This can be done by adding system property "java.library.path" and set it to the path of the directory which contains sqljdbc_auth.dll file. To resolve this file during configuration of component, navigate to Tools->Options->Service Wizard and add "java.library.path" system property.To resolve this file during component runtime add "-Djava.library.path=$directory_of_sqljdbc_auth.dll" to runtime arguments of the component.
    2. "integratedSecurity=true" should be appended to the database connection URL.
      Example : To connect to MSSQL 2005 the databse connection URL should be "jdbc:sqlserver:$server_host:$port;databaseName=$database_name;integratedSecurity=true"
    3. Username and password need not be provided additionally.
  • To connect to linked servers, enable DTC access on both machines, that is, local and remote, and do the following in both the machines:

  1. Go to Component Services > Computers > My Computer > Distributed Transaction Coordinator > Local DTC

  2. Right-click the Local DTC > Properties

  3. Go to Security Tab and enable the DTC access. Select all the Allow properties.


    Figure 127: Enabling the DTC access to connect to linked servers


To understand the microservice better, refer the below examples which demonstrate DB service features:


This property determines whether the input message has to be validated against the schema defined on the input ports.


JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.