Adapter for JDBC 10.3 | webMethods Adapter for JDBC Documentation | webMethods Adapter for JDBC Installation and User’s Documentation | Adapter Services | Configuring SelectSQL Services
 
Configuring SelectSQL Services
A SelectSQL service retrieves specified information from a database table. You configure Adapter for JDBC services using Designer. For more information about adapter services, see Using Adapter Services.
Be sure to review the section Before Configuring or Managing Adapter Services before you configure adapter services.
*To configure a SelectSQL 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 SelectSQL 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 Tables tab to configure the database table (or tables) the operation accesses, using the following fields:
Field
Description/Action
Table Alias
The table alias is assigned automatically when you select more than one table in the Table Name field. The default is t1.
Table Name
Select a table name. The default for the associated catalog name is current catalog. The default for the associated schema name is current schema. The table name must not contain a period. If the table name does contain a period, Designer will throw an error.
Note:
Informix databases do not allow you to specify a catalog and database name because you can only access the current catalog.
Type
The type displays automatically based on the table you select.
7. If you are not joining tables, skip this step. Select the Joins tab to specify the columns for joining the tables you just configured.
a. Select the icon to create new left and right columns.
b. Select Left Column and select the first table's joining column.
c. Select the appropriate Operator.
d. Select Right Column and select the next table's joining column.
e. Repeat this procedure until you have defined all the joins.
8. Use the SELECT tab to define the columns and fields to be selected as follows:
a. In the ALL/DISTINCT field, select ALL to include duplicate rows or DISTINCT to suppress duplicate rows. Selecting ALL corresponds to the SQL statement SELECT ALL name from tablename. The default value is blank, which corresponds to the SQL statement SELECT name from tablename.
b. Use the icon to create new rows as needed. You can use the icon to fill in all rows to the table.
c. As you insert additional rows, the corresponding Column Type, JDBC Type, Output Field Type, and Output Field display for each column you select in the Expression field.
Use the following fields:
Field
Description/Action
Expression
The column name in the database table.
Column Type
The column data type defined in the database table.
JDBC Type
The JDBC type of the corresponding Output Field.
Output Field Type
The data type of the output field. Adapter for JDBC automatically converts database-specific types to Java data types. For a list of JDBC type to Java type mappings, see JDBC Data Type to Java Data Type Mappings.
Output Field
The name of the field containing the output from the SELECT operation. An output field name displays when you select an expression. You can also modify the output field names as required.
Sort Order
Specifies how rows are returned as follows: Select either Ascend or Descend. Leave the field blank if there is no sort order.
Maximum Row
Use this field only to specify the maximum number of records to retrieve from the database. The default value of 0 (no limit) retrieves all records.
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
Specify a name for the output field that must contain 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.
9. Use the WHERE tab to specify the conditions for selecting information:
a. Select the icon to define new WHERE clause fields.
b. Select a logical operator from the AND/OR field, an Operator, and separators (the left and right parentheses) as needed, and specify values for the following fields:
Field
Description/Action
AND/OR
The logical operator.
Column
The name of the column you want to use in the WHERE clause.
Operator
The operator used with the Column and Input Field.
Input Field
The default value is ?, which acts as a placeholder for the variable so that you can set the input variable for that column at run time, or get input external to this adapter service. You can also type a fixed value in this field now or at run time. If you type a fixed value, be sure that it is valid, or an exception will be generated at run time.
The adapter automatically generates values for the following fields:
Field
Description
Parameter
The number of the inserted row.
Column (second occurrence of this field)
The name of the column you want to use in the WHERE clause.
JDBC Type
The JDBC type of the corresponding Input Field.
Input Field Type
The corresponding input field's Java type. For a list of JDBC type to Java type mappings, see JDBC Data Type to Java Data Type Mappings.
Input Field (second occurrence of this field)
The name of the input field. By default the name combines the values of the Parameter and Column fields. However, you can also choose to specify any custom value.
Note:
For Oracle users, if you use a CHAR(n) data type and enter a value in the Input Field, Adapter for JDBC automatically sets the ORACLEFIXED_CHAR data type as the JDBC data type.
Note:
The WHERE clause does not support the java.sql.Array data type.
c. If necessary, use the or icons to change the order of the WHERE clause to ensure the parameters are parsed in the correct order.
d. Repeat this procedure until you have specified all WHERE parameters.
10. From the File menu, select Save.