Copyright © Cloud Software Group, Inc. All Rights Reserved

JDBC Query

Activity

The JDBC Query activity performs the specified SQL SELECT statement.

If this activity is not part of a transaction group, the SQL statement is committed after the activity completes. If this activity is part of a transaction group, the SQL statement is committed or rolled back with the other JDBC activities in the group at the end of the transaction. See TIBCO ActiveMatrix BusinessWorks Process Design for more information about creating groups for transactions.

If you want to override the default behavior of transaction groups for certain JDBC activities in a transaction group, you can check the Override Transaction Behavior field on the Advanced tab. This specifies that the activity is outside of the transaction and the SQL statement is committed when the activity completes, even if the activity is in a transaction group.

Configuration

The Configuration tab has the following fields.

Field	Global Var?	Description
Name	No	The name to appear as the label for the activity in the process definition.
Description	No	Short description of the activity.
JDBC Connection	Yes	Path to the shared configuration resource containing the JDBC connection information. See JDBC Connection for more information about JDBC resources.
SQL Statement	Yes	The SQL SELECT statement to perform in the database. You can use a wizard to build the query for the desired database. See Query Designer for more information about using the Query Designer wizard.
Timeout (sec)	Yes	Time (in seconds) to wait for the query to complete. If the query does not complete in the given time limit, an error is returned.
Maximum Rows	Yes	The maximum number of rows to retrieve. To retrieve all rows, specify 0.
Prepared Parameters	No	See Prepared Parameters for a description of this field.

Fetch Button

The Fetch button on this activity allows you to synchronize the activity with the contents of the database.

When you first configure a JDBC activity, you must click the Fetch button to retrieve the schema for the output result set before attempting to apply your changes with the Apply button or by saving your project. If you do not click the Fetch button before applying your changes, TIBCO ActiveMatrix BusinessWorks displays an error dialog prompting you to first fetch the output schema.

After you have configured your activity, the Fetch button is useful when you make a change to the database while editing a process definition containing this activity in TIBCO ActiveMatrix BusinessWorks. The Fetch button synchronizes with the database and changes the output schema, if necessary.

Prepared Parameters

If you want to replace static names in the SQL statement with process variables, you must edit the SQL Statement field and replace the variable items with a question mark (?). For example, if you want to specify a variable for the first column returned, you would specify the following SQL statement:

 

SELECT DEMO.ITEM.ITEM_ID FROM DEMO.ITEM

   WHERE DEMO.ITEM.ITEM_ID = ?

The ? replaces the value in the WHERE clause, and you can then provide input to the SQL statement from process data on the Input tab.

 

Editing the SQL statement disables the Design tab of the Query Designer wizard. Make sure you have designed the query you need before editing it and inserting question marks for process variables. If your question marks are in the WHERE clause, you can put those conditions into the Design tab in the Where fields of each column. This will allow you to keep the Design tab available and still have process variables in your query. Keep in mind, ? is not valid SQL syntax, so checking the syntax of a query that contains question marks will return an invalid character error in the Query Designer wizard.

When you add or remove question marks in SQL statements, a warning appears to the right of the Prepared Parameters field ("SQL/ Prepared Parameter Count Mismatch") to alert you to add or remove corresponding prepared parameters. Each prepared parameter corresponds to the question mark in the same position in the SQL statement. That is, the first prepared parameter in the list corresponds to the first question mark, the second prepared parameter in the list corresponds to the second question mark, and so on. The warning is for informational purposes only, you must make sure the parameters in this field correctly correspond to the SQL statement.

You can optionally specify names for each prepared parameter. By default, the prepared parameters are named Prepared_Param_1, Prepared_Param_2, and so on.

You must supply the datatype of each parameter to the SQL statement, and this datatype is used in the input schema for the statement. You can use the up or down arrow keys to move a specified datatype to the previous or next parameter. You can also select a contiguous set of parameters to move them up or down, or delete them with the delete button.

At run time, any parameters you place in the SQL statement are replaced with the mapped values for those parameters on the Input tab.

Any mappings or expressions you create on the Input tab are not updated when you move or delete prepared parameters. You must manually update the Input tab after changing the prepared parameter field.

Advanced

The Advanced tab has the following fields.

Field	Global Var?	Description
Override Transaction Behavior	Yes	Overrides the default behavior of a transaction group. If this activity is in a transaction group, the activity is normally committed or rolled back with the other transactional activities. If this check box is checked, this activity is not part of the transaction group and is committed when it completes. Checking this option uses a separate database connection to perform the activity and commit the SQL statement.
Use Nil	No	Specifies whether NULLs are represented as optional schema elements or whether each item that can contain a NULL has sub-items. For example, in the following schema, the NAME column can contain NULLs. If Use Nil is unchecked, the NAME element appears with a ? indicating it is optional (and if the column is NULL, the schema element is not included for that row). If Use Nil is checked, the NAME element is not optional, and it has two sub-elements, @nil and text(). The @nil element indicates whether the column value for the row is NULL, the text() element contains the column value when the column is not NULL.
Interpret Empty String as Null	No	Specifies how empty strings in the activity’s input elements should be handled. When the field is checked, nulls are sent to the database where empty strings are supplied. When the field is unchecked, empty strings are treated as zero-length strings. Whether you use this field or not, you can still use XPath to set input elements explicitly to null. Note: Many databases treat empty strings and nulls as the same, so this field will not affect how the database interprets empty strings.
Process In Subsets	No	Checking this field specifies that you would like to process the result set in smaller batches rather than processing the entire result set at once. When this field is checked, the subsetSize input element appears to allow you to specify the size of each batch of records you want to process. Also, the lastSubset output element appears and is set to true when the last batch of records is being processed. When this field is unchecked, the entire result set is returned. See Fetching Subsets of the Result Set for more information.

Input

The input for the activity is the following.

Input Item	Datatype	Description
ServerTimeZone	string	Specifies the time zone for the database server. See Time Zone Construction for more information about how time zones are constructed using this field. You can use the Constants tab in the XPath Formula Builder to supply a valid time zone name. The list of time zone names is generated from java.util.TimeZone.getAvailableIDs(). See Time Zone Names for the list of valid time zone names.
Prepared_Param_* (name corresponds to name specified in the Prepared Parameters field on the Configuration tab)	varies	The list of prepared parameters supplied in the SQL statement. Prepared parameters allow you to substitute process data for items in the SQL statement. See the description of the Prepared parameters field on the Configuration tab.
subsetSize	integer	This element is only available when the Process In Subsets field on the Advanced tab is checked. When that field is checked, records are processed in batches and this element specifies the number of records to process in each batch. This allows you to process smaller batches of rows instead of retrieving one large result set. This element must be used in conjunction with a Repeat Until True loop group for processing the result sets. See Fetching Subsets of the Result Set for more information.
timeout	number	Time (in seconds) to wait for the query to complete. If the query does not complete in the given time limit, an error is returned.
maxRows	number	The maximum number of rows to retrieve. To retrieve all rows, specify 0.

Fetching Subsets of the Result Set

When the result set of a query is very large, you can retrieve subsets of the result set and iterate until the entire result set is processed. To retrieve subsets, you must use a Repeat Until True loop group to iterate until the entire result set is processed. Use the following procedure as a guideline for creating your Repeat Until True loop group.

1.	Create a JDBC Query activity.

2.	Specify the fields on the Configuration tab and create a query that returns multiple rows.

3.	Check the Process In Subsets field on the Advanced tab.

4.

Set the subsetSize input item for the JDBC Query activity to the number of records you want to process for each execution of the loop. You could specify a constant, or you could use a global variable to store the value. Using a global variable has the advantage that you can change the value of the variable at deployment time. For example, create a global variable named BATCH_SIZE, then specify the chunkSize input element as $_globalVariables/pfx:GlobalVariables/BATCH_SIZE.

5.	Select the JDBC Query activity and click the group icon on the toolbar to create a group containing the activity. See TIBCO ActiveMatrix BusinessWorks Process Design for more information about working with groups.

6.	Select Repeat Until True as the Group action, and specify an index name (for example, i).

7.	The loop should exit when the entire result set has been consumed. The output element lasSubset is a boolean that is set to true when the last subset of records is being processes. For example, the condition for the loop could be set to the following:

 

   $JDBCQuery/resultSet/lastSubset = "true"

The procedure above is a general guideline for creating a loop group for processing a large set of records. You may want to modify the procedure to include additional processing of the records, or you may want to change the XPath expressions to suit your business process.

See the examples included in the TIBCO ActiveMatrix BusinessWorks installation directory for an example of processing subsets of a larger result set.

Output

The output for the activity is the following.

Output Item	Datatype	Description
resultSet	complex	The root class for the output of the JDBC Query activity. This class contains all output items for the activity.
Record	complex	The result of the database query. The contents of the Record element depends upon the columns returned by the query.
lastSubset	boolean	This element only appears when the Process In Subsets field on the Advanced tab is checked. This field is set to true when the current subset of records being processed is the last subset of records. You can use this element to create an XPath expression to determine when a Repeat Until True loop that is processing records in batches should stop processing. For example:   $JDBCQuery/resultSet/lastSubset = "true"
Elapsed Time	long	This field displays the time taken for the execution of the activity in milliseconds. This is an optional element that appears only when the property java.property.bw.activity.output.stats.elapsedTime.JDBCQueryActivity is set to true in designer.tra. For run-time, set the property to true in the bwengine.tra file. By default, the property is set to false. If set in the design-time, this property must be carried forward to the run-time; else it can cause run-time errors. Enabling this property causes additional performance overhead. Hence, it is recommended that you use this property only when this information is to be consumed by some other ActiveMatrix BusinessWorks activity. Also, it is recommended that this property be used only in a non-production environment as a diagnostic tool and the property be turned off at run-time. You can turn off the property and not calculate the elapsed time at run-time by setting the property java.property.bw.activity.output.stats.elapsedTime.turnoff in the bwengine.tra file. However, note that setting this property will turn off the calculation of elapsed time at run-time for all the JDBC activities.

Error Output

The Error Output tab lists the possible exceptions that can be thrown by this activity. See TIBCO ActiveMatrix BusinessWorks Error Codes for more information about error codes and corrective action to take.

Exception	Thrown When...
JDBCDriverLoadingException	An error occurred when loading the specified JDBC driver.
InvalidSQLTypeException	A parameter’s datatype does not match the datatype of the table column.
JDBCConnectionNotFoundException	An error occurred when attempting to connect to the specified database.
DuplicatedFieldNameException	A duplicate field name was specified.
InvalidTimeZoneException	An invalid timezone was specified. See Time Zone Names for a list of valid timezones.
JDBCSQLException	A SQL error occurred.
JDBCTimedOutException	The operation has timed out.
LoginTimedOutException	A timeout has occurred while attempting to connect to the database.

 

Copyright © Cloud Software Group, Inc. All Rights Reserved