SQL Direct

The SQL Direct activity executes a SQL statement that you provide. This activity allows you to build a SQL statement dynamically (using other activities), then pass the SQL statement into this activity’s input. This activity also allows you to execute SQL statements that are not supported by other activities in the JDBC palette. For example, DDL commands (for example, CREATE TABLE) are not available using any other activity.

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.
Timeout (sec)	Yes	Time (in seconds) to wait for the statement to complete. If the statement 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.

Field

The name to appear as the label for the activity in the process definition.

Description

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.

Timeout (sec)

Yes

Time (in seconds) to wait for the statement to complete. If the statement 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.

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

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

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.
statement	string	The SQL statement you want to execute. You can use any process variables to build the statement dynamically, if desired.
timeout	number	Time (in seconds) to wait for the statement to complete. If the statement 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.

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.

statement

string

The SQL statement you want to execute. You can use any process variables to build the statement dynamically, if desired.

timeout

number

Time (in seconds) to wait for the statement to complete. If the statement 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.

Output Item	Datatype	Description
noOfUpdates	integer	The number of updates performed by the statement. There could be more than one statement performed (for example, if a stored procedure is executed). Because of this, this item is a list of integers. Each item in the list corresponds to the number of updates performed by each statement. The first integer in the list is the number of updates performed by the first statement executed by this activity. The second integer is the number of updates performed by the second statement, and so on.
unknownResultSet	complex	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.
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.JDBCGeneralActivity 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.

The number of updates performed by the statement.

There could be more than one statement performed (for example, if a stored procedure is executed). Because of this, this item is a list of integers. Each item in the list corresponds to the number of updates performed by each statement. The first integer in the list is the number of updates performed by the first statement executed by this activity. The second integer is the number of updates performed by the second statement, and so on.

unknownResultSet

complex

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.

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

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.
JDBCConnectionNotFoundException	An error occurred when attempting to connect to the specified database.
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.

Exception

Thrown When...

JDBCDriverLoadingException

An error occurred when loading the specified JDBC driver.

JDBCConnectionNotFoundException

An error occurred when attempting to connect to the specified database.

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.