JDBC adapter -KEY command examples
These JDBC adapter examples illustrate the effect of the -KEY command in a GET function and in an output card.
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
.