Adapter for JDBC 10.3 | webMethods Adapter for JDBC Documentation | webMethods Adapter for JDBC Installation and User’s Documentation | Adapter Services | Configuring BatchInsertSQL Services
 
Configuring BatchInsertSQL Services
Similar to an InsertSQL service, a BatchInsertSQL service also inserts new information into a database table; however the BatchInsertSQL service can insert a large volume of data into a table more efficiently than an InsertSQL service, improving performance when a large data volume is involved. 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.
Note:
BatchInsertSQL services cannot be used with a Teradata database (any version).
*To configure a BatchInsertSQL 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.
Note:
For BatchInsertSQL services, you must use a LOCAL_TRANSACTION connection. If you do not use LOCAL_TRANSACTION, you will not see a list of tables in the Tables tab. Also, you may not see an error message until you reload metadata values or check the error log. For instructions for creating a LOCAL_TRANSACTION connection, see Configuring Adapter for JDBC Connections. For information about reloading metadata values, see Reloading Adapter Values.
5. From the list of available templates, select the BatchInsertSQL 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 Table tab to configure the database table to be updated and set the fields as follows:
Field
Description/Action
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 table type displays automatically based on the table you select.
7. Select the INSERT tab and use the Column, Column Type, JDBC Type, and Expression fields on the top row of the tab to define the columns and fields to be inserted as described in the following table.
a. Use the icon to create new rows as needed. You can use the icon to fill in all rows to the table.
Field
Description/Action
Column
The INSERT column name in the database table.
Column Type
The INSERT column data type in the database table.
JDBC Type
The JDBC type for the input field.
Expression
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. It adds one row with the same column name to the table. 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.
b. For each inserted row that uses the default Expression value of ?, the corresponding Input Field, and Input Field Type display on the second row of the INSERT tab. Use the following fields:
Field
Description/Action
Column
The INSERT column name in the database table.
Column Type
The INSERT column data type in the database table.
Input Field
The input field name. You can change this name if needed.
Input Field Type
The data type of the input field. You can change this type if needed.
Note:
If you use WmFlatFile services to generate the document list as input, the input field type must be java.lang.String. This is because fields from WmFlatFile services generate documents that have String fields.
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.
c. Specify the query time out value of the BatchInsertSQL service you are configuring in the following field:
Field
Description/Action
Query Time Out
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.
8. Use the Batch Result tab's Batch Result Output Name to specify the output field name for the batch operation. The output of the batch operation is a string list. The elements of the string list are ordered according to the order in which commands were added to the batch. Depending on the JDBC driver you use, the elements in the string list may be one of the following:
*A number greater than or equal to zero. This indicates that the command was successfully executed and the number of rows in the database affected.
*A value of SUCCESS_NO_INFO. This indicates that the command was processed successfully but the number of rows affected is unknown.
9. From the File menu, select Save.