JDBC Query

JDBC Query is an asynchronous activity that 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.

If you want to override the default behavior of transaction groups for certain JDBC activities in a transaction group, you can select the Override Transaction Behavior checkbox 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.

Note: Do not use the Override JDBC Connection option in a transaction. Selecting this option in a transaction results in failure of the transaction.

General

The General tab has the following fields.

Field	Literal Value/Module Property	Description
Name	None	The name to be displayed as the label for the activity in the process.
JDBC Shared Resource	Yes	The path to the shared configuration resource containing the JDBC connection information. For more information, see JDBC Connection.
Timeout(sec)	Yes	The 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.

Description

Provide a short description for the JDBC Query activity.

Statement

The Statement tab has the following fields.

Field	Description
Statement	This is the SQL SELECT statement to perform in the database. You can use a SQL Builder wizard to build the query for the desired database.
Prepared Statement	The Prepared Statement contains the Parameter Name and Data Type fields. All the parameters defined in the Prepared Statement table are shown in the Input tab of the activity for the user to provide values. Each prepared statement corresponds to the question mark in the same position in the SQL statement. That is, the first prepared statement in the list corresponds to the first question mark, the second prepared statement in the list corresponds to the second question mark, and so on. The warning is for the purpose of information only. Ensure that the parameters in this field correctly correspond to the statement.

Field

Description

Statement

This is the SQL SELECT statement to perform in the database. You can use a SQL Builder wizard to build the query for the desired database.

Prepared Statement

The Prepared Statement contains the Parameter Name and Data Type fields. All the parameters defined in the Prepared Statement table are shown in the Input tab of the activity for the user to provide values.

Each prepared statement corresponds to the question mark in the same position in the SQL statement. That is, the first prepared statement in the list corresponds to the first question mark, the second prepared statement in the list corresponds to the second question mark, and so on. The warning is for the purpose of information only. Ensure that the parameters in this field correctly correspond to the statement.

You can optionally specify names for each prepared parameter. By default, the prepared statements are named Parameter1, Parameter2, and so on. Supply the datatype of each parameter to the SQL statement, and this data type is used in the input schema for the statement.

Run the Query Button.

Runs the JDBC Select statement entered in the Statement field and shows the results in the Results View. These results are persisted in the workspace.

SQL Button

Open Eclipse SQL Builder. For more information about using the SQL Query Builder to visually build queries, see Eclipse Documentation at eclipse.org.

Fetch Button.

Using the Fetch button on this activity, you can synchronize the activity with the contents of the database. When you first configure a JDBC activity, click the Fetch button to retrieve the schema for the output result set before applying your changes by saving your project.

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

Advanced

The Advanced tab has the following fields.

Field	Description
ThreadPool SharedResource	Specifies a queue of threads available to run a queue of tasks. Thread pools are used to improve performance when running large numbers of asynchronous tasks by reducing per task invocation overhead, provide a means of bounding, and managing the resources consumed when running a collection of tasks.
Loop Subset in Calling Processes	Note: This field can be activated on selecting the Process in Subsets checkbox. Use this field to configure the loop with lastSubset in another process. This another process calls the subprocess in which the JDBC Query activity is used. Provide the names of the processes as a comma-separated list. For example, mypackage1.myprocess1, mypackage2.myprocess2. If no input is provided for this field and the Loop Subset in Current Process is also not selected it means that the loop is in a direct call process of the subprocess. Either Loop Subset in Calling Processes or Loop Subset in Current Process can be selected at a time.
Loop Subset in Current Process	Note: This field can be activated on selecting the Process in Subsets checkbox. Select this checkbox to configure the loop with lastSubset inside the current process for activity. Either Loop Subset in Calling Processes or Loop Subset in Current Process can be selected at a time.
Override Transaction Behavior	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. This activity is not part of the transaction group and is committed when it completes, when this checkbox is selected. Selecting this option uses a separate database connection to perform the activity and commit the SQL statement.
Override JDBC Connection	Selecting this option overrides the default datasource connection, and allows for SQL queries to be run against the optional datasource. Note: Do not use the Override JDBC Connection option in a transaction. Selecting this option in a transaction results in failure of the transaction. After selecting this option, the sharedResourceName field is enabled under the Input > Data Source tab. From the sharedResourceName field, type the complete path with the name of the optional datasource, which can be found under the Resources folder. For example, if a JDBC shared resource is located at Resources > Package_Name > JDBC_SR_Name, then the value you enter should be `Package_Name.JDBC_SR_Name`. The value you enter in the XPath Expression field is case sensitive and must be a string. To avoid mapping issues, ensure that the optional datasource maintains a structure that is identical to the default database, and that both datasources are using the same datatypes. Note: Transactions between multiple datasources are not supported.
Use Nil	Specifies whether NULLs are represented as optional schema elements or whether each item that can contain a NULL has subitems.
Interpret Empty String as Null	Specifies how empty strings in the activity’s input elements should be handled. Selecting this checkbox sends the nulls to the database where empty strings are supplied. Empty strings are treated as zero-length strings, when this checkbox is not selected. 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 does not affect how the database interprets empty strings.
Interpret Invalid XML Chars	Specifies how invalid characters should be handled. If the checkbox is clear and invalid characters are sent from the database, the JDBC Query execution fails. When the checkbox is selected and invalid characters are sent, TIBCO Cloud Integration runs it successfully.
Process In Subsets	Selecting this checkbox activates the Loop Subset in Calling Processes and Loop Subset in Current Process fields. This field provides the option to process the result set in smaller batches rather than processing the entire result set at once. When this checkbox is selected, the subsetSize input element is displayed for you to specify the size of each batch of records you want to process. Also, the lastSubset output element is displayed and is set to `true` when the last batch of records is being processed. Not selecting this checkbox returns the whole result set.
Override SQL Statement	Selecting this checkbox overrides the SQL SELECT statement specified on the Statement tab, and enables you to add a new SQL statement on the Input tab. Tip: To connect to another database that has a schema with the same table structure, select the Override JDBC Connection checkbox. After doing this, go to the Input tab, and specify a Shared Resource at sharedResourceName.

Input

The following is the input for the activity.

Input Item	Datatype	Description
ServerTimeZone	string	Specifies the time zone for the database server.
timeout	number	The 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.
SqlStatement	string	Displays after the Override SQL Statement checkbox on the Advanced tab has been selected. This is the SQL SELECT statement to perform in the database. You can use a SQL Builder wizard to build the query for the desired database. You can also use a module property here. Note: If you create a SQL statement, ensure that the new statement does not differ from the output schema specified on the Output tab. If the new SQL statement does not match the output schema, an error is generated at run time. The output schema is determined from the original SQL statement on the Statement tab.

Output

The following is the output of the activity.

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 on the columns returned by the query.

Fault

The Fault tab lists the possible exceptions generated by this activity.

Fault	Generated When..
`JDBCConnectionNotFoundException`	An error occurred when attempting to connect to the specified database.
`InvalidTimeZoneException`	An invalid time zone was specified.
`JDBCSQLException`	An SQL error occurred.
`LoginTimedOutException`	A timeout has occurred while attempting to connect to the database.
`InvalidSQLTypeException`	A parameter’s datatype does not match the datatype of the table column.
`DuplicatedFieldNameException`	A duplicate field name was specified.
`ActivityTimedOutException`	A timeout has been reached.