view enabled.
This document covers the following topics:
Database Connection Configuration
Insert Statement Configuration
Update Statement Configuration
Delete Statement Configuration
Select Statement Configuration
Stored Procedure Configuration
SQL Statement Details Configuration
Request Level Post Processing Query
Generate response for no selected records
Generate result sets for queries returning no records
Validate Connection using Dummy Table
Treat zero update count as Exception
The DB component is an all encompassing powerful component which can be used to configure simple and nested queries like 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 can also 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.
The following are some silent 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 an 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 Data types (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 section 3.3.3.3 of Fiorano SOA User Guide or Fiorano SOA Online Help.
Connection details are configured in the first panel, which is Managed
Connection Factory - MCF of Configuration Property Sheet -
CPS. Figure 3.6.101 illustrates the panel with expert properties view enabled.
Figure 3.6.101: Managed Connection Factory Panel
Database: Select the appropriate database in the Database property, the drop-down list all the supported databases as shown in Figure 3.6.102. If the required database is not listed, select Other as the database option.
Note: If a database is not listed in the drop-down list, this does not mean that the component will not work with the database. This only means:
Monitor table feature will not be supported for the database
Appropriate Driver and URL should be specified
Figure 3.6.102: 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).
Note: 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, e.g.: replace <hostname>, shown in Figure3.6.103, with appropriate IP address
User name: Name of the user to connect to database as
Password: Password for user
Figure 3.6.103: Driver, URL, User name, and Password Properties
Connection Properties: Any driver specific connection properties which may have to be passed while creating a JDBC connection should be provided against Connection Properties (shown in Figure 3.6.104). For example, fixedString=true uses FIXED CHAR semantics for string values in oracle.
Note: Please refer to documentation of driver that is being used for valid name-values for connection properties.
Figure 3.6.104: DB Connection Properties
Auto commit: Commit mode that should be used by the JDBC connection.
|
|
yes |
no |
|
Commit behavior |
Transactions are committed implicitly |
Transactions are committed explicitly |
|
Database update |
Happens instantaneously |
Happens when the commit is called explicitly |
|
Performance |
Low |
High (comparatively) |
|
Granularity of transaction |
Fixed. Every transaction is atomically committed |
User defined. Granularity can be defined by specifying appropriate value for Commit Mode in Advanced Properties panel in the SQL configuration wizard (explained later) |
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 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.
Note: If this value is set to 0, all the rows are returned in one turn.
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
Enable jdbc driver logging: Value yes implies that logging at the driver level should be enabled. This is used as a debugging option.
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. For example, for oracle and [ ] for excel.
Database object names are wrapped as shown below:
Start wrap character + object name + End wrap character
Start wrap character: Character which should be used before the object name
End wrap character: Character which should be used after the object name
SQL configuration details and advanced configurations are configured in the second panel (Interaction configurations) of CPS.
This section covers the following topics:
Insert Statement Configuration
Update Statement Configuration
Delete Statement Configuration
Select Statement Configuration
Click on 
next to SQL configuration property
(shown in Figure 3.6.105) to launch the wizard (Figure 3.6.106) which
allows configuring queries that have to be executed.
Figure 3.6.105: Interaction Configurations
Figure 3.6.106: SQL Configurations Panel
SQL Configuration panel allows configuring
multiple queries. To configure a query, click 
button
and select required type of query from the pop-up menu.
Figure 3.6.107: Adding a Query
Explanation for different types of queries is given in the following table:
|
Type of query |
Explanation |
|
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 |
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 is shown in the Figure 3.6.108:
Figure 3.6108: Table Selection Dialog
To select a database object, provide selection criteria (schema name pattern and object name pattern) in corresponding database object selection UI as shown in the Figure 3.6.109:
Figure 3.6.109: Table Selection Criteria
Schema/object name pattern may contain SQL wild cards:
% - matches an number of characters
_ - matches one character
Example: S% - means all object names starting with S, %S% - means all object names containing S, and _S% means all object names whose second character is S.
Schema name can either be typed or selected from drop-down list after clicking on fetch schemas button.
Figure 3.6.110: Selecting a Schema
Note: Select <none> to ignore schema while searching. Provide empty value to get objects without a schema.
Click on Refresh <object> (Refresh Table, for table selection) to fetch the list of objects matching the criteria specified. Result can be incrementally searched for appropriate value by typing in first few characters when the result tree is focus as shown in Figure 3.6.11.
Figure 3.6.111: Searching a Table
Note: Response time for fetching required objects depends on search criteria, narrower the search criteria faster the response.
Click on Add à Insert Statement to launch Insert Query Builder (shown in Figure 3.6.12)
Note:
Do not type in the text area before configuring the query.
Do any modifications only after all other configurations are done.
For user modified values, required input/output details (for example, data types) will not populated and these have to be configured manually.
Figure 3.6.112: Insert Query Builder
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.
2.
Click on 
(add database table) button
to launch Table Selection dialog.
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 3.6.113: Selected table added to easel with all columns
4.
Table can be changed by clicking 
(replace
selected table) button and removed by clicking 
(remove
database table) button.
5. If values are never to be inserted into a particular column, then that column can be unchecked (this requires column has a default value or supports null values) as shown in Figure 3.6.114.
Figure 3.6.114: 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.
Note:
If the value is a string value it should be wrapped in single quotes ( )
? indicates value is taken from input or from the output of another query where possible
Figure 3.6.115: Inserting constant value into a table
Insert statement is automatically generated
and shown in the text editor under SQL Statement Figure 3.6.116. The generated
SQL can be validated by pressing 
(check syntax)
button.
Note: This feature only checks for invalid tokens, it does not perform a complete syntax check
Figure 3.6.116: Generated insert query
7. Click Ok to close the dialog.
Behavior: Insert rows in configured table by selecting rows from another table.
1. Follow the steps from 1 to 6 as described in section Simple Insert Statement.
2. Select option SELECT Query against Insert values using as shown in Figure 3.6.117.
Figure 3.6.117: Option to insert values into a table using select query
3. Click on Wizard button to launch SELECT Query Builder.
4. Follow the steps as described in section Select Statement Configuration.
5.
Insert statement is automatically generated and shown in the text
editor under SQL Statement. The generated SQL can be validated
by clicking the (check syntax) button.
Note: This feature only checks for invalid tokens, it does not perform a complete syntax check
Figure 3.6.118: Generated query to insert values using select
6. Click Ok to close the dialog.
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 section Simple Insert Statement.
2. Click on the check box Exception Table. Another Insert Query Builder launches, this is the table in which the values are stored which raised exceptions.
Figure 3.6.119: Exception Table Selection
3. Configure inserting into exception table following steps from one of the previous Insert Statement sections based on the requirement.
4. Click Ok to close the dialog.
Click on Add à Update Statement to launch UPDATE Query Builder (shown in Figure 3.6.120)
Note:
Do not type in the text area before configuring the query.
Do any modifications only after all other configurations are done.
For user modified values, required input / output details (for example, data types) are not populated and have to configured manually.
Figure 3.6.120: Update Query Builder
Behavior: Update rows satisfying defined condition in configured table, with column values taken from 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.
2.
Click on (add database table) 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 3.6.121: Selected table added to easel with all columns
5.
Table can be changed by clicking on (replace
selected table) button and removed by clicking on (remove
database table) button.
6. Select the columns whose values have to be set (Figure 3.6.122 shows that NAME and AGE are selected for update)
Figure 3.6.122: Ignoring column for update
7. Selected columns are added under the SET tab.
Figure 3.6.123: Columns added to SET clause
8. Click on WHERE tab and select a column name on which where condition has to be applied.
Figure 3.6.124: Adding condition on column to WHERE clause
9. When selecting multiple columns for where condition, conditions can be combined using AND or OR under And/Or column.
Figure 3.6.125: Specifying multiple conditions for WHERE clause
10. Operator of choice can be selected from the drop-down list under Operator column.
Figure 3.6.126: Selecting operator for a condition
Figure 3.6.127: WHERE tab with conditions and operators selected
11. Constant values can also be set to columns that have to be updated (under SET tab) or for values in where condition (under WHERE tab).
12. 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.
Note:
If the value is a string value it should be wrapped in single quotes ( )
? indicates value is taken from input or from the output of another query where possible.
Figure 3.6.128: Specifying constant value for a column in SET clause
13. 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.
Note:
If the value is a string value it should be wrapped in single quotes ( ).
? indicates value is taken from input or from the output of another query where possible.
Figure 3.6.129: Specifying constant value for a column in condition for WHERE clause
14. 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 3.6.130: Specifying comparison between columns in condition for WHERE clause
15.
Update statement is automatically generated and shown in the text
editor under SQL Statement. The generated SQL can be validated by pressing
(check syntax) button.
Note: This feature only checks for invalid tokens, it does not perform a complete syntax check
Figure 3.6.131: Generated update query
16. Click Ok to close the dialog.
Behavior: Update a rows satisfying defined condition in 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.
Note:
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.
1. Configuring update statement following steps mentioned in Simple Update Statement.
2. Check UPSERT check box.
Figure 3.6.132: UPSERT Check Box
Click on Add à Delete Statement to launch DELETE Query Builder (shown in Figure 3.6.133)
Note:
Do not type in the text area under before configuring the query.
Do any modifications after all other configuration is done.
For user modified values required input / output details (e.g. data types) will not be populated and have to configured manually
Figure 3.6.133: Delete Query Builder
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.
2.
Click on (add database table) 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
Figure 3.6.134: Selected table added to easel with all columns
5.
Table can be changed by clicking (replace
selected table) button and removed by clicking (remove
database table) button
6. Specify condition which should be satisfied for deleting row under WHERE condition. Select a column name on which where condition has to be applied.
Figure 3.6.135: 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
Figure 3.6.136: Specifying multiple conditions for WHERE clause
8. Operator of choice can be chosen from the drop-down under Operator column.
Figure 3.6.137: Selecting operator for a condition
Figure 3.6.138: WHERE Tab 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 in where tab
Note:
If the value is a string value it should be wrapped in single quotes ( ).
? indicates value is taken from input or from the output of another query where possible.
Figure 3.6.139: 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 3.6.140: Specifying comparison between columns in condition for WHERE clause
11.
Delete statement is automatically generated and shown in the text
editor under SQL Statement. The generated SQL can be validated by pressing
(check syntax) button.
Note: This feature only checks for invalid tokens, it does not perform a complete syntax check.
Figure 3.6.141: Generated delete query
12. Click Ok to close the dialog.
Click on Add and select Delete Statement to launch SELECT Query Builder (shown in Figure 3.6.142)
Note:
Do not type in the text area under before configuring the query.
Do any modifications after all other configuration is done.
For user modified values required input/output details (Example: data types) will not be populated and have to configured manually.
Figure 3.6.142: Select Query Builder
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.
2.
Click on (add database table) 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 3.6.143: Selected table added to easel with all columns
5.
Table can be changed by clicking (replace
selected table) button and removed by clicking (remove
database table) button
6. To retrieve specific columns values from the table, check required columns to build a select query with specific columns. If no column is checked, then SELECT * is used. Select the columns in order in which they should appear they should appear in select clause.
Figure 3.6.144: Ignoring column for selection
7. Selected columns are shown under Columns tab. Check/Uncheck the check box in Output column against required column name to show/not show the corresponding column in the output XML.
For example, configuration in the following image generates IDNO in the output XML but does not generate NAME in output XML, though values for both IDNO and NAME are retrieved from the table.
Figure 3.6.145: Selecting columns for output XML
8. To define a column alias, provide the alias name under Alias column against required column name. Aliases are useful when the column name is not intuitive or too long. When an alias is specify output XML contains an element with defined alias name instead of the column name
Figure 3.6.146: Defining Column Alias
9. To return unique rows check DISTINCT
Figure 3.6.147: Distinct option to return unique values
10.
Select statement is automatically generated and shown in the text
editor under SQL Statement. The generated SQL can be validated by pressing
(check syntax) button.
Note: This feature only checks for invalid tokens, it does not perform a complete syntax check
Figure 3.6.148: Generated select query
11. Click Ok to close the dialog.
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 section Simple Select Statement.
2. Click on WHERE tab and select a Column name on which WHERE condition has to be applied.
Figure 3.6.149: 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.
Figure 3.6.150: Specifying multiple conditions for WHERE clause
4. Operator of choice can be selected from the drop-down list under Operator column.
Figure 3.6.151: Selecting operator for a condition
Figure 3.6.152: WHERE tab with conditions and operators selected
5. Constant values can also be set for values in WHERE condition (under WHERE tab).
6. 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.
Note:
If the value is a string value it should be wrapped in single quotes ( ).
? indicates value is taken from input or from the output of another query where possible.
Figure 3.6.153: Specifying constant value for a column in SET clause
7. 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 3.6.154: Specifying comparison between columns in condition for WHERE
8.
Select statement is automatically generated and shown in the text
editor under SQL Statement. The generated SQL can be validated by clicking
the (check syntax) button.
Note: This feature only checks for invalid tokens, it does not perform a complete syntax check.
Figure 3.6.155: Generated select query with filter
9. Click the Ok button to close the dialog.
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 section Simple Select Statement.
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, i.e. order by clause is added in the SQL statement as ORDER BY <column name> |
Figure 3.6.156: Selecting sorting order for column
An example of SQL statement with different sort orders is shown in the Figure 3.6.157.
Figure 3.6.157: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 3.6.158: SQL Statement with Sort Priority
4.
Select statement is automatically generated and shown in the text
editor under SQL Statement. The generated SQL can be validated by clicking
(check syntax) button.
Note: This feature only checks for invalid tokens, it does not perform a complete syntax check.
5. Click the Ok button to close the dialog.
Behavior: Retrieves data, after applying grouping conditions, from all columns or from selected columns in a configured database table.
Note: 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 8 as described in the section Simple Select Statement.
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 3.6.159: 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 3.6.160: Adding condition to HAVING clause
4. Select required columns under Tables.
Figure 3.6.161:Selecting required columns
5. Edit Select and HAVING clauses to apply appropriate grouping condition on selected columns.
Note: Editing Select and HAVING clauses should be last action before closing the dialog.
Figure 3.6.162: Generated select query with grouping
6. Click the Ok button to close the dialog.
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 section Simple Select Statement.
2.
To add second table, click on (add database
table) button to launch Table Selection dialog.
Note:
Multiple tables can be added by repeating this step.
Specify any conditions after selecting all required tables.
Figure 3.6.163: Selecting multiple tables
3. Add WHERE condition, described in section Select Statement with filter, to perform join on the tables. If no condition is specified, Cartesian product of rows in all selected tables is returned.
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 3.6.164: Generated SQL statement with join
5. To specify filtering, sorting or grouping conditions refer to sections above.
6. Click the Ok button to close the dialog.
Behavior: Executes a stored procedure and returns the result (returns return value or out parameter values).
Note:
Functions can also be configured
Stored Procedure/Function have 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 on Add à Stored Procedure to launch Stored Procedure dialog.
Figure 3.6.165: Stored Procedure Query Builder
2. Provide a name against Query Name.
3.
Click 
against Stored Procedure to launch
Procedure Selection dialog.
4. Select required procedure as described in section Object Selection.
Figure 3.6.166: Stored procedure details
5. Parameters and their configurations are automatically populated.
Note: Stored Procedures currently does not support User Defined Data types.
|
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. Select Execute to execute the stored procedure to create output structure when the Stored Procedure dialog is closed (shown in the Figure 3.6.167). If Execute is not selected, the output structure will not be defined and has to be manually defined.
Figure 3.6.167: Output structure generated for selected stored procedure
7. Click Ok to close the dialog.
This section covers the following topics:
Behavior: Monitors a configured table for any changes (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 (see section Database connection configuration). 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.
Click on Add à Monitor Table to launch SQL Creation Wizard (shown in Figure 3.168).
Figure 3.6.168: Monitor table wizard
Note: Do not use 
these
buttons.
2.
Click against Monitor Table
and choose the table to monitor (refer to section Object
Selection).
3. Select actions which have to be monitored.
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 when this option is checked.
Figure 3.6.169: Selecting table for monitoring
4.
For each action which has to be monitored, specify conditions which
filter changes to be notified, click on 
button (configure
expression to filter inserted records).
5. Define condition on required columns, similar to WHERE tab in Select Statement with filter section. Figure 3.170 shows configuring a condition send notification if a row is inserted with IDNO > 500.
Note: Do not set column value as? (Like in WHERE tab)
Figure 3.6.170: Specifying filter condition for monitoring
6. Click the Next button.
7. 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
Note: 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.
Note:
o This option should be used with caution as changing table definition might break other applications.
o TIF_RECORDID, TIF_OPERATIONTYPE and TIF_STATUS columns should be populated externally.
8. 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.
Figure 3.6.171: Selecting monitor option
9. In case of either monitor option, Enable Loop Detection modifies the monitored table to add an additional column TIF_SOURCE whose value should be NULL for notifications.
Note:
o This option should be used with caution as changing table definition might break other applications.
o TIF_RECORDID, TIF_OPERATIONTYPE and TIF_STATUS columns should be populated externally.
10. Click the Next button.
11. 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, 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.
Figure 3.6.172: Selecting polling option
12. Click the Next button.
13. To check SQLs which create required database objects required for monitoring, click View SQLs These SQLs are by default executed when Finish button is clicked.
14. To check SQLs which remove all database objects created for monitoring, click View Cleanup SQLs&
15. 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
16. Check Ignore SQL execution errors to finish the wizard even if some exceptions occur when executing SQLs to create database objects required for monitoring.
Note: If this is checked, appropriate database objects should be created by user.
17. Check Do not execute SQLs on Finish to finish the wizard without creating database objects required for monitoring.
Note: If this is checked, appropriate database objects should be created by user.
18. Click the Finish button.
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.
Figure 3.6.173: SQL Statement Details
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 3.6.174: Building output structure manually
Basic view of input tab is shown in Figure 3.6.175.
Figure 3.6.175: Input tab showing basic view of input structure
Check advanced check box to see advanced configuration details.
Figure 3.6.176: Input tab showing advanced view of input structure
Each of the columns in Input tab is explained below.
|
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. Note: 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 first or last value from list values: $First[<expression>]. $Last[<expression>] Note: 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. Note: Do not change this value. |
|
Java Type |
JDBC type which maps to Data Type. |
Basic view of output tab is shown in the Figure 3.6.177:
Figure 3.6.177: Output tab showing basic view of output structure
Check advanced check box to see advanced configuration details.
Figure 3.6.178: 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 |
1. Select a configured stored procedure under SQL Statements.
2. Click Edit to launch Query Builder in edit mode. This mode is same for all DML statements (Select, Insert, Update, Delete).
Figure 3.6.179: Editing configured SQL query
3. Make necessary changes in the SQL Statement.
4.
The changed SQL can be validated by pressing (check
syntax) button.
Note: This feature only checks for invalid tokens, it does not perform a complete syntax check
5. 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 respectively.
6. Click Ok to close the dialog.
1. Select a configured stored procedure under SQL Statements.
2. Click Edit to launch Query Builder for Stored Procedure and follow steps in section Stored Procedure Configuration.
Figure 3.6.180: Editing stored procedure
3. Check Execute check box 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.
Figure 3.6.181: Execute option to execute stored procedure
4. Click Ok to close the dialog box.
Select the query to remove and click Remove button.
Figure 3.6.182: Selecting query to be removed
1. Any configured can be tested from SQL Configuration panel. To test a query, select the query and click Execute button.
Figure 3.6.183: Selecting query to be tested
2. Specify Variable Value dialog opens.
Figure 3.6.184: Input parameters which require user values
3. Specify values for parameters which require user input (marked ? in the SQL statement) in the Parameters tab under Value column. All other columns are not editable.
Example: INSERT INTO "SCOTT"."EMP_ORIGINAL" (IDNO", "NAME", "AGE") VALUES (?, ?, ?)
Figure 3.6.185: 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.
For each configured query, different types of child queries can be configured. Different types of child queries are listed below:
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.
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.
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.
1. Configure any query.
2. Check advanced check box against SQL Statements.
Figure 3.6.186: Advanced option for SQL Statements
3. Right-click on the query, from the popup navigate to:
a. Add Nested Query à <query of interest> for nested query.
b. Define Failover Query à <query of interest> for failover query.
c. Add Post Processing Query à <query of interest> for post processing query.
Figure 3.6.187: Adding a child query
4. A query builder is launched. Refer to appropriate section based on the query that has to be configured. Configured query is shown as a child node to initial query.
Figure 3.6.188: Configured query is shown as a child
5. 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.
6. Child query can also take input from the result of parent query.
7. To configure child query to take input from the result of parent query
a. Select the child query.
b. View Input tab in SQL Statement Details panel.
c. Check advanced against SQL Statement Details.
d. In the MapTo column against the required column, click on the drop-down list to see a list of entries, one for each column in the parent queries result, as shown in Figure 3.189.
Figure 3.6.189: MapTo entries for result of parent query
e. From the drop-down list, select appropriate value. For example, Figure 3.6.190 shows that department number is the 8th field in the output of parent query.
Figure 3.6.190: Output of parent query
So select $OUT/employee/employee/8 to map the DEPTNO of parent query (employee) to input of child query (department_details)
Note: $OUT/employee/employee/8 is computed using proprietary formula and should not be modified
f. When parent query returns multiple rows, input for child query can be specified as value at $OUT/employee/employee/8 from first or last row returned by parent query by using $First[<MapTo>] or $Last[<MapTo>], that is, as $First[$OUT/employee/employee/8] or $Last[$OUT/employee/employee/8]
This section covers the following topics:
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 post processing query:
1. Check advanced check box against SQL Statements.
Figure 3.6.191: Advanced view showing Post Processing
2. Right-click on Post Processing and navigate to Add Query à <query of interest>
3. A query builder is launched. Refer to appropriate section based on the query that has to be configured.
Adapter mode can be selected from the Adapter Mode drop-down list in SQL Configuration panel as shown in Figure 3.6.192:
Figure 3.6.192: 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.
1. Check advanced check box against SQL Statements and select Execution node
Figure 3.6.193: Options on Execution for sending output
2. Go to Options tab.
a. Select Send output immediately after query execution to send output of each configured query in a separate message.
b. 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).
1. Check advanced check box against SQL Statements and select any top level query node.
Figure 3.6.194: Options on configured query for post processing query execution
2. Check Send Output check box if the result of the selected query has to be sent in output message, else uncheck.
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.
This section covers the following topics:
Generate response for no selected records
Generate result sets for queries returning no records
Validate Connection using Dummy Table
Treat zero update count as Exception
Figure 3.6.195: Advanced Properties
The maximum number of records 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 is 5 responses of which 4 responses contain 200 records each and last response contains 100 records.
Determines whether batching should be used or not. Batching should be used only for insert, update or upsert. 'After Row' commit mode cannnot be used when batching is used. The size of the batch can be specified by the property Batch Size.
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 |
|
After Document |
Request Database is committed after all the queries in the request are executed. |
|
After Row |
Query Database is committed after each of the queries is executed. This is not relevant when Use Batching is selected. |
|
After Batch |
Configured count of queries Database is committed after each batch (n) of queries are executed, where n is value specified against Batch Size when Use Batching is selected. |
|
BasedOnInput |
Database is committed/rolled back based on the message on input port. |
Example:
<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>
The Batch size when batching is used. 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.
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.
Note: id attribute value can be used to map request with all responses or responses for a particular request.
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/>
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>
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
Name of the table which should be queried to validate connection when a SQL exception occurs while executing any query
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.
Note: This should definitely be checked when an upsert query is being used.
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.
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
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.
The input schema is auto generated based on the configuration provided. For the configuration shown above, the schema would be
Figure 3.6.196: Input schema
The output schema is auto generated based on the configuration provided. For the configuration shown above, the schema would be
Figure 3.6.197: Output schema
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 setion Configuration and Testing and use feeder and display component to send sample input and check the response respectively.
Figure 3.6.198: Demonstrating Scenario 1 with sample input and output
In a database replication scenario, updates to one database need to be monitored and subsequently updated in another database.
Figure 3.6.199: DB replcation 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.
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.
In the DB component, scheduling cannot be directly enabled from the scheduling panel. Scheduling can be enabled in the SQL configuration panel. The scheduling interval and rate is determined in the scheduling panel. This is set to scheduler by default when Monitor Table option is chosen.
Figure 3.6.200: DB adapter Configuration Property Sheet SQL Configuration
It is recommended NOT to use JDBC-ODBC Bridge driver to connect to any RDBMS in a 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 section 3.3.3.3 of Fiorano SOA User Guide or Fiorano SOA Online Help.
Copyright © 1999-2008, Fiorano Software Technologies Pvt. Ltd. All rights reserved.
Copyright © 2008-2009, Fiorano Software Pty. Ltd. All rights reserved.
This software is the confidential and proprietary information of Fiorano Software ("Confidential Information"). You shall not disclose such Confidential Information and shall use it only in accordance with the terms of the license agreement enclosed with this product or entered into with Fiorano.
