# Using other DB connection

**URL:** <https://forum.cloverdx.com/t/using-other-db-connection/319>\
**Category:** CloverDX Platform\
**Created:** [May 21, 2008, 12:00am UTC](https://forum.cloverdx.com/t/using-other-db-connection/319 "2008-05-21T00:00:00Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![bpilla](https://avatars.discourse-cdn.com/v4/letter/b/ac8455/32.png) [@bpilla](https://forum.cloverdx.com/u/bpilla)\
**Post date:** [May 21, 2008, 12:00am UTC](https://forum.cloverdx.com/t/using-other-db-connection/319/1 "2008-05-21T00:00:00Z")

</div>

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.

---

<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 23, 2008, 10:41am UTC](https://forum.cloverdx.com/t/using-other-db-connection/319/2 "2008-05-23T10:41:17Z")

</div>

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).

---

<div class="post-metadata">

**Author:** ![bpilla](https://avatars.discourse-cdn.com/v4/letter/b/ac8455/32.png) [@bpilla](https://forum.cloverdx.com/u/bpilla)\
**Post date:** [May 23, 2008, 6:39pm UTC](https://forum.cloverdx.com/t/using-other-db-connection/319/3 "2008-05-23T18:39:27Z")

</div>

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.

---

<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 26, 2008, 6:51am UTC](https://forum.cloverdx.com/t/using-other-db-connection/319/4 "2008-05-26T06:51:02Z")

</div>

It is impossible, but you can store your connection properties in file, eg.: in db2.cfg file

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

---

<div class="post-metadata">

**Author:** ![bpilla](https://avatars.discourse-cdn.com/v4/letter/b/ac8455/32.png) [@bpilla](https://forum.cloverdx.com/u/bpilla)\
**Post date:** [May 28, 2008, 6:46pm UTC](https://forum.cloverdx.com/t/using-other-db-connection/319/5 "2008-05-28T18:46:29Z")

</div>

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?

---

<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 29, 2008, 8:25am UTC](https://forum.cloverdx.com/t/using-other-db-connection/319/6 "2008-05-29T08:25:47Z")

</div>

Hi, I slightly modified your code for better messaging:

```auto
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:

```auto
<?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.