JDBC adapter -BIND command examples

These JDBC adapter examples illustrate the effect of the -BIND command in a GET function and in an output card and custom Data Manipulation Language (DML) statement.

Example of the -BIND command used in a GET function

The JDBC adapter is used in a GET function as follows:

GET("JDBC", "-URL connection -USER username -PASSWORD password -QUERY ""SELECT C1, C2, C3 FROM table1 WHERE c4 = ? AND c1 = ?""", inputdata)
Because the -BIND command is not specified, the adapter assumes that the input data consists of a record with two fields:
  • The first field is mapped to the first parameter. It corresponds to the column c4 in the WHERE clause.
  • The second field is mapped to the second parameter. It corresponds to the column c1 in the WHERE clause.
The adapter interprets both fields and binds both parameters as text values.

When the GET function is defined with the -BIND command as follows:

GET("JDBC", "-URL connection -USER username -PASSWORD password -QUERY ""SELECT C1, C2, C3 FROM table1 WHERE c4 = ? AND c1 = ?"" -BIND ""2 INT32,1 FLOAT64""", inputdata)
The adapter assumes that the input data consists of a record with two fields:
  • The first field is mapped to the second parameter. It corresponds to column c1 in the WHERE clause.
  • The second field is mapped to the first parameter. It corresponds to column c4 in the WHERE clause.
The adapter interprets the first field as a 32-bit integer (in text representation). It interprets the second field as a double-precision floating-point number (value 8 in text representation, followed by the pipe (|) delimiter, followed by 8 bytes representing the number in binary format).

Example of the -BIND command used in an output card and custom DML

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

-URL connection -USER username -PASSWORD password -DML "INSERT INTO table1 VALUES (? + 10, TO_TEXT(?), ? + ?)"
Because the -BIND command is not specified, the adapter assumes that the input data consists of records with four fields each:
  • The first field is mapped to the first parameter.
  • The second field is mapped to the second parameter.
  • The third field is mapped to the third parameter.
  • The fourth field is mapped to the fourth parameter.
The adapter interprets all four fields and binds all four parameters as text values. It's likely that this statement will fail to run (depending on the driver) because the plus (+) operator used with some of the parameters might not work with text values.

Now assume the adapter command line is defined with the -BIND command as follows:

-URL connection -USER username -PASSWORD password -DML "INSERT INTO table1 VALUES (? + 10, TO_TEXT(?), ? + ?)" -BIND "1 INT8,2 DATE,4 INT16,3 INT64"
The adapter assumes that the input data consists of records with four fields each:
  • The first field is mapped to the first parameter.
  • The second field is mapped to the second parameter.
  • The third field is mapped to the fourth parameter.
  • The fourth field is mapped to the third parameter.
The adapter interprets the fields and binds the parameters as follows:
  • The first field is bound to the first parameter as an 8-bit integer.
  • The second field is bound to the second parameter as a date.
  • The third field is bound to the fourth parameter as a 16-bit integer.
  • The fourth field is bound to the third parameter as a 64-bit integer.

Feedback