| Wiki Markup |
|---|
{scrollbar} |
Performs a data or database modifying SQL query against a JDBC or JNDI data source. The result of execution is a resultset containing number of rows affected and status, as detailed below. The query may contain references to parameters by enclosing the parameter in curly braces. For example update myTable set column = 'newvalue' where department = '{dept}' would replace {dept} with the value of the parameter named "dept".
Component Name: SQLExecute
...
Component Definition:
REQUIRED
JNDI Name - Name of the JNDI connection.
...
JDBC Driver - The JDBC driver to use when connecting to the data source.
JDBC Connection String - The connection string identifying the location of the data source.
JDCB User Name - User name to use when connecting to the data source.
JDBC Password - Password to use when connecting to the data source.OPTIONAL
Keep Connection Open _-- If selected the query results are retrieved from the data source as needed, otherwise the entire query results are brought over from the data source and cached in memory. It is recommended that you set this option with larger datasets. Never use this option if you are storing the resultset in the session
Query Definition - the update, create, alter or drop statement that you wish to execute. This component will execute more than one statement in a query definition.
OPTIONAL
force single statement (true\false) - if set to true, and there are multiple statements in the query definition, will force the query to be submitted to the server as one statement.This execution path should be used if the query has a semi-colon in the text of the SQL statement. This is a legitimate condition if there is (for example) a statement with a where-clause that has a semi-colon.
e.g.: UPDATE sometable SET somecolumn='val1;val2' WHERE somecolumn='val3;val4'
multi-statement separator (single character value) - defaults to ';". Allows you to change the statement separator when multiple sql statements are present
continue on exception (true\false) - if set to true, continue to execute following sql statements after an exception is thrown. This is useful when a database throws an exeception on a DROP TABLE statement because the table does not exist.
Note: Any parameter may be referenced in the query by enclosing the parameter in curly braces.
...
Result Set Name -- The name of the parameter in which to store the query results.
...
| title | TODO |
|---|
...
The results of this query will be a resultset that has two columns: the first for the number of rows affected, and the second for the status of the query execution, success or failed. If more than one statement is executed independently, then there will be as many rows in the resultset as were queries executed. For example, if you defined three update statements in the query definition, then there would be three rows in the resulting resultset, one for each statement executed.