Adapter for JDBC 10.3 | webMethods Adapter for JDBC Documentation | webMethods Adapter for JDBC Installation and User’s Documentation | Adapter Services | Configuring CustomSQL Services | Creating a CustomSQL service
 
Creating a CustomSQL service
Use the following instructions to create a CustomSQL adapter service. You configure adapter services using Designer.
*To create a CustomSQL 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 CustomSQL 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 SQL tab to specify a SQL statement and the associated input and output parameters.
Use the icon and to create new rows as needed. You can use the icon to fill in all rows to the table.
Be sure to review the section Considerations before using Fill in all rows to the table feature in CustomSQL Adapter Service before you use the Fill in all rows to the table icon.
Set the SQL parameters as described in the following table:
Note:
When using the CustomSQL service for a Select SQL, it is not mandatory to configure the output fields Output JDBC Type, Output Field Type, and Output Field. Adapter for JDBC uses the fields provided in the Select SQL statement as the output parameter fields.
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. For example, using the SQL statement above, 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 an exception will be generated at run time.
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.
Result Field Type
The data type of the Result Field.
7. From the File menu, select Save.