Integration Server 10.7 | Built-In Services Reference Guide | Db Folder | Summary of Elements in this Folder | pub.db:execSQL
 
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.