SQL Direct

The SQL Direct is an asynchronous activity that executes an SQL statement that you supply. With this activity you can build an SQL statement dynamically using other activities, then pass the SQL statement into this activity's input. You can use this activity to execute DDL SQL Statements like CREATE and DROP. You can also execute SQL statements that are not supported by other activities in the JDBC palette.

General

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.

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 Path to the shared configuration resource containing the JDBC connection information. For details, refer to JDBC Connection
Timeout 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.

Description

Provide a short description for the activity in this field.

Advanced

The Advanced tab has the following field.

Field Description
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 this check box is selected, 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.

Input

The following is the input for the activity.

Input Item Datatype Description
statement string The SQL statement you want to execute. You can use any process variables to build the statement dynamically, if required.
ServerTimeZone string Specifies the time zone for the database server.
timeout number Time (in seconds) to wait for the query to complete. If the query does not complete in the specified time limit, an error is returned.
maxRows number The maximum number of rows to retrieve. To retrieve all rows, specify 0.

Output

The following is the output of the activity.

Output Item Datatype Description
noOfUpdates string The number of updates performed by the statement specified on the Configuration 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.

unknownResultSet string The result of the database statement. The result element depends upon what is returned by the statement.

The result set is a list of zero or more rows with zero or more columns. Each column contains a name and a value element. You can use XPath expressions to retrieve the desired row and column from the result set. There could be multiple result sets returned by the statements executed by this activity.

Because of this, this output item is a repeatable list of result sets. The first item in the list of result sets corresponds to the result set returned by the first statement executed by this activity. The second item in the list of result sets corresponds to the result set returned by the second statement executed by this activity, and so on.

Fault

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

Fault Thrown When..
JDBCConnectionNotFoundException An error occurred when attempting to connect to the specified database.
InvalidTimeZoneException An invalid time zone was specified.
JDBCSQLException A SQL error occurred.
ActivityTimedOutException The operation has timed out.
LoginTimedOutException A timeout has occurred while attempting to connect to the database.