JDBCLookupPipe Properties

The JDBCLookupPipe component queries a database at start-up and uses the cached result set as a lookup table.

ClassName: com.sybase.esp.adapter.jdbc.JDBCLookupPipe

Each record in the lookup table consists of a unique lookup key and an array of added attributes. The lookup key consists of one or more attributes. When a data object arrives from the source:

The values of the key attributes are matched against a record in the lookup table.
If no record matches, the data object is passed on to the sink without any transformation.
If a record in the lookup table does match the value of the key attributes, the added attributes from the lookup table are added to the record, and the record result is passed on to the sink.

Property	Description
`JdbcDriver`	(Required) The JDBC driver that connects to the database. For example: oracle.jdbc.OracleDriver
`JdbcUrl`	(Required) The location of the database. For example: jdbc:oracle:thin:@myhost.com:1521:mydatabase
`DBProperty++`	(Optional) Name of a database property that the pipe sets when connecting to the database. For example, the user name, password, database name, and so on.
`DBPropertyn.Value`	(Dependent optional) Value for the associated DBProperty. Set this property if the `DBProperty++` property is set.
`Table`	(Required) Name of the database table where lookup is performed.
`KeyAttName++`	(Required) Attribute names that make up the lookup key.
`KeyDbCol++`	(Required) Names of the database columns that correspond to KeyAttNames.
`ValueAttName++`	(Required) Names of the attributes used for added values.
`ValueDbCol++`	(Required) Names of the database columns that correspond to `ValueAttNames`.
`WhereClause`	(Optional) The `WHERE` clause that is part of the lookup `SELECT` query. The lookup query uses this form: SELECT KeyDbCol1, KeyDbCol2, ... , ValueDbCol1, ValueDbCol2, ... FROM Table WHERE WhereClause

Example

The Oracle database table "MyTable = (SYMBOL, ID, PRICE)" is used for lookup. Each data object has four attributes: AttA, AttB, AttC and AttD. AttA and AttB correspond to SYMBOL and ID respectively and are used as a lookup key, and AttD corresponds to PRICE and is added to the data object received from the source. Here is an example of the pipe configuration:

adapter.LOOKUPPIPE.ClassName= com.sybase.esp.adapter.jdbc.JdbcLookupPipe
adapter.LOOKUPPIPE.JdbcUrl = jdbc:oracle:thin:@myhost.com:1521:mydatabase
adapter.LOOKUPPIPE.JdbcDriver = oracle.jdbc.OracleDriver
adapter.LOOKUPPIPE.DBProperty1 = user
adapter.LOOKUPPIPE.DBProperty1.Value = MyUser
adapter.LOOKUPPIPE.DBProperty2 = password
adapter.LOOKUPPIPE.DBProperty2.Value = MyPassword
adapter.LOOKUPPIPE.Table = MyTable
adapter.LOOKUPPIPE.KeyDbCol1 = SYMBOL
adapter.LOOKUPPIPE.KeyAttName1 = AttA
adapter.LOOKUPPIPE.KeyDbCol2 = ID
adapter.LOOKUPPIPE.KeyAttName2 = AttB
adapter.LOOKUPPIPE.ValueDbCol1 = PRICE
adapter.LOOKUPPIPE.ValueAttName1 = AttD
adapter.LOOKUPPIPE.WhereClause = SYMBOL LIKE 'A%'