WmDB. Executes the specified SQL statement. As an alternative to this service, consider using the services provided with the webMethods JDBC Adapter.
The service does not perform any parsing on the SQL statement.
Input Parameters
$data Document Optional. Criteria that the rows to delete must meet.
Important! If no criteria are provided, all rows are deleted from the
table.
$updateCount String Number of rows deleted.
$dbMessage String Conditional. Message indicating the success or failure of
the operation.
$dbAlias String Optional. Database alias.
$dbURL String Optional. JDBC URL that identifies the database resource. $dbUser String Optional. User name to use to log into the database. $dbPass String Optional. Password for the user.
$dbDriver String Optional. Name of the JDBC driver to use.
$dbConnection com.wm.app.b2b.server.DBConnection Optional. Connection object
returned by pub.db:connect.
$dbCatalog String Optional. Name of the database's system catalog. Include
this parameter if your DBMS supports distributed databases and you want to retrieve information from a database to which you are not currently connected.
If you are not using a distributed database system, you do not need to specify this parameter.
If you are running against DB2, use this parameter to specify the database location.
5 Db Folder
$dbSchemaPattern String Optional. Name of the schema to which the table belongs.
If your database supports pattern-matching on schemas, you may specify the schema name with a pattern-matching string, where _ represents a single character and % represents any string of characters. For example, the value HR% would represent any schema beginning with the characters HR.
If you are running against DB2, you use this parameter to specify the table's AuthID.
$dbSQL String SQL statement to execute.
$dbProcessEsc String Optional. Flag that indicates whether JDBC SQL escapes
will be processed. These escapes allow database-independent access to database-dependent functionality. For example, different dialects of SQL have different syntax for date literals. Using a JDBC escape, you can encode a date literal in a SQL string that should work on any database. Documentation on JDBC SQL escapes is widely available.
Set to:
true to process JDBC SQL escapes. This is the default.
false to skip processing JDBC SQL escapes.
$dbProcessReporter Tokens
String Optional. Flag that indicates whether reporter tags (for
example, %value xxx%) will be processed in the SQL. Including these tokens in your SQL allows dynamic construction of complex SQL statements, at the possible expense of some execution speed.
Set to:
true to process tags.
false to ignore tags. This is the default.
$dbParamValues Object List Optional. If the "?" parameters in the SQL statement
are not supplied indirectly (with the $dbParamNames parameter), they can be supplied directly via this parameter. See “Usage Notes” on page 181 below. Objects in $dbParamValues can be of any type.
$dbParamNames String List Optional. Names of any "?" parameters in the SQL. See
“Usage Notes” on page 181 below.
$dbParamTypes String List Optional. SQL type names for each parameter. Use
type names from the JDBC 1.2 specification ("INTEGER", "VARCHAR", etc.).
5 Db Folder
Output Parameters
Usage Notes
This service does not support updates from a web browser or HTML form. You may specify the connection parameters in one of the following ways:
$dbAlias
$dbURL, $dbUser, $dbPass, $dbDriver
$dbConnection
SQL supports host variables ("?") in statements to be executed. Because the pipeline is based on named values and individual host variables are not named, $dbParamNames and
$dbParamTypes are used to supply an index-to-name mapping for each SQL statement
executed. For example, consider the following SQL query: SELECT * FROM royalties WHERE pub_id = ? and roy_amt > ?
To execute this SQL query, you could supply the following values to the pub.db:execSQL service:
sql String Conditional. SQL as it was actually passed to the target
database. This can be helpful in debugging calls to this service when dynamic SQL is used (that is, you are using either JDBC SQL escapes or webMethods Reporter tokens in your SQL).
paramsAsStrings String List Conditional. Values used for each of the parameters in
the SQL statement. This can be helpful in debugging calls to this service when "?" parameters are being used.
$rowCount String Conditional. Number of rows in results.
results com.wm.util.Table Conditional. Results from the SQL statement.
The Integration Server recognizes and treats this parameter as a Document List at run time.
$updateCount String Conditional. Number of rows updated.
$dbMessage String Conditional. Message indicating the success or failure of
the operation.
Key Value Description
$dbSQL SELECT * FROM royalties
WHERE pub_id = ? and roy_amt > ?
SQL query to execute.
$dbParamNames pub_id
roy_amt Pipeline items to use for the host variables.
$dbParamTypes varchar
5 Db Folder
Example: Consider the following SQL query, which contains an INSERT with three host variables:
INSERT INTO books VALUES (?, ?, ?)
To execute this SQL query, you could supply the following values to the pub.db:execSQL service:
pub_id P1053 Values for the host
variables.
roy_amt 10 Values for the host
variables.
Note: Even if there is only one host variable in the SQL
statement, both $dbParamNames and $dbParamTypes are String arrays.
Key Value Description
$dbSQL INSERT INTO books VALUES
(?, ?, ?)
SQL query to execute.
$dbParamNames book_id
pub_id book_title
Pipeline items to use for the host variables.
$dbParamTypes varchar
varchar varchar
SQL types for the host variables.
book_id B234 Values for the host
variables.
pub_id P1053 Values for the host
variables.
book_title The Importance of Being Earnest
Values for the host variables.
Note: The SQL type names used in the examples are
defined in the java.sql.Types and SQL92. Even if you used an Oracle database, which calls long string types "varchar2," you would call them varchar. The standard names from SQL92 will be mapped into database- specific type names.
5 Db Folder