JDBC adapter -KEY command examples

Example of the -KEY command used in a GET function

Assume the table in the database is defined as follows:

table1(c1 int, c2 varchar(10), c3 date, primary key(c1, c3))

The adapter is used in the following GET function:

GET("JDBC", "-URL connection -USER username -PASSWORD password -TABLE table1", inputdata)

Because the -KEY command is not specified, the adapter uses columns c1 and c3 as the primary key of table1, and generates a SELECT lookup statement as follows. The fully qualified table name and quoted identifiers are omitted for clarity.

SELECT c1, c2, c3 FROM table1 WHERE c1 = ? and c3 = ?

The adapter assumes that the input data is a record with fields that match the order and names of the columns in the primary key, meaning the c1 field followed by the c3 field.

However, when the adapter command line includes the -KEY command as follows:

GET("JDBC", "-URL connection -USER username -PASSWORD password -TABLE table1 -KEY c2,c3", inputdata)

The adapter generates the SELECT lookup statement as follows. The fully qualified table name and quoted identifiers are omitted for clarity.

SELECT c1, c2, c3 FROM table1 where c2 = ? and c3 = ?

The adapter assumes that input data is a record with the fields that match the order and names of the columns in the -KEY command, meaning the c2 field followed by the c3 field.

Example of the -KEY command used in an output card

Assume the table in the database is defined as follows:

table1(c1 int, c2 varchar(10), c3 date, primary key(c1, c3))

The JDBC adapter is used in an output card with the adapter command line defined as follows:

-URL connection -USER username -PASSWORD password -WRITEMODE INSERT_FIRST -TABLE table1

Because the -KEY command is not specified, the adapter uses columns c1 and c3 as the primary key of table1, and generates INSERT and UPDATE statements as follows. The fully qualified table name and quoted identifiers are omitted for clarity.

INSERT INTO table1(c1, c2, c3) VALUES (?, ?, ?)
UPDATE TABLE table1 SET c2 = ? WHERE c1 = ? AND c3 = ?

However, if the adapter command line in the output card includes the -KEY command as follows:

-URL connection -USER username -PASSWORD password -WRITEMODE INSERT_FIRST -TABLE table1 -KEY c2,c3

The adapter generates the following INSERT and UPDATE statements. The fully qualified table name and quoted identifiers are omitted for clarity.

INSERT INTO table1(c1, c2, c3) VALUES (?, ?, ?)
UPDATE TABLE table1 SET c1 = ? WHERE c2 = ? AND c3 = ?

Regardless of whether the -KEY command is specified, the adapter assumes that input data consists of records with fields that match the order and names of the columns in the target table, meaning c1, followed by c2 followed by c3.