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%'