Adapter for JDBC 10.3 | webMethods Adapter for JDBC Documentation | webMethods Adapter for JDBC Installation and User’s Documentation | Adapter Services | Configuring DynamicSQL Services | Creating a DynamicSQL Service
 
Creating a DynamicSQL Service
Use the following instructions to create a DynamicSQL adapter service. You configure Adapter for JDBC services using Designer.
Be sure to review the section Before Configuring or Managing Adapter Services before you configure adapter services.
Note:
You can use a DynamicSQL service to call a stored procedure only when the stored procedure does not have any OUT/INOUT or return parameters. If you need these parameters, use the StoredProcedure service. For instructions, see Configuring StoredProcedure Services.
*To create a DynamicSQL service
1. In Designer, right-click the package in which the service should be contained and select New > Adapter Service.
2. Select the parent namespace, type a name for the adapter service, and click Next.
3. Select Adapter for JDBC as the adapter type and click Next.
4. Select the appropriate Adapter Connection Name and click Next.
5. From the list of available templates, select the DynamicSQL template and click Finish.
The adapter service editor for the adapter service appears. You can select the Adapter Settings tab at any time to confirm adapter service properties such as the Adapter Name, Adapter Connection Name, and Adapter Service Template, as necessary.
6. Select the Dynamic SQL tab to specify a SQL statement and the associated input and output parameters.
Use the icon and set the SQL parameters as described in the table below.
Field
Description/Action
SQL
A SQL statement. If you need more space to type your statement, use the launch icon to the right to open a text editor window. You can type the statement directly in this field, for example:
select short_col, int_col, float_col, double_col,
date_col, date_time_col, varchar_col from ADAPTER-TEST
For variable names, use the ? variable placeholder for each variable. For example:
select employee_name where StaffID = ? and Dept = ?
Note:
If you use the ? variable placeholders in your SQL statement, be sure to enter the corresponding Input Field and field type information in the same order as they appear in your SQL statement. In the above example, StaffID would be the first entry in the Input Field and Dept would be the second entry.
Note:
Do not end your SQL statement with a semi-colon (;) or you will generate an exception.
Note:
You may paste text into this field from the system clipboard. However, you may not cut or copy text from this field to the clipboard for pasting into another application.
Input JDBC Type
The JDBC type of the corresponding Input Field.
Input Field Type
The Java type that corresponds to the input JDBC type. For a list of JDBC type to Java type mappings, see JDBC Data Type to Java Data Type Mappings.
Input Field*
Type the name of the input field.
Output JDBC Type
The JDBC type of the corresponding Output Field.
Output Field Type
The Java type that corresponds to the output JDBC type. For a list of JDBC type to Java type mappings, see JDBC Data Type to Java Data Type Mappings.
Output Field
The output field name.
Maximum Row
The maximum number of records to retrieve from the database. The default value of 0(no limit) retrieves all records.Use this field only with SQL statements that return a result set.
Query Time Out
Specify the query time out value in seconds.
This value is the amount of time Adapter for JDBC waits for the service to execute before stopping the SQL operation.
The time out specified in the Query Time Out field is not guaranteed but depends on the implementation specific to the driver vendor. The JDBC standard Statement.SetQueryTimeout() method relies on the Statement.cancel() method. When execution takes longer than the specified time-out interval, the monitor thread calls Statement.cancel(). In some cases, because of a limitation in the Statement.cancel() method, the time out does not free the thread that invoked the Statement.execute() method and this may lead to higher waiting times.
The default value is -1. Use the default value to have the service use the value indicated on the watt.adapter.JDBC.QueryTimeout property as the time out. If you specify a value equal to 0, or if the watt.adapter.JDBC.QueryTimeout property is not set, the service executes without a time out. If you specify a value greater than 0, the service executes with the specified value as the time out.
Note:
-1 is the only permissible negative value for this field.
For more information about the watt.adapter.JDBC.QueryTimeout property, see Forcing a Timeout During Long-Running SQL Operations in Services and Notifications.
Result Field
Name of the output field that contains the total number of rows affected by the SQL statement.Do not use results as the value of the Result Field.
Result Field Type
The data type of the Result Field.
7. From the File menu, select Save.