http://birtworld.blogspot.com/2008/11/birt-connection-pooling-continued-again.html
The Eclipse Business Intelligence and Reporting Tools project is an open-source project focused on the development and delivery of framework tools for reporting and business intelligence within the Eclipse platform.
Wednesday, November 05, 2008
BIRT Connection Pooling Continued Again
With the release of BIRT 2.3.1 connection pooling options have been extended.
In a prior post, I built an example that showed how to implement connection pooling using the driverBridge extension point. While this method is useful, with BIRT 2.3.1 there is a much easier method for passing BIRT an already created connection object.
You can now supply a connection by adding the connection object to BIRT’s application context object. The key for this object is OdaJDBCDriverPassInConnection. So to pass in the connection while using the Report Engine API, use code similar to:
IReportRunnable design = null;
//Open the report design
design = engine.openReportDesign("Reports/passinconnection.rptdesign");
IRunAndRenderTask task = engine.createRunAndRenderTask(design);
HTMLRenderOption options = new HTMLRenderOption();
options.setOutputFileName("output/resample/passinconnobj.html");
options.setOutputFormat("HTML");
task.setRenderOption(options);
task.getAppContext().put("OdaJDBCDriverPassInConnection", this.getConnection());
task.run();
task.close();
This assumes you already have the report engine started. The getConnection method in this example simply creates a java.sql.Connection to my database. You will need to create your own function. Also keep in mind that the BIRT JDBC plugin will close the connection when it has finished with it, so if you plan on using the object in multiple tasks, you will need to reopen it. There is a bug to allow the closing of the connection to be optional -Bugzilla Entry. This application context setting should be applied at the task level.
If you wish to set it in the Example Viewer’s application context take a look at this wiki entry.
These are the ways that a connection can now be manipulated in BIRT:
1-Property binding
2-JNDI
3-Script data set
4-DataSource.beforeOpen() event.
5–driverBridge extension
6-Application Context Ojbect (described here)
Another setting the JDBC plugin now provides is OdaJDBCDriverClassPath, which allows setting the classpath for locating drivers. This prevents the user from having to put JDBC drivers in the drivers directory of the JDBC plugin. This should be set on the EngineConfig object and not on the task object.
config = new EngineConfig( );
config.setBIRTHome("C:\\birt\\birt-runtime-2_3_1\\birt-runtime-2_3_1\\ReportEngine");
config.getAppContext().put("OdaJDBCDriverClassPath", "c:/birt/mysql/mysql-connector-java-5.0.4-bin.jar");
Platform.startup( config );
IReportEngineFactory factory = (IReportEngineFactory) Platform.createFactoryObject( IReportEngineFactory.EXTENSION_REPORT_ENGINE_FACTORY );
engine = factory.createReportEngine( config );
8 comments:
Hi Jason!
I have to implement connection pooling for a rerport similar to following http://wiki.eclipse.org/Java_-_Execute_Modified_Report_%28BIRT%29
I used your code segment in my ReportRunner class as follows,
...............................
DealerLevelDetails.setupDataSourceAndConnection();
ServletContext sc = req.getSession().getServletContext();
this.birtReportEngine = BirtEngine.getBirtEngine(sc);
//setup image directory
HTMLRenderContext renderContext = new HTMLRenderContext();
renderContext.setBaseImageURL(req.getContextPath() + "/images");
renderContext.setImageDirectory(sc.getRealPath("/images"));
// create ReportDesignHandle object and render the report using IRunAndRenderTask task
String path = sc.getRealPath("/");
BuildDynamicReport buildDynamicReport = new BuildDynamicReport(distributor, transActionType, region, fromDate, toDate, path, locale);
ReportDesignHandle designHandle = buildDynamicReport.createDynamicTable();
IReportRunnable design = birtReportEngine.openReportDesign(designHandle);
IRunAndRenderTask task = birtReportEngine.createRunAndRenderTask(design);
HashMap contextMap = new HashMap();
contextMap.put(EngineConstants.APPCONTEXT_HTML_RENDER_CONTEXT, renderContext);
// using a exiting connection used to create temporary cache tables.
HTMLRenderOption options = new HTMLRenderOption();
// Set the output to servlet output stream
options.setOutputStream(resp.getOutputStream());
options.setOutputFormat("html");
task.setAppContext(contextMap);
task.setRenderOption(options);
// DealerLevelDetails.getConnection() returns an existing connection task.getAppContext().put("OdaJDBCDriverPassInConnection",DealerLevelDetails.getConnection());
task.run();
task.close();
...................
Now report is not running and It gives error messgages like ... DataSource for ds is null . My ReportRunner class still using a buildDataSource() method as follows.
public void buildDataSource( ElementFactory designFactory, ReportDesignHandle designHandle ) throws SemanticException
{
OdaDataSourceHandle dsHandle = designFactory.newOdaDataSource(
"Data Source", "org.eclipse.birt.report.data.oda.jdbc" );
dsHandle.setProperty( "odaDriverClass",
"org.eclipse.birt.report.data.oda.sampledb.Driver" );
dsHandle.setProperty( "odaURL", "jdbc:classicmodels:sampledb" );
dsHandle.setProperty( "odaUser", "ClassicModels" );
dsHandle.setProperty( "odaPassword", "" );
designHandle.getDataSources( ).add( dsHandle );
}
...................
What I want to know is , Is it possible to reusue Connection object in a Report similar to
http://wiki.eclipse.org/Java_-_Execute_Modified_Report_%28BIRT%29
Thanks
-Jestan
Jestan,
The error you are stating sounds like you are not setting the datasource propery of the data set that you have dynamically added. BTW that is really not connection pooling as you are adding a datasource to the report definition, which will get opened when the report is executed.
Jason
Hi Jason !
thanks for your reply. Actually my problem is , I am having a ReportRunner class like http://wiki.eclipse.org/Java_-_Execute_Modified_Report_%28B
and I have to modify the it to enable connection pooling, and I have done some changes in
task.getAppContext().put("OdaJDBCDriverPassInConnection",connection) ; But now I am having some problem in following code.
is there a way to modify following code to enable connection pooling..
........................................
void buildDataSource() throws Exception {
OdaDataSourceHandle dsHandle = designFactory.newOdaDataSource("Data Source", "org.eclipse.birt.report.data.oda.jdbc");
if(BirtPorpertyLoader.topTransferReportDS.equals("dataguard")){
dsHandle.setProperty("odaDriverClass", BirtPorpertyLoader.dataguardDriverClass);
dsHandle.setProperty("odaURL", BirtPorpertyLoader.dataguardConnectionUrl);
dsHandle.setProperty("odaUser", BirtPorpertyLoader.dataguardConnUsername);
dsHandle.setProperty("odaPassword", BirtPorpertyLoader.dataguardConnPassword);
}
else if(BirtPorpertyLoader.topTransferReportDS.equals("live")){
dsHandle.setProperty("odaDriverClass", BirtPorpertyLoader.liveDriverClass);
dsHandle.setProperty("odaURL", BirtPorpertyLoader.liveConnectionUrl);
dsHandle.setProperty("odaUser", BirtPorpertyLoader.liveConnUsername);
dsHandle.setProperty("odaPassword", BirtPorpertyLoader.liveConnPassword);
}
else {
throw new Exception("TopupTransfer report's respective datasource configuration is wrong. 'live' and 'dataguard' are the possible values.");
}
designHandle.getDataSources().add(dsHandle);
}
...............................
...............................
private void buildFooterDataSet(String dataSetName, String transType) throws SemanticException {
OdaDataSetHandle dsHandle = designFactory.newOdaDataSet(dataSetName,
"org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet");
dsHandle.setDataSource("Data Source");
String tableName = "";
if (transType.equals(BuildDynamicReport.RECHARGE_KEY)) {
tableName = this.rechargeDealerLevelDetails.cacheTableName;
}
else if (transType.equals(BuildDynamicReport.TRANSFER_KEY)) {
tableName = this.transferDealerLevelDetails.cacheTableName;
}
StringBuffer buf = new StringBuffer();
buf.append(" SELECT CREDIT_AMOUNT AS DENOM , COUNT(CREDIT_AMOUNT) AS DENOM_COUNT , (CREDIT_AMOUNT * COUNT(CREDIT_AMOUNT)) AS TOTAL_AMOUNT");
buf.append(" FROM "+tableName +" CONNECT BY PRIOR MSISDN = PARENT_MSISDN START WITH MSISDN = '"+this.distributor+"' GROUP BY CREDIT_AMOUNT ORDER BY CREDIT_AMOUNT ");
dsHandle.setQueryText(buf.toString());
designHandle.getDataSets().add(dsHandle);
}
..................................
I have to run the above query using an existing connection object. To do that how i can modify above two methods. Can you give some hints !
Thank you.
-Jestan
What error are you getting?
Are you sure this
task.getAppContext().put("OdaJDBCDriverPassInConnection",connection) ;
connection object is valid?
Hi Jason!
The connection object is valid. BWT can you tell me , how I can modify following lines to use existing connection object to build the DataSet to run the query...
OdaDataSetHandle dsHandle = designFactory.newOdaDataSet(dataSetName,
"org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet");
dsHandle.setDataSource("Data Source");
Thank you.
-Jestan
Jestan,
If you modify the report using the DE API and then run the report with the task.getAppContext().put("OdaJDBCDriverPassInConnection",connection); This should use the connection you supply. This will be the case as long as you add a JDBC connection.
Jason
Hi Jason!
I have solved the "Data source null" problem by using one of your earlier blog.
http://birtworld.blogspot.com/2005/12/using-supplied-connection-with-birt.html
It works perfectly and was a real lifesaver for me. Thanks a lot.
Jestan
Great tutorial! Very informative in how well worded and descriptive you were! You know they say that if one knows how to describe what they want really well, then life is just as good as how you describe it :)
Its great for people who feel like time is running against them and then land on your blog and feel like a whole burden was just lifted off of their shoulder.. I admire and respect people who take
time to make it easier for others.. Thanks a bunch! :)