pub.db:execSQL
WmDB. Executes the specified SQL statement. As an alternative to this service, consider using the services provided with the webMethods Adapter for JDBC.
The service does not perform any parsing on the SQL statement.
Input Parameters
$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. |
$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. |
$dbProcessReporterTokens | 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 below. Objects in $dbParamValues can be of any type. |
$dbParamNames | String List Optional. Names of any "?" parameters in the SQL. See Usage Notes below. |
$dbParamTypes | String List Optional. SQL type names for each parameter. Use type names from the JDBC 1.2 specification ("INTEGER", "VARCHAR", etc.). |
Output Parameters
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. |
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:
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
integer | SQL types for the host variables. |
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. |
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:
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. |