Using other DB connection

CloverDX Platform
1

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 ?

thanks.

2

Hello,
I don’t exactly understand your problem, but if you use CloverETL from java code, you can create DBConnection object, with properties as you need (JNDI will be supported since 2.5 version only).

3

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.

4

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”);

5

  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?

  1. 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?
6

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.