DB SQLExec2

This database tool allows for the execution of any SQL statement that does not return a result set. If used for queries or stored procedure calls that return result sets, you will not be able to retrieve the results. Use DB Query and DB Stored Procedure for stored procedure calls that return result sets instead of this tool.

This tool will optionally allow for the retrieval of the number of affected rows (INSERTs, UPDATEs, and DELETEs only).

Like the DB list tools, this tool will grab a connection from the cache (or create a new one if one is not available), perform the operation, then release the connection back to the cache. In other words, it is completely self-contained, which means you do not need to call DB Open & DB Get Connection prior to using this tool.

Inputs

IC Data Source

The name of the CIC (not ODBC) data source to use.

SQL Command

The SQL command to execute.

Timeout (seconds)

The time, in seconds, before this tool will return with a timeout error. Where supported by the RDBMS, the statement/query timeout on the server will also be set with this value.

Check Count of Affected Rows

If TRUE, a call will be made to retrieve the count of the number of rows affected by this command.

If you know the command does not affect any rows or produce a row count (or you don't care about the row count) set this to FALSE as an optimization.

Outputs

Affected Row Count

If the input "Check Count of Affected Rows" is TRUE, this will contain the row count; note that a row count of zero is possible in many situations (e.g. the where clause of an UPDATE does not specify any rows, the command does not produce a row count, etc.). If "Check Count of Affected Rows" is FALSE, or an error was encountered, this value will be set to -1.

Error Code

If the tool execution was successful, this will be set to zero; otherwise, it will be set to the native (RDBMS) error code, or in the case of an internal error (e.g. Notifier error) it will be set to an CIC-specific error code.

Exit Paths

Success

The operation was successful. Note that this doesn't mean that there were affected rows; it only means the SQL statement itself did not generate any errors.

No Rows

This path will only be taken if all three of the following conditions are met:

1. There were no errors

2. The input "Check Count of Affected Rows" is TRUE

3. The result did not affect any rows (or did not produce a row count).

Failure

There was a SQL or internal error generated while trying to execute this command. Check the IPDBServer.log for information on the error.