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
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
-
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). -
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 -
Hi Agata,
How do I setup the "metadata" attribute for the LookupTable in the graph? Here is my Connection and LookupTable in my graph:
<Connection id="CONN_0" type="JDBC" dbURL="jdbc:mysql://localhost:3306/core?autoReconnect=true" dbDriver="com.mysql.jdbc.Driver" driverLibrary="D:/mylib/mysql-connector-java-5.0.5.jar" user="appuser" password="appuser" />
<LookupTable id="LOOKUPTABLE_0" type="dbLookup" dbConnection="CONN_0" metadata="WHAT SHOULD BE HERE??" >
<attr name="sqlQuery"><![CDATA[select lookup_code_descr from lookup_codes where lookup_code = ? and lookup_type = 'land_use_codes']]></attr>
</LookupTable>
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 -
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. -
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 {
.... <some code> ....
public boolean init() throws ComponentNotReadyException {
.... <some code> ....
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());
.... <some code> ....
}
.... <some code> ....
}
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 -
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? -
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 {
.... <some code> ....
public boolean init() throws ComponentNotReadyException {
.... <some code> ....
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());
.... <some code> ....
} // 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();
.... <some code> ....
}
.... <some code> ....
} // end transform()
.... <some code> ....
} // end BaseRules
Thanks,
al -
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:database=MYSQL
jdbcSpecific=MYSQL
It should work.
If not, you will have to create the metadata by yourself. -
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: * <!ATTLIST Connection id ID #REQUIRED type NMTOKEN (JDBC) #REQUIRED database CDATA #IMPLIED dbDriver CDATA #IMPLIED dbURL CDATA #IMPLIED dbConfig CDATA #IMPLIED driverLibrary CDATA #IMPLIED user CDATA #IMPLIED password CDATA #IMPLIED threadSafeConnection NMTOKEN (true | false) #IMPLIED passwordEncrypted NMTOKEN (true | false) #IMPLIED transactionIsolation (READ_UNCOMMITTED | READ_COMMITTED | REPEATABLE_READ | SERIALIZABLE ) #IMPLIED>
Currently the Connection element in my graphs looks like this:
<Connection id="CONN_0" type="JDBC" dbURL="jdbc:mysql://localhost:3306/core?autoReconnect=true" dbDriver="com.mysql.jdbc.Driver" driverLibrary="D:/mylib/mysql-connector-java-5.0.5.jar" user="appuser" password="appuser" />
Please clarify what needs to be added/changed?
Thanks,
al -
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).
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:
<Connection id="CONN_0" type="JDBC" dbURL="jdbc:mysql://localhost:3306/core?autoReconnect=true" database="MYSQL" jdbcSpecific="MYSQL" user="appuser" password="appuser" /> - 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.
-
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 -
Hi Agata,
that's right, the javadoc did mention that Lookup extends Iterator<DataRecord>... i somehow missed that... oops...
Now, my lookup is working :P 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 -
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.
Please sign in to leave a comment.
Comments 16