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

37 comments:

Anonymous said...

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

Jason Weathersby said...

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

Anonymous said...

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

Jason Weathersby said...

What error are you getting?
Are you sure this
task.getAppContext().put("OdaJDBCDriverPassInConnection",connection) ;

connection object is valid?

Jestan Nirojan said...

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

Jason Weathersby said...

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

Anonymous said...

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

Cummins Replacement Parts said...

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! :)

Johannes said...

Hi,

I managed to run a report against a Database-Connection passed in via OdaJDBCDriverPassInConnection. (As suggested by your first code snippet). I'm doing this within an RCP Application having loaded the following BIRT-plugins:

org.eclipse.birt.report.engine
org.eclipse.birt.data
org.eclipse.birt.data.oda.jdbc

When shutting down my application I'm getting a nullpinter-exception in the close() methode of org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager.close(JDBCDriverManager.java:107) This is cause the ExtraDriverLoader doesn't get initialized and is null therefore.

Everything else is working fine. If I don't load the oda.jdbc-plugin I'm getting many other exceptions.

Any hints?

-orgler

Jason Weathersby said...

Orgler,

Can you log a bug to put a null check in before calling the close?

Jason

bucsie said...

this all looks great, but when designing the report - where can you set the datasource to use the connection you have just passed in the context?
the new data source dialog doesn't have an option for specifying that

Jason Weathersby said...

I am not sure what you are asking. In the design environment how are you creating a connection? There is no GUI for this. In the designer preferences there is an option to use a custom application context. It looks for classes in your workspace that implement the org.eclipse.birt.report.viewer.appcontext extension point. You can configure this to work like your runtime environment.

bucsie said...

This is really cumbersome then; So you managed to simplify the way to send a connection to the report, but it's a whole ordeal to use it!
Is there a tutorial for doing what you explained? (using a custom application context, implementing org.eclipse.birt.report.viewer.appcontext extension points ?)

My requirements were very simple. I have a "server" - an app that receives a request (give me report x with parameters y,z) and then replies with a byte[] containing the report. It was fairly easy to implement it, up to the point where the DB connection is defined in a configuration file and has to be passed to the report. I got stuck there; this article was great to explain that one would be able send the connection to the report, but then - when one starts designing the report, how can one get that connection and create a data source that would use it? It's like half of the puzzle is missing :)

Anonymous said...

I am a little confused by the term cumbersome. What exactly is cumbersome?

- Are you saying that it is hard to design the report?

- It is hard to pass the dataset?

There are approaches to all of this which require a bit of work, but once that work is done it will be re-usable and relatively straight-forward to everyone (IMHO).

bucsie said...

Hi Scott,

I meant that there are a lot of steps that require a lot of time to learn (for a very simple problem) - and the information is scarce on the subject. I mean I would have to understand what org.eclipse.birt.report.viewer.appcontext is about, then extend it, then find out how to use it in the report design, etc.

Anyway, I asked the same question on the Eclipse newsgroup and I got this:
"In report designer use the standard "jdbc datasource" und "jdbc dataset".

In your app first call
task.getAppContext().put("OdaJDBCDriverPassInConnection", conn);
and then
"runAndRender()"
your connection "conn" is used instead of the connection and/or datasource you defined directly in your rptdesign-file."


So actually there is no need to do what Jason said, it's done automatically by the framework.

Another solution I found is instead of passing a java.sql.Connection, I would pass as report parameters the connection properties as strings (driver class, DB URL, username, pass). Then I would use property binding on a defined data source: when designing the report I define a data source, then edit it and in the property binding tab I set the passed report parameters. Simple, once you find a blog talking about it :)

Nimish said...

SEVERE: Cannot open connection.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: There is an error in get connection, [ActuateDD][SQLServer JDBC Driver][SQLServer]Login failed for user 'sa'..
at org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager.doConnect(JDBCDriverManager.java:222)
at org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager.getConnection(JDBCDriverManager.java:179)
at org.eclipse.birt.report.data.oda.jdbc.Connection.connectByUrl(Connection.java:220)
at org.eclipse.birt.report.data.oda.jdbc.Connection.open(Connection.java:151) can anyone help?

Nimish said...

EngineConfig config = new EngineConfig();



config.setBIRTHome("E:/birtruntime/birt-runtime-2_3_2/birtruntime/ReportEngine");
config.getAppContext().put("OdaJDBCDriverClassPath", "E:/birtruntime/birt-runtime-2_3_2/birtruntime/ReportEngine/plugins/org.eclipse.birt.report.data.oda.jdbc_2.3.2.v20090331-1440/drivers/mysql/mysql-connector-java-5.0.4-bin.jar");

try{
Platform.startup( config );
IReportEngineFactory factory = (IReportEngineFactory) Platform.createFactoryObject( IReportEngineFactory.EXTENSION_REPORT_ENGINE_FACTORY );
IReportEngine engine = factory.createReportEngine( config );
IReportRunnable design = engine.openReportDesign("E:/Actuate/EqipmentAvailability/EqipmentAvailabilityDashBoard.rptdesign");
IRunTask task = engine.createRunTask(design);
//Set parent classloader for engine
//task.getAppContext().put(EngineConstants.APPCONTEXT_CLASSLOADER_KEY, RunTaskExample.class.getClassLoader());

//run the report and destroy the engine

task.run("E:/Actuate/GroupingTesting/Group.rptdocument"); //at this line is the error

task.close();

Jason Weathersby said...

Take this out:
config.getAppContext().put("OdaJDBCDriverClassPath", "E:/birtruntime/birt-runtime-2_3_2/birtruntime/ReportEngine/plugins/org.eclipse.birt.report.data.oda.jdbc_2.3.2.v20090331-1440/drivers/mysql/mysql-connector-java-5.0.4-bin.jar");


And just copy the mysql driver to

E:/birtruntime/birt-runtime-2_3_2/birtruntime/ReportEngine/plugins/org.eclipse.birt.report.data.oda.jdbc_2.3.2.v20090331-1440/drivers directory. Do not put it in a sub directory.

Jason Weathersby said...
This comment has been removed by the author.
Jason Weathersby said...

ReportEngine\plugins\
org.eclipse.birt.
report.data.oda.jdbc_2.5.1.v20090821\drivers

Nimish said...

it is still giving the same error i did what you said.

Jason Weathersby said...

Can you try running a report that uses the sample db instead of MySQL to isolate the issue? Also it looks like the orignal error is a credentials issue. Are you sure you have entered the right user/pwd?

Nimish said...

It is surely not a credentials issue because i have the server at my system only and using the same username and password i am able to view the report from Actuate BIRT.Also i am able to connect to the server using that username and password.

Nimish said...

I am not using MySql. I am using using Microsoft SQL Server 2005

Jason Weathersby said...

I thought this was a MySQL driver:
mysql-connector-java-5.0.4-bin.jar

Nimish said...

ya i am sorry about that.... but i am using N-sqlserver.jar which i have put in the drivers directory. it is using this driver as it is giving error in a getConnection method of a class which is present in this jar file.

Jason Weathersby said...

Can you log a bug for this?

Nimish said...

i dont know how to do that?

Jason Weathersby said...

Take a look at this page:
http://www.eclipse.org/birt/phoenix/community.php

VasyaL said...

Hi. What about situation when there are 2 data sources for different databases. How do I specify which connection to substitute? Thanks,
Vasiliy.

VasyaL said...
This comment has been removed by the author.
Jason Weathersby said...

You will have to know which you are wanting in the task. Each task can only process one report so this should not be difficult. If you do not know you could always use the DE API to read the datasource info from the report design and then put an if block around the code that sets OdaJDBCDriverPassInConnection.

Anonymous said...

Hi. Thanks for your reply. Though, I didn't quit get the idea. I have a report which connects to two databases (i.e. has two datasources). How do I tell the engine which connection to substitute? I tried using the API to get info about the datasource and then substituting it with supplied connection, but as soon it substitutes it the other connection gets substituted also.
Vasiliy.

Jason Weathersby said...

I thought these where two different reports. I do not think this feature will help in your case. Can you use JNDI for both datasources and setup connection pooling in your app server?

Anonymous said...

Unfortunatelly - no. It is a Java application wrapped as a Windows service. I cannot use BIRT's ability to set up connection by its own, since I need to establish a trusted connection to MS SQL server. As soon as my Java application sets up a trusted connection, BIRT is unnable to set up the same since it uses another ClassLoader which leads to infamous UnsatisfiedLinkError. :(.
Is there a chance to have an option to specify quilified DataSource name for substitution like for ex.: OdaJDBCDriverPassInConnection.DataSourceName in future releases of BIRT?
Thanks for your help and time!

Jason Weathersby said...

Can you open a bug for this? There may be another way around this, and bugzilla is the best discussion area for it.

Anonymous said...

Interesting post you got here. It would be great to read something more concerning that topic. The only thing this blog needs is a few pictures of some gizmos.
David Stepman
Phone jammers