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||The path to the shared resource containing the JDBC connection information.
For details, refer to JDBC Connection.
|Timeout||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.|
The Advanced tab has the following field.
|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.
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||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.|
The following is the output of the activity.
|noOfUpdates||string||The number of updates performed by the statement specified on the
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.
The Fault tab lists the possible exceptions thrown by this activity. See Error Codes for more information about error codes and the corrective action to take.
|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||The operation has timed out.|