# LookupTable changes between CloverETL version 2.5 and 2.6?

**URL:** <https://forum.cloverdx.com/t/lookuptable-changes-between-cloveretl-version-2-5-and-2-6/525>\
**Category:** CloverDX Platform\
**Created:** [May 27, 2009, 12:00am UTC](https://forum.cloverdx.com/t/lookuptable-changes-between-cloveretl-version-2-5-and-2-6/525 "2009-05-27T00:00:00Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![achan](https://avatars.discourse-cdn.com/v4/letter/a/7cd45c/32.png) [@achan](https://forum.cloverdx.com/u/achan)\
**Post date:** [May 27, 2009, 12:00am UTC](https://forum.cloverdx.com/t/lookuptable-changes-between-cloveretl-version-2-5-and-2-6/525/1 "2009-05-27T00:00:00Z")

</div>

Hi,

in version 2.5.2, there is a **get(java.lang.Object[] keys)** method that returns a DataRecord in the LookupTable interface. However, this method does not exist for version 2.6. Is there an equivalent method?

If not, is this what I need to do:  
1. call **getKeyMetadata()** to get the key fields  
2. iterate through the LookupTable object to find the lookup data row that matches the key fields to their values

seems like a memory hog if each time I need to find a lookup data row, I have to iterate through the whole LookupTable…

any suggestions?

Thanks,  
al

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [May 28, 2009, 7:39am UTC](https://forum.cloverdx.com/t/lookuptable-changes-between-cloveretl-version-2-5-and-2-6/525/2 "2009-05-28T07:39:11Z")

</div>

Hi Al,  
lookup tables really changed from 2.5 to newer versions. Now “old” lookup table is represented by two interfaces: _LookupTable_, which contains data and kind of lookup and _Lookup_, which serves as a provider to lookup table data. When you create LookupTable, you should call _createLookup(…)_ method, then after each _seek(…)_ calling you get set of data records fitting to current data (hold in DataRecord used for creating lookup). Please see javaExamples/testDBLookup.java example ([http://www.cloveretl.com/download/examp … -2-7-0.zip](http://www.cloveretl.com/download/examples/cloverETL.examples.rel-2-7-0.zip)).

---

<div class="post-metadata">

**Author:** ![achan](https://avatars.discourse-cdn.com/v4/letter/a/7cd45c/32.png) [@achan](https://forum.cloverdx.com/u/achan)\
**Post date:** [June 22, 2009, 6:15pm UTC](https://forum.cloverdx.com/t/lookuptable-changes-between-cloveretl-version-2-5-and-2-6/525/3 "2009-06-22T18:15:59Z")

</div>

Hi Agata,

After migrating to CloverETL 2.7.1 from 2.5.2, I am getting this error when I try to run my previous graph:

INFO [main] - Graph definition file: DEMO.grf  
DEBUG [main] - create PluginClassLoader:[file:/D:/FACoreLogic/core\_etl/dist/plugins/org.jetel.connec  
tion/cloveretl.connection.jar]  
DEBUG [main] - create PluginClassLoader:[file:/D:/FACoreLogic/core\_etl/dist/plugins/org.jetel.lookup  
/cloveretl.lookup.jar]  
Exception in thread “main” java.lang.NoClassDefFoundError: org/jetel/data/lookup/LookupTableIterator

at java.lang.Class.forName0(Native Method)  
at java.lang.Class.forName(Class.java:242)  
at org.jetel.data.lookup.LookupTableFactory.getLookupTableClass(LookupTableFactory.java:105)

at org.jetel.data.lookup.LookupTableFactory.createLookupTable(LookupTableFactory.java:133)  
at org.jetel.graph.TransformationGraphXMLReaderWriter.instantiateLookupTables(Transformation  
GraphXMLReaderWriter.java:740)  
at org.jetel.graph.TransformationGraphXMLReaderWriter.read(TransformationGraphXMLReaderWrite  
r.java:353)  
at org.jetel.graph.TransformationGraphXMLReaderWriter.read(TransformationGraphXMLReaderWrite  
r.java:286)  
at org.jetel.graph.TransformationGraphXMLReaderWriter.loadGraph(TransformationGraphXMLReader  
Writer.java:215)  
at org.jetel.main.runGraph.main(runGraph.java:319)

Is a jar missing from the plugins or is a file missing from a jar in this 2.7.1 version? Or am I doing something incorrect?

Thanks,  
albert

---

<div class="post-metadata">

**Author:** ![achan](https://avatars.discourse-cdn.com/v4/letter/a/7cd45c/32.png) [@achan](https://forum.cloverdx.com/u/achan)\
**Post date:** [June 22, 2009, 10:27pm UTC](https://forum.cloverdx.com/t/lookuptable-changes-between-cloveretl-version-2-5-and-2-6/525/4 "2009-06-22T22:27:06Z")

</div>

Hi Agata,

Please ignore my previous message, coz I fixed my problem… It’s because I did not update the plugins…

Thanks,  
al

---

<div class="post-metadata">

**Author:** ![achan](https://avatars.discourse-cdn.com/v4/letter/a/7cd45c/32.png) [@achan](https://forum.cloverdx.com/u/achan)\
**Post date:** [June 25, 2009, 4:38pm UTC](https://forum.cloverdx.com/t/lookuptable-changes-between-cloveretl-version-2-5-and-2-6/525/5 "2009-06-25T16:38:25Z")

</div>

Hi Agata,

How do I setup the “metadata” attribute for the LookupTable in the graph? Here is my Connection and LookupTable in my graph:

and here is how I create my lookup\_codes table:

CREATE TABLE `lookup_codes` (  
`lookup_code_id` int(10) unsigned NOT NULL auto\_increment COMMENT ‘Surrogate key’,  
`lookup_code` varchar(20) NOT NULL COMMENT ‘A lookup code’,  
`lookup_type` varchar(50) NOT NULL COMMENT ‘A way to separate different types of lookup codes into categories’,  
`lookup_code_descr` varchar(50) NOT NULL COMMENT ‘Purpose of the lookup code’,  
`lookup_code_replace_value` varchar(20) NOT NULL COMMENT ‘A standardized replacement value for the lookup code’,  
`modified_user` varchar(20) NOT NULL COMMENT ‘Username of the last modifier’,  
`modified_date` datetime NOT NULL COMMENT ‘Date when the record was last modified’,  
PRIMARY KEY (`lookup_code_id`),  
UNIQUE KEY `NATURAL_KEY` (`lookup_code`,`lookup_type`)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT=‘Generic lookup table that is used for core lookup’;

Thanks,  
al

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [June 26, 2009, 6:54am UTC](https://forum.cloverdx.com/t/lookuptable-changes-between-cloveretl-version-2-5-and-2-6/525/6 "2009-06-26T06:54:29Z")

</div>

Hi Al,  
DBLookupTable metadata describes the structure of data received from database by executing the query, that defines your lookup table. So in your case the metadata should consist of one string field (lookup\_code\_descr). But CloverETL is able to create the metadata by itself with most of the database drivers and (since 2.6 version) metadata attribute is not requisite for db lookup table.

---

<div class="post-metadata">

**Author:** ![achan](https://avatars.discourse-cdn.com/v4/letter/a/7cd45c/32.png) [@achan](https://forum.cloverdx.com/u/achan)\
**Post date:** [June 26, 2009, 5:25pm UTC](https://forum.cloverdx.com/t/lookuptable-changes-between-cloveretl-version-2-5-and-2-6/525/7 "2009-06-26T17:25:42Z")

</div>

Hi Agata,

I removed the “metadata” attribute from the LookupTable element in my graph, but I am still getting NULL when trying to get the “metadata” in my java code… here is a snipplet of my java class:

public class BaseRules extends DataRecordTransform {

… …

public boolean init() throws ComponentNotReadyException {

… …

LookupTable lookupTable = getLookupTable(“LOOKUPTABLE\_0”);

// initialize lookup table  
if (!lookupTable.isInitialized()) {  
lookupTable.init();  
}

logger.info(new StringBuffer("Lookup table “).append(lookupTblID).append(” metadata : ").append(lookupTable.getMetadata()).toString());

… …  
}

… …  
}

and the output from my logger.info statement is:

INFO [WatchDog] - Lookup table LOOKUPTABLE\_0 metadata : null

I also tried printing lookupTable.getKeyMetadata() and it also comes back NULL…

am I missing something?

Thanks,  
al

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [June 29, 2009, 9:16am UTC](https://forum.cloverdx.com/t/lookuptable-changes-between-cloveretl-version-2-5-and-2-6/525/8 "2009-06-29T09:16:49Z")

</div>

Hi Al,  
dbMetadata is instantiated, when really needed, that means eg. during first seek or calling iterator. So it is OK, that you get null before the first seek. But when calling getKeyMetadata() method you shouldn’t definitely get null. This method should return the metadata or throw en exception. Can you show your code?

---

<div class="post-metadata">

**Author:** ![achan](https://avatars.discourse-cdn.com/v4/letter/a/7cd45c/32.png) [@achan](https://forum.cloverdx.com/u/achan)\
**Post date:** [June 29, 2009, 6:37pm UTC](https://forum.cloverdx.com/t/lookuptable-changes-between-cloveretl-version-2-5-and-2-6/525/9 "2009-06-29T18:37:19Z")

</div>

Hi Agata,

you are right, getKeyMetadata() did throw an exception: “Reason: Can’t get metadata from database”… sorry for the confusion…

if I cannot get the dbMetadata, since it is not instantiated immediately after init(), how can I create the RecordKey for creating the lookup and seeking?

I would not be able to create keyRecord in the transform() in my BaseRules class (from my code snipplet below) if keyMetadata is NULL… so what should I do?

public class BaseRules extends DataRecordTransform {

… …

public boolean init() throws ComponentNotReadyException {

… …

LookupTable lookupTable = getLookupTable(“LOOKUPTABLE\_0”);

// initialize lookup table  
if (!lookupTable.isInitialized()) {  
lookupTable.init();  
}

logger.info(new StringBuffer("Lookup table “).append(lookupTblID).append(” metadata : ").append(lookupTable.getMetadata()).toString());

… …

} // end init()

public int transform(DataRecord \_source, DataRecord \_target) throws TransformException {

DataRecordMetadata keyMetadata = lookupTable.getMetadata();

DataRecord keyRecord = new DataRecord(keyMetadata);  
keyRecord.init();  
RecordKey key = new RecordKey(keyMetadata.getFieldNamesArray(), keyMetadata);  
key.init();

// create lookup query based on requested key  
Lookup lookup = null;  
try {  
lookup = lookupTable.createLookup(key, keyRecord);  
} catch (ComponentNotReadyException e) {  
logger.error(new StringBuffer(“Error creating lookup query for lookup table!!”).toString());  
return null;  
}

// setup the key record value  
keyRecord.getField(i).fromString(“COM”);

// try to lookup based on specified parameter  
lookup.seek(keyRecord);

// get results, if there are any  
if (lookup.hasNext()) {  
Object value = lookup.next();  
… …  
}

… …

} // end transform()

… …

} // end BaseRules

Thanks,  
al

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [June 30, 2009, 7:40am UTC](https://forum.cloverdx.com/t/lookuptable-changes-between-cloveretl-version-2-5-and-2-6/525/10 "2009-06-30T07:40:13Z")

</div>

Hi Al,  
this error means, that metadata can’t be obtain from database with driver you use. You will probably not be able to execute seek method without the db metadata.Try to use built-in connection:

```auto
database=MYSQL
jdbcSpecific=MYSQL

```

It should work.  
If not, you will have to create the metadata by yourself.

---

<div class="post-metadata">

**Author:** ![achan](https://avatars.discourse-cdn.com/v4/letter/a/7cd45c/32.png) [@achan](https://forum.cloverdx.com/u/achan)\
**Post date:** [June 30, 2009, 6:55pm UTC](https://forum.cloverdx.com/t/lookuptable-changes-between-cloveretl-version-2-5-and-2-6/525/11 "2009-06-30T18:55:45Z")

</div>

Hi Agata,

What do you mean by using a built-in connection? How do I set “database=MYSQL” and “jdbcSpecific=MYSQL” since “jdbcSpecifc” attribute is not defined in the Connection XML DTD I found from the javadoc (excerpt below) for CloverETL version 2.7.1?

The XML DTD describing the internal structure is as follows: \*

Currently the Connection element in my graphs looks like this:

Please clarify what needs to be added/changed?

Thanks,  
al

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [July 1, 2009, 8:00am UTC](https://forum.cloverdx.com/t/lookuptable-changes-between-cloveretl-version-2-5-and-2-6/525/12 "2009-07-01T08:00:10Z")

</div>

Hi Al,  
you right - our documentation is really not up-to-date :oops: I’ve reported an issue to our bug tracking system ([http://bug.cloveretl.org/view.php?id=2335](http://bug.cloveretl.org/view.php?id=2335)).  
But, cutting to the chase:

- **database** attribute is used to lookup in build-in JDBC drivers. There are two built-in jdbc drivers in CloverETL so far: for MySql data base (_database=MYSQL_) and for PostgreSQL database (_database=POSTGRE_) - you can see it in plugin.xml in cloveretl.jdbc.jar.
- by setting **jdbcSpecific** you can slightly change connection behavior, that means different data type conversion, getting auto-generated keys etc.

So your connection should look like follows:

```auto

<Connection id="CONN_0" type="JDBC" dbURL="jdbc:mysql://localhost:3306/core?autoReconnect=true" database="MYSQL" jdbcSpecific="MYSQL" user="appuser" password="appuser" />

```

---

<div class="post-metadata">

**Author:** ![achan](https://avatars.discourse-cdn.com/v4/letter/a/7cd45c/32.png) [@achan](https://forum.cloverdx.com/u/achan)\
**Post date:** [July 2, 2009, 12:38am UTC](https://forum.cloverdx.com/t/lookuptable-changes-between-cloveretl-version-2-5-and-2-6/525/13 "2009-07-02T00:38:15Z")

</div>

Hi Agata,

That suggestion did not work… I am still getting a NULL metadata even though I set database and jdbcSpecific to both “MYSQL” in my graph…

So I went ahead and created my own metadata (see below) in my Java class (see previous posting for the full Java class):

DataRecordMetadata keyMetadata = new DataRecordMetadata(“dbMetadata”);  
DataFieldMetadata field = new DataFieldMetadata(“lookup\_code\_descr”, Short.parseShort(“1000”));  
keyMetadata.addField(field);

instead of doing:

DataRecordMetadata keyMetadata = lookupTable.getMetadata();

When I tried to print out the lookup returned result by:

Object value = lookup.next();  
logger.info(new StringBuffer("Returned: ").append(value.toString()).toString());

I am getting a lookup result in this format:

#0|lookup\_code\_descr|S-\>COMMERCIAL

How do I get only the string value “COMMERCIAL” out of the lookup result above? Are there other methods, not documented in your javadoc, to get the value?

Thanks,  
al

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [July 2, 2009, 8:09am UTC](https://forum.cloverdx.com/t/lookuptable-changes-between-cloveretl-version-2-5-and-2-6/525/14 "2009-07-02T08:09:04Z")

</div>

Hi Al,  
interface Lookup extends Iterator, so calling next method you get DataRecord (with the dbMetadata structure). So to get only string you need to call:

```auto

DataRecord value = lookup.next();
StringBuilder sValue = value.getField(0).getValue();

```

---

<div class="post-metadata">

**Author:** ![achan](https://avatars.discourse-cdn.com/v4/letter/a/7cd45c/32.png) [@achan](https://forum.cloverdx.com/u/achan)\
**Post date:** [July 2, 2009, 4:40pm UTC](https://forum.cloverdx.com/t/lookuptable-changes-between-cloveretl-version-2-5-and-2-6/525/15 "2009-07-02T16:40:07Z")

</div>

Hi Agata,

that’s right, the javadoc did mention that Lookup extends Iterator… i somehow missed that… oops…

Now, my lookup is working 😛 I am creating my own db metadata, but I am still wondering why i cannot get the db metadata when setting “database” and “jdbcSpecific” to “MYSQL” for the Connection element in my graph?? Can this possibly be a bug??

Thanks a lot for your time and help,  
al

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [July 3, 2009, 8:21am UTC](https://forum.cloverdx.com/t/lookuptable-changes-between-cloveretl-version-2-5-and-2-6/525/16 "2009-07-03T08:21:17Z")

</div>

Hi Al,  
this is problem of MySql jdbc driver. The original exception is:  
_Caused by: java.sql.SQLException: Parameter metadata not available for the given statement_  
_at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)_  
_at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)_  
_at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)_  
_at com.mysql.jdbc.MysqlParameterMetadata.checkAvailable(MysqlParameterMetadata.java:66)_  
_at com.mysql.jdbc.MysqlParameterMetadata.getParameterType(MysqlParameterMetadata.java:115)_  
_at org.jetel.connection.jdbc.SQLUtil.dbMetadata2jetel(SQLUtil.java:192)_  
_at org.jetel.connection.jdbc.SQLUtil.dbMetadata2jetel(SQLUtil.java:284)_  
_at org.jetel.lookup.DBLookupTable.getKeyMetadata(DBLookupTable.java:458)_  
This method works properly with eg. PostgreSQL database.

---

<div class="post-metadata">

**Author:** ![achan](https://avatars.discourse-cdn.com/v4/letter/a/7cd45c/32.png) [@achan](https://forum.cloverdx.com/u/achan)\
**Post date:** [July 6, 2009, 6:21pm UTC](https://forum.cloverdx.com/t/lookuptable-changes-between-cloveretl-version-2-5-and-2-6/525/17 "2009-07-06T18:21:35Z")

</div>

Hi Agata,

are you saying that setting “database” and “jdbcSpecific” to “MYSQL” did not work because there is a problem with MySQL, it’s not a CloverETL-specific bug?

anyway, for my case, I am going to create the db metadata in my java class… that works for me…

Thanks,  
al