SQL Direct

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.

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.

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.
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
ThreadPool Sharedresource Specifies a queue of threads available to run a queue of tasks. Thread pools are used to improve performance when executing large numbers of asynchronous tasks by reducing per task invocation overhead, provide a means of bounding, and managing the resources consumed when executing a collection of tasks.
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 check box, 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 executed 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 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 is not supported.
Use Nil Specifies whether NULLs are represented as optional schema elements or whether each item that can contain a NULL has subitems.

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

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 can 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 generated by this activity. For more information about error codes and the corrective action to take, see the TIBCO BusinessWorks™ Container Edition Error Codes guide.

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