currently, I have my DB configuraton defined in the clover XML graph file.
I am embedding this clover xml file to my Java program. Is there a way for me to use other DB connection (for example datasource in JNDI or DriverManager), instead of using the DB conn in the clover XML graph ?
<Connection>
thanks.
I am embedding this clover xml file to my Java program. Is there a way for me to use other DB connection (for example datasource in JNDI or DriverManager), instead of using the DB conn in the clover XML graph ?
<Connection>
thanks.
-
Below is my Java code to load the XML graph file.
TransformationGraphXMLReaderWriter graphReader = new TransformationGraphXMLReaderWriter(properties);
graph = graphReader.read(new FileInputStream(graphFile));
graph.dumpGraphConfiguration();
GraphRuntimeContext runtimeContext = new GraphRuntimeContext();
runtimeContext.setUseJMX(false);
GraphExecutor executor = new GraphExecutor();
GraphExecutor.initGraph(graph);
executor.runGraph(graph, runtimeContext);
From Clover javadoc, the TransformationGraph.addConnection(Iconnection conn) is expecting an object that implements org.jetel.database. Iconnection.
My question is how can I inject a non-Clover DB connection (which does not implement org.jetel.database. Iconnection) to my loaded graph object?
Thanks again. -
It is impossible, but you can store your connection properties in file, eg.: in db2.cfg file driverLibrary=file\:/lib/db2_v9_db2driver_for_jdbc_sqlj/db2jcc.jar;file\:/lib/db2_v9_db2driver_for_jdbc_sqlj/db2jcc_license_cu.jar
user=userName
dbDriver=com.ibm.db2.jcc.DB2Driver
name=DB2Connection
password=psw
threadSafeConnection=true
dbURL=jdbc\:db2\://hostname\:50001/database
and then create new DBConnection object from this data:
graph.addConnection(new DBConnection("my_connection","db2.cfg"); -
1) My objective is to reuse DB connection specially from existing DB connection pool. Why does Clover requires DB connection object to implement IConnection?
2) I removed the Connection element from my graph file. In my Java code I instantiated a DBConnection object with DB properties.
// build the DBConnection
Properties p = new Properties();
p.setProperty("user", DBUser);
p.setProperty("password", DBPassword
p.setProperty("dbDriver", "oracle.jdbc.OracleDriver");
p.setProperty("name", "oracleConn");
p.setProperty("passwordEncrypted","false");
p.setProperty("dbURL", DBServer);
dbconn = new DBConnection ("conn3", p);
dbconn.setThreadSafeConnections(false);
My graphfile has XLS Data Reader which reads an XLS file of 3 sheets, data of each sheet is process sequentially by a Reformat component and inserted into a DB Table.
//java code loops to process the 3 sheets using the same graph, but with a different sheetname.
Properties p2 = new Properties();
for (int i = 0; i < f.getNumsheets(); ++i) {
String sheetName = f.sheetNames[i];
p2.setProperty("sheetname", sheetName);
TransformationGraph graph = null;
TransformationGraphXMLReaderWriter graphReader = new TransformationGraphXMLReaderWriter(p2); <--- pass property with new sheetname
try {
graph = graphReader.read(new FileInputStream(graphFile)); <-- read the XML Graph file
graph.addConnection(dbconn); <---- pass dbconn to graph file
// dump the graph
graph.dumpGraphConfiguration();
//prepare runtime parameters - JMX is turned off
GraphRuntimeContext runtimeContext = new GraphRuntimeContext();
runtimeContext.setUseJMX(false);
GraphExecutor executor = new GraphExecutor();
GraphExecutor.initGraph(graph);
executor.runGraph(graph, runtimeContext);
} catch (Exception e) {
logger.error("Exception occurred: ", e);
logger.error("Failed graph execution!\n" + e.getMessage());
return;
}
}
The code loops 3 times on the for-loop. However only the data from the LAST Sheet file was inserted to DB?
Do you know why this is happening? what happen to the first 2 sheets?
3) If I include the Connection element into my Graph file, and remove the graph.addConnection(dbconn) from my Java code. Data from all 3 sheets of the XLS file are processed and inserted into the DB table. Doing this way requires a new DB connection for each execution of a graph file. As you may know, instantiating, opening and closing for each DB connection takes time specially for XLS files with large data and more sheets per file. Is there a better way to re-use DB connections in multiple graph executions? -
Hi, I slightly modified your code for better messaging: import java.io.FileInputStream;
import java.util.Properties;
import java.util.concurrent.Future;
import org.jetel.connection.DBConnection;
import org.jetel.graph.Result;
import org.jetel.graph.TransformationGraph;
import org.jetel.graph.TransformationGraphXMLReaderWriter;
import org.jetel.graph.runtime.EngineInitializer;
import org.jetel.graph.runtime.GraphExecutor;
import org.jetel.graph.runtime.GraphRuntimeContext;
public class test {
public static void main(String[] args) {
EngineInitializer.initEngine("../cloveretl.gui/lib/plugins", "../cloveretl.gui/lib/bin/defaultProperties", null);
DBConnection conn = new DBConnection("my_connection", "oracle.cfg");
conn.setThreadSafeConnections(false);
String[] sheetNames = new String[]{"Sheet1", "Sheet2", "Sheet3"};
Properties p2 = new Properties();
for (int i = 0; i < sheetNames.length; ++i) {
String sheetName = sheetNames[i];
p2.setProperty("sheetname", sheetName);
TransformationGraph graph = null;
TransformationGraphXMLReaderWriter graphReader = new TransformationGraphXMLReaderWriter(p2);
try {
graph = graphReader.read(new FileInputStream("test.grf"));
} catch (Exception e1) {
e1.printStackTrace();
return;
}
graph.addConnection(conn);
// dump the graph
graph.dumpGraphConfiguration();
//prepare runtime parameters - JMX is turned off
GraphRuntimeContext runtimeContext = new GraphRuntimeContext();
runtimeContext.setUseJMX(false);
GraphExecutor executor = new GraphExecutor();
Future<Result> result;
try{
result = executor.runGraph(graph, runtimeContext);
while (result.isDone()) {;}
if (!result.get().equals(Result.FINISHED_OK)){
System.out.println(result.get().message());
System.out.println("Failed graph execution!");
return;
}else{
System.out.println("Execution finished succesfully");
}
}catch (Exception e) {
System.out.println("Failed graph execution!\n" + e.getMessage());
return;
}
}
}
}
And graph "test.grf" is:<?xml version="1.0" encoding="UTF-8"?>
<Graph author="avackova" created="Thu May 29 09:17:59 CEST 2008" guiVersion="1.10" id="1212046109215" licenseType="Evaluation license." modified="Thu May 29 10:17:31 CEST 2008" modifiedBy="avackova" name="test" revision="1.17">
<Global>
<Metadata id="Metadata0" previewAttachmentCharset="">
<Record fieldDelimiter="|" name="test" recordDelimiter="\n" type="delimited">
<Field name="name" type="string"/>
<Field name="value" type="integer"/>
</Record>
</Metadata>
</Global>
<Phase number="0">
<Node dbConnection="my_connection" dbTable="test" id="DB_OUTPUT_TABLE0" type="DB_OUTPUT_TABLE"/>
<Node fileURL="test.xls" id="XLS_READER0" sheetName="${sheetname}" startRow="2" type="XLS_READER"/>
<Edge fromNode="XLS_READER0:0" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 0 (out)" toNode="DB_OUTPUT_TABLE0:0"/>
</Phase>
</Graph>
It works properly and data from all three sheets are written to database.
Please sign in to leave a comment.
Comments 5