Configuring InsertSQL Services

About this task

An InsertSQL service inserts new information into 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 an InsertSQL service

Procedure

In Designer, right-click the package in which the service should be contained and select New > Adapter Service.
Select the parent namespace, type a name for the adapter service, and click Next.
Select Adapter for JDBC as the adapter type and click Next.
Select the appropriate Adapter Connection Name and click Next.
From the list of available templates, select the InsertSQL 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.

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.

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.

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.

For each inserted row that uses the default Expression value of ?, the corresponding JDBC Type, 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.
JDBC Type	The JDBC type for the input field.
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: 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.

Specify the query time out value of the InsertSQL service you are configuring in the following field:

Field Description/Action

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.

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.

Use the Result tab's Result Field and Result Field Type to specify the output field name and corresponding field types for the resulting number of rows that have been inserted.
From the File menu, select Save.