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.
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.|
The Advanced tab has the following field.
|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.
The following is the input for the activity.
|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.|
The following is the output of the activity.
|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.
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.
|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.|