Look up not working

CloverDX Platform
1

Hi i want to find values from the table according the input from csv file…

I wrote code for that but it is not working and giving error that…Lookup Reason: Lookup table “LookupTable” not found.

Part of my Code is

DataRecordMetadata metaDataParentChild;
DataRecordMetadataXMLReaderWriter reader=new DataRecordMetadataXMLReaderWriter();

try{
metaDataParentChild=reader.read(new FileInputStream(“C:\\FeedVaidationTool\\src\\com\\mps\\pubstats\\metadata\\parent_child.fmt”));
}catch(IOException ex){
System.err.println(“Error when reading metadata!!”);
throw new RuntimeException(ex);
}

if (metaDataParentChild==null){
throw new RuntimeException(“No INPUT metadata”);
}
TransformationGraph institutionGraph = new TransformationGraph();

Edge egde1 = new Edge(“InEdge1”, metaDataParentChild);
Edge egde2 = new Edge(“OutEdge2”, metaDataParentChild);

// //Nodes…
DelimitedDataReader deLimitedReader= new DelimitedDataReader(“DataParser”,
“c://hanu//feeds//consortium_members.200802.csv”);
deLimitedReader.setSkipFirstLine(true);
//System.out.println(“Skip First Line :::”+feedController.headingIncluded);

Node nodeRead = deLimitedReader;

RecordTransform parentChild =new ParentChildReformat();

DBLookupTable lookup=new DBLookupTable(“LookupTable”,dbConnection.getConnection(dbConnection.getId()),metaDataParentChild,"select id from accounts where code=‘10002’ ");

//Node parentChildReformatNode = parentChildReformat;
try {
lookup.init();
} catch (ComponentNotReadyException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
lookup.setLookupKey(“Field1”);

String joinKeys={“Field1”};

LookupJoin lookUpJoin=new LookupJoin(“lookup”,lookup.getId(),joinKeys,parentChild);

Node dataWriter = new DelimitedDataWriter(“DataWriter3”, “c:\\hanu\\feeds\\out_put\\test.xls”,
false);

/*
DBOutputTable dbFilter = new DBOutputTable(“DBOutPutTable1”, “Conn0”,
“feed_errors”);
dbFilter.setDBFields(feedController.dbFields_FeedStatus);
dbFilter.setCloverFields(feedController.dbFields_FeedStatus);
Node dbFilterOutPut = dbFilter;

//String deDup1={“Field0”};

Trash trash=new Trash(“trash”);
Node trashNode=trash;*/

egde1.connectReader(nodeRead, 0);
egde1.connectWriter(lookUpJoin, 0);
egde2.connectReader(lookUpJoin, 0);
egde2.connectWriter(dataWriter, 0);

nodeRead.addOutputPort(0, egde1);
lookUpJoin.addInputPort(0,egde1);
lookUpJoin.addOutputPort(0, egde2);

dataWriter.addInputPort(0,egde2);

// add Edges & Nodes & Phases to graph
try {
institutionGraph.addConnection(dbConnection);

institutionGraph.addPhase(PHASE_0);
PHASE_0.addNode(nodeRead);
PHASE_0.addNode(lookUpJoin);
PHASE_0.addNode(dataWriter);

institutionGraph.addEdge(egde1);
institutionGraph.addEdge(egde2);

// institutionGraph.addEdge(egde6);

System.out.println(“Graph is created properly”);

} catch (GraphConfigurationException ex) {

System.out.println(“::::::::::::::::::::::::::File Not Found:::::::::::::::::::::::::::::::::::::::::::::::::::::::”);

ex.printStackTrace();
}

Plase help…

Thanks .

2

package com.mps.pubstats.tool;
import org.jetel.component.DataRecordTransform;
import org.jetel.data.DataRecord;
import org.jetel.data.RecordKey;
import org.jetel.data.lookup.LookupTable;
import org.jetel.exception.JetelException;
import org.jetel.graph.TransformationGraph;

public class ParentChildReformat extends DataRecordTransform{

LookupTable mylookup;

public boolean init(){

try {
mylookup = graph.getLookupTable(“LookupTable”);
mylookup.init();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

// create key
String lookupKeyStr={“consortia_id”};
RecordKey key=new RecordKey(lookupKeyStr,sourceMetadata[0]);
key.init();
mylookup.setLookupKey(key);
return true;
}

public boolean transform(DataRecord source, DataRecord target){

DataRecord employee;
employee=mylookup.get(source[0]);

if (employee==null) {// skip this one
errorMessage = “Order: " + source[0].getField(0).getValue() + " - no corresponding record in lookup table”;
return false;
}

//target[0].getField(0).setValue(source[0].getField(0).getValue());
//target[0].getField(1).setValue(source[0].getField(1).getValue());
//target[0].getField(2).setValue(source[0].getField(2).getValue().toString());
target[0].getField(0).setValue(employee.getField(0).getValue().toString());
//target[0].getField(4).setValue(employee.getField(1).getValue());

return true;
}
}

3

Please reply as early as possible…because i am stuck because of this only…

4

Hi,
you must call TransformationGraph.addLookupTable() to add the lookup table to the graph. Generally all elements of the graph must be added to it by som add…() method.

Jaro

5

Hi,
Thanks for help, but now it is giving NULL Pointer at

mylookup = graph.getLookupTable(“LookupTable0”);

Thanks,

6

Hi,
that’s because you created the lookup table with the ID “LookupTable”, not “LookupTable0”. look at the constructor of the DBLookupTable…

Jaro

7

No Jaro,
I changed it to LookupTable0 already…do i need to set graph by the setter method in the Reformer?

8

Hi,
you should either use the constructor of DataRecordTransform which takes the graph as a parameter or set the graph via setGraph() manually.

Jaro

9

Hi Jaro,

My Code is running in Infinity loop…

10

Hi Jaro…I did every thing what ever you suggested but when i run the programme it is taking very much time to execute then run in infinity loop.

So please help…

11

Where does it run into an infinity loop? In the lookup tranformation?

Jaro

12

My Code in main class
…
public boolean LoadParentChild(){

EngineInitializer.initEngine(null, null, null);
System.out.println(“…INs side validatin g graph…”);
boolean isDone = false;
DBConnection dbConnection;
Phase PHASE_0 = new Phase(1);
// initialization; must be present
DataBaseConnection dbConn=new DataBaseConnection();
dbConnection= dbConn.getConnection();

DataRecordMetadata metaDataParentChild;
DataRecordMetadataXMLReaderWriter reader=new DataRecordMetadataXMLReaderWriter();

/*try{
metaDataParentChild=reader.read(new FileInputStream(“C:\\FeedVaidationTool\\src\\com\\mps\\pubstats\\metadata\\parent_child.fmt”));
}catch(IOException ex){
System.err.println(“Error when reading metadata!!”);
throw new RuntimeException(ex);
}

if (metaDataParentChild==null){
throw new RuntimeException(“No INPUT metadata”);
}*/

/*DataRecordMetadata lookupMetaData;
DataRecordMetadataXMLReaderWriter reader1=new DataRecordMetadataXMLReaderWriter();

try{
lookupMetaData=reader1.read(new FileInputStream(“C:\\FeedVaidationTool\\src\\com\\mps\\pubstats\\metadata\\lookup.fmt”));
}catch(IOException ex){
System.err.println(“Error when reading metadata!!”);
throw new RuntimeException(ex);
}

if (lookupMetaData==null){
throw new RuntimeException(“No INPUT metadata”);
}*/

DataRecordMetadata dummy;
DataRecordMetadataXMLReaderWriter reader2=new DataRecordMetadataXMLReaderWriter();

try{
dummy=reader2.read(new FileInputStream(“C:\\FeedVaidationTool\\src\\com\\mps\\pubstats\\metadata\\dummy.fmt”));
}catch(IOException ex){
System.err.println(“Error when reading metadata!!”);
throw new RuntimeException(ex);
}
//System.out.println(“Field…”+dummy.getField(0).getName());
if (dummy==null){
throw new RuntimeException(“No INPUT metadata”);
}

TransformationGraph institutionGraph = new TransformationGraph(“graph”);

Edge egde1 = new Edge(“InEdge1”, dummy);
Edge egde2 = new Edge(“OutEdge2”, dummy);

// //Nodes…
DelimitedDataReader deLimitedReader= new DelimitedDataReader(“DataParser”,
“c://hanu//feeds//consortium_members.200802.csv”);
deLimitedReader.setSkipFirstLine(true);
//System.out.println(“Skip First Line :::”+feedController.headingIncluded);

Node nodeRead = deLimitedReader;

//ParentChildReformat parentChild =new ParentChildReformat();
ChildJoinReformat childRef=new ChildJoinReformat();
childRef.setGraph(institutionGraph);

DBLookupTable dbLookup=new DBLookupTable(“LookupTable0”,dbConnection.getConnection(dbConnection.getId()),dummy,“select id from accounts where code=?”);
dbLookup.setName(“name”);

//dbLookup.setGraph(institutionGraph);

//Node parentChildReformatNode = parentChildReformat;
try {
dbLookup.init();
} catch (ComponentNotReadyException e1) {
System.out.println(“Cause of the error”+e1.getCause());
e1.printStackTrace();
}

/* lookup.setLookupKey(“code”);
String lookupKeyStr={“id”};

RecordKey key=new RecordKey(lookupKeyStr,metaDataParentChild);
key.init();
lookup.setLookupKey(key)*/;

//DataRecord data=dbLookup.get(“10017”);
// System.out.println(data.getField(0)+“…data value…”);

String joinKeys={“Field1”};

LookupJoin lookUp=new LookupJoin(“lookUpJoin”,dbLookup.getId(),joinKeys,childRef);

Node lookUpJoin=lookUp;
Node dataWriter = new DelimitedDataWriter(“DataWriter3”, “c:\\hanu\\feeds\\out_put\\test.xls”,
false);

/*
DBOutputTable dbFilter = new DBOutputTable(“DBOutPutTable1”, “Conn0”,
“feed_errors”);
dbFilter.setDBFields(feedController.dbFields_FeedStatus);
dbFilter.setCloverFields(feedController.dbFields_FeedStatus);
Node dbFilterOutPut = dbFilter;

//String deDup1={“Field0”};

Trash trash=new Trash(“trash”);
Node trashNode=trash;*/

egde1.connectReader(nodeRead, 0);
egde1.connectWriter(dataWriter, 0);
egde2.connectReader(lookUpJoin, 0);
egde2.connectWriter(dataWriter, 0);

nodeRead.addOutputPort(0, egde1);
lookUpJoin.addInputPort(0,egde1);
lookUpJoin.addOutputPort(0, egde2);

dataWriter.addInputPort(0,egde2);

// add Edges & Nodes & Phases to graph
try {
institutionGraph.addConnection(dbConnection);
institutionGraph.addLookupTable(dbLookup);

//institutionGraph.addEdge(egde2);
institutionGraph.addPhase(PHASE_0);
PHASE_0.addNode(nodeRead);
PHASE_0.addNode(lookUpJoin);
PHASE_0.addNode(dataWriter);
institutionGraph.addEdge(egde1);
institutionGraph.addEdge(egde2);
//childRef.setGraph(institutionGraph);
// institutionGraph.addEdge(egde6);

System.out.println(“Graph is created properly”);

} catch (GraphConfigurationException ex) {

System.out.println(“::::::::::::::::::::::::::File Not Found:::::::::::::::::::::::::::::::::::::::::::::::::::::::”);

ex.printStackTrace();
}

// prepare runtime parameters - JMX is turned off
GraphRuntimeContext runtimeContext = new GraphRuntimeContext();
runtimeContext.setUseJMX(false);

GraphExecutor executor = new GraphExecutor();

/* System.out.println(“Grapgh Executer…” + executor);
System.out.println(“Grapgh Executer…” + institutionGraph);*/
try {
GraphExecutor.initGraph(institutionGraph);
} catch (ComponentNotReadyException e) {
System.out.println(“::::::::::::::::::::::::::File Not Found in Compontnt not ready:::::::::::::::::::::::::::::::::::::::::::::::::::::::”);
System.out.println(“Failed graph initialization!\n”
+ e.getMessage());
return false;
}

Future result;
try {
result = executor.runGraph(institutionGraph, runtimeContext);

Result rs = result.get();
System.out.println(result.isDone() + “>>>>>>>>>>>>>>>>>>>>>>>”);
if (result.isDone()) {
System.out.println(“Done”);
isDone = true;
}
if (!result.get().equals(Result.FINISHED_OK)) {
System.out.println(result.get().message());
// System.out.println(“Failed graph execution!”);
// return false;
}
} catch (Exception e) {
System.out.println(“Failed graph execution!\n” + e.getMessage());
// return false;
}
return isDone;
// return true;

}

13

Reformar class…

public ChildJoinReformat(){

}

public boolean init(){
//TransformationGraph institutionGraph=(TransformationGraph)graph;

try {
System.out.println(“JJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJ”+graph);
mylookup = graph.getLookupTable(“LookupTable0”);
mylookup.init();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

// create key
String lookupKeyStr={“Field1”};
RecordKey key=new RecordKey(lookupKeyStr,sourceMetadata[0]);
key.init();
mylookup.setLookupKey(key);
return true;
}

public boolean transform(DataRecord source, DataRecord target){

DataRecord employee=null;
employee=mylookup.get(source[0]);

if (employee==null) {// skip this one
errorMessage = “Order: " + source[0].getField(0).getValue() + " - no corresponding record in lookup table”;
return false;
}
System.out.println(“hello>>>>>>>>>>”+employee.getField(0).getValue());

//target[0].getField(0).setValue(source[0].getField(0).getValue());
//target[0].getField(1).setValue(source[0].getField(1).getValue());
//target[0].getField(2).setValue(source[0].getField(2).getValue().toString());
//target[0].getField(3).setValue(employee.getField(0).getValue().toString());
target[0].getField(“Field1”).setValue(employee.getField(0).getValue());
//target[0].getField(1).setValue(source[0].getField(0).getValue());
return true;
}
public void setGraph(TransformationGraph graph){
this.graph=graph;
}

14

where in the code is the infinite loop? did you try to debug it?

Jaro

15

Hi jaro,

Thanks a lot for great help…

Actually it was problem because of the duplicate data in the database…

Thanks,
Hanuman Mishra