JDBC Update

JDBC Update is an asynchronous activity that performs the specified SQL INSERT, UPDATE, or DELETE 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.

Note: Do not select the Override Transaction Behavior checkbox for a JDBC resource in a transaction group.

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.

The SQL statement is committed after the activity completes.

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 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.

Description

Provide a short description for the JDBC Update activity.

Statement

The Statement tab has the following fields.

Field Description
Statement This is an SQL INSERT UPDATE OR DELETE statement to perform in the database. You can use the SQL Builder wizard to build the query for the desired database.
Prepared Statement The Prepared Statement contains the Parameter Name and Datatype 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 informational purposes only. You must make sure the parameters in this field correctly correspond to the statement.

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.
Insert if record does not exist Select this checkbox to insert the record if it does not exist in the database.
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.

If you select this checkbox, this activity is not part of the transaction group and is committed when it completes. 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.

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.

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.

When the checkbox is not selected, empty strings are treated as zero-length strings. Whether you use this checkbox option 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 checkbox does not affect how the database interprets empty strings.

Batch Update

Selecting this checkbox signifies that you want to perform multiple statements by supplying an array of records as input to the activity where each record matches the prepared parameters for the statement. The statements are performed in one batch at the end of the activity’s execution.

If you do not select this checkbox, the expected input is the list of prepared parameters for the statement (no array of records). Some database drivers may not support batch updates. A JDBCSQLException is generated, if the database driver does not support batch updates.

Override SQL Update Statement

Selecting this checkbox overrides the SQL UPDATE statement specified on the Statement tab, and enables you to add a new SQL statement on the Input tab.

Note: 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
Record complex This field appears when the Batch Update checkbox is selected on the Advanced tab.
InsertStatement string This field appears when the Insert if record doesn't exist checkbox is selected on the Advanced tab.
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.
sharedResourceName string This field appears when the Override JDBC Connection checkbox is selected on the Advanced tab.
SqlUpdateStatement string

Displays after the Override SQL Update Statement checkbox on the Advanced tab has been selected. This is the SQL UPDATE 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 Update 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 Update statement on the Statement tab.

Output

The following is the output for the activity.

Output Item Datatype Description
noOfUpdates integer The number of updates performed by the statement specified on the General tab.

This element can also return -2 that indicates, in the event of a batch update, that the updates were successful, but it is unknown how many updates were performed.

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.
ActivityTimedOutException A timeout has been reached.