Tuesday, January 16, 2007

BIRT Connection Pooling Continued

Since writing a post on using a supplied connection with BIRT, many changes have occurred in the BIRT Project. Specifically connection pooling has been addressed with a JNDI property. This property can be set when using a JDBC data source.




To see how this can be used with Tomcat, see the
BIRT Wiki

If this does not meet your needs, the Data Tools project now supplies an extension point that can be used to alter the behavior of the standard BIRT JDBC driver. This extension point named org.eclipse.datatools.connectivity.oda.consumer.driverBridge can be implemented to intercept calls made to the JDBC driver and override the standard ODA calls like getConnection. If a user did not wish to use JNDI for connection pooling, but wanted to supply a connection through another means, this could be implemented using this extension point.

The driverTye and bridgeId attributes must be supplied for the extension point. The driverType specifies the ODA driver to which the bridge is applied. For the JDBC driver this should be set to org.eclipse.birt.report.data.oda.jdbc.OdaJdbcDriver.
The bridgeId specifies the element id of the extension that implements the ODA runtime. When using this to change the behavior of the JDBC driver this should be an extension that extends the JDBC runtime.

If you are using the Report Engine API to run reports, a connection object can be passed to engine using the application context as follows:


//Report Engine API snippet
HashMap contextMap = new HashMap();
contextMap.put( EngineConstants.APPCONTEXT_HTML_RENDER_CONTEXT, renderContext );

//add the connection object to the map
contextMap.put( "org.eclipse.birt.report.data.oda.subjdbc.SubOdaJdbcDriver", myconnectionobject );

task.setAppContext( contextMap );








A plug-in could then be created that implements the driverBridge extension point
to retrieve and use this connection. This plug-in would implement two extension points. The first should be the driverBridge and have values similar to the following:

<extension
id="org.eclipse.birt.report.data.testjdbc"
point="org.eclipse.datatools.connectivity.oda.consumer.driverBridge">

<bridge
driverType="org.eclipse.birt.report.data.oda.jdbc.OdaJdbcDriver"
bridgeId="org.eclipse.birt.report.data.testjdbc">
</bridge>
</extension>




The driverType specifies that the bridge will be applied to the BIRT JDBC driver.
The bridgeId specifies an element id of testjdbc, which can be defined in the same plug-in.

<extension
point="org.eclipse.datatools.connectivity.oda.dataSource">
<dataSource
odaVersion="3.0"
driverClass="org.eclipse.birt.report.data.testjdbc.MyJdbcDriver"
defaultDisplayName="Sample Driver Bridge"
setThreadContextClassLoader="false"
id="org.eclipse.birt.report.data.testjdbc"/>
</extension>




This extension must implement the org.eclipse.datatools.connectivity.oda.dataSource
extension point. This is the runtime portion of an ODA driver. Because this bridge is being applied to the BIRT JDBC driver we can just extend the OdaJdbcDriver and change the behavior of the setAppContext and getConnection methods. The code below illustrates retrieving the connection from the application context. If the connection object does not exist in the application context, the default JDBC driver is used to retrieve the connection. This will allow the plug-in to be used within the designer using the credentials supplied in the Data Source editor and when deployed to an application that supplies the connection through the application context.


package org.eclipse.birt.report.data.testjdbc;
import java.sql.Connection;
import java.util.HashMap;
import java.util.Properties;

import org.eclipse.birt.report.data.oda.jdbc.*;
import org.eclipse.datatools.connectivity.oda.IConnection;
import org.eclipse.datatools.connectivity.oda.OdaException;

public class MyJdbcDriver extends OdaJdbcDriver {

private Connection passedInConnection;


public void setAppContext( Object context ) throws OdaException
{
HashMap ctx = (HashMap)context;
passedInConnection = (java.sql.Connection)ctx.get("org.eclipse.birt.report.data.oda.subjdbc.SubOdaJdbcDriver");

}

public IConnection getConnection(String connectionClassName) throws OdaException
{
if( passedInConnection != null){
return new appContextDBConnection();
}else{
return new org.eclipse.birt.report.data.oda.jdbc.Connection();
}
}



private class appContextDBConnection extends org.eclipse.birt.report.data.oda.jdbc.Connection
{


public void open(Properties connProperties) throws OdaException
{
super.jdbcConn = passedInConnection;

}

public void close( ) throws OdaException
{
if ( jdbcConn == null )
{
return;
}
//should have call to return connection to the pool
jdbcConn.close();
jdbcConn = null;
}



}
}





The source for this plug-in is available here.

40 comments:

Atanas Dimitrov said...

The code works like a charm on Linux.
I had only one issue compiling it:
I had to go under Window->Preferences and modify the "Plugin Dev" - "Target Platform" settings: the "Location" there must be set to the Ecplise installation directory otherwise your existing plugins will not be recognized and you will get build errors.

Also, I needed this in a deployable form for a tomcat application so after building the project I did File->Export and I chose "Deployable plug-ins". It spits out a plugins directory with a single JAR file in it, which (the JAR file) must be copied into the "platform/plugins" directory that the BIRT RE uses.
Cheers and great job Jason!

Atanas Dimitrov said...

I forgot to mention that to compile this code simply unzip it then do File->Import select "General" - "File System" and then select the extracted directory ...

Anonymous said...

I got a 403 Forbidden error when i tried to download the source code....

Anonymous said...

Can you try downloading again?

Anonymous said...

Thanks so much for this code! It works perfectly and was a real lifesaver for me.

The only thing I was unsure of was what kind of project to import the code into. I ended up making a new Plugin Project, accepted all of the defaults and then deleted the stuff I didn't seem to need. If there is a more direct way, it probably wouldn't hurt to post it for all of us out there who are clueless when it comes to plugins.

Thanks Again!

Anonymous said...

Hi jason
im luiz from brazil and id like i think you could help me
im working in a oracle pl/sql project and
in some of these menus of the system i need to send to report informations about 2 fields (example: name(list of values - lov and birth date-list of values too)
in the first time the report bring the data but in the second time dont update the informations of the fields
how i can proceed to generate reports with the users choice in the fields(name and birth date)? how i can create parameters for it? do you have a
article to send to me , please
hope for a answer as soon as you can
sorry for my english but i dont speak as well
tks so much
luiz(brazil)(ktlubr@click21.com.br)

Atanas Dimitrov said...

"The only thing I was unsure of was what kind of project to import the code into."

The easiest thing you can do is to go to File->Import then select "Existing Projects into Workspace" under "General" then select the source directory and that's it...

Shawn said...

I just have a question about this, what data source do I specify in my report design file? There's an oda-data-source element that normally would have the connection string, userid and password. Since I'm using a supplied connection object, what should I put there instead? Thanks.

Jason Weathersby said...

Shawn,

You need to specify a JDBC driver, because this is the one the filter is intercepting.

Jason

Remo said...

I guess my question is similar to Shawn's - though I didn't understand the proposed solution.
I'm trying to use a supplied connection within a standalone birt application. I unzipped the provided plug-in into the "plugins" directory of my ReportEngine home directory.

Now my question: What exactly do I have to specify as the data source of my report design file? I need to fill in "Driver Class", "Database URL" and so on. Before, I had the information there to directly connect to the DB, which worked perfectly. But now, what do I have to change in order to use the supplied connection?

Thanks!

Jason Weathersby said...

If you set this up as the example shows then you will need to use a jdbc plugin and specify the jdbc properties. Your code should get executed before a connection using the credentials is made.

Jason

Remo said...

Ok, in that case I do not understand how to apply the newly created testjdbc plug-in to my report design file within eclipse.
i.e. where in the report designer can I specify that my plug-in should be used in order to use the supplied connection?

Thanks!

Jason Weathersby said...

This is determined by the driverType in the new extension. You do not set it in the designer. If the bridge plugin is in the plugins directory it will intercept all jdbc calls that use the jdbc ODA which is the the driverType in this example.

Jason

Anonymous said...

Following Remo's post - I don't understand how this fits together when using the Report Engine API to run reports in an Application Server.

I have a Spring managed bean that contains a DataSource that I want to supply to the Report Engine. I tried using this code, but it does not seem to work:

mapcontextMap.put("org.eclipse.birt.report.data.oda.subjdbc.SubOdaJdbcDriver", myconnectionobject);
task.setAppContext(contextMap);

Am I missing something? I'm using BIRT 2.1.2.

Jason Weathersby said...

If you setup the filter for the JDBC ODA, any report that uses a JDBC connection will call the filter. Does that make sense?

Jason

Prashanth said...

In our project we cannot use JNDI feature of BIRT 2.2. We are planning to use the plugin you described in the article. Earlier about a year ago, we have prototyped the connection pooling solution using your subjdbc eclipse plugin. Unfortunately, we lost that plugin when my computer crashed.

Is there any binary distribution for the plugin which you discussed in the article? If so please send us a link or email us the plugin binaries as as attachment. Also, if you still happen to have subjdbc eclipse plugin, please share that as well. We tried to find that through google. But, we could not find it.

Anonymous said...

Hi Jason, I am trying with BIRT with Spring.

I am passing Spring JDBC connection to task.
Connection conn = DataSourceUtils.getConnection(dataSource);

appContext.put("org.eclipse.birt.report.data.oda.subjdbc.SubOdaJdbcDriver", conn);

task.setAppContext(appContext);

Still, reportEngine is not using runtime jdbc connection. I am missing any configuration in the report? I am using Spring 2.2

Thanks for your help.

Jason Weathersby said...

Can you send me the report you are using?

Jason

Anonymous said...

This plugin is exactly what I want. Unfortunatly I'm using the birt designer and I don't know how to create the deployable plugin.

Is there an mean to find the deployable plugin anywhere on the web?

Jason Weathersby said...

you can use the export wizard in the plugin editor. Here is a link to an uploaded version of the deployable plugin.

http://download.eclipse.org/birt/downloads/examples/extension/driverBridge/org.eclipse.birt.report.data.testjdbc_1.0.0.jar

Jason

Jason Weathersby said...

Trying again, click here for the deployable version

Anonymous said...

Thanks a lot for the plugin and the fast answer.

G.

Anonymous said...

I'm new to BIRT (eclipse environment). When you talked about passing engine to use the application context in Report Engine API, do I need to check out the source from the CVS repository? I did check out the BIRT source. I looked into ReportEngine.java but I didn't find the lines you mentioned. I'm on BIRT 2.2. Thanks!

Jason Weathersby said...

The code is using the Report Engine API. This is available in the birt runtime download. You set the application context using one of the engine tasks. For example if I create a RunAndRenderTask task, I can set the appcontext using
task.setAppContext();
No need to get anything out of CVS.

Jason

Anonymous said...

Jason, thanks for your quick response. I can understand why we're setting the connection thru the application context. Sorry, but where do I call task.setAppContext()?

Anonymous said...

In your example, you have added 2 lines in ReportRunner.java. As what you have mentioned, I downloaded birt runtime (I have all-in-one downloaded on another directory). I can find all the jars and plugins but I didn't see any source code. Where do I call the tast.setAppContext() to set the connection? Thanks again.

Jason Weathersby said...

You have to write the code.
Take a look at:
http://wiki.eclipse.org/
Separate_Run_and_Render_%28BIRT%29_2.1

or

http://wiki.eclipse.org/
Simple_Execute_%28BIRT%29_2.1

Remo said...

I use BIRT to generate reports from data contained within a HSQLDB.
The report engine is built into a rich client which also has access to the db. HSQLDB accepts concurrent connections (in my case from BIRT and from the application) as long as they are coming from the same JVM.

With BIRT 2.1 this was no problem. Now with BIRT 2.2 however, I'm no longer able to access the db. What has changed? Is the new BIRT version creating a new VM to access the db or something similar?

Amar Shah said...

I tried to download this plugin as it is and copied it to BIRT plugins directory after adding few sysout. It seems that I am not OdaDriver access is not intercepted by this driver. I am not getting any SOP.

In BIRT Report I used the same oracle driver, url and other properties.

Please let me know if I have missed some thing?

Jason Weathersby said...

Amar,

Did you package the plugin in deployable format? If you get it deployed it should work with all JDBC datasources including the BIRT sample db.

Jason

saikiran said...

Hi jason

I am new to BIRT. And trying the below:

I am using Derby database(Embedded database) and BIRT to generate PDF reports using the design file, where we are specifying the connection URL.

I am also using the same derby database for application access.
Since the BIRT is running another JVM i am not able to start the Derby embedded database as derby has a limitation. The report is coming if i explictly stop the database from my application before generating the report. This is leading to performance problems.

Can you explain me how can i use the BIRT Connection pooling in this case? And also can you send the sample on this?

Jason Weathersby said...

saikiran

I am not sure what is happening here. Are you using JDBC to access the derby database?

Jason

saikiran said...

Yes i am using the jdbc connection. finally i get rid of the problem. The sample jar given by you is also working fine for me.

The code given by you is also working correct. Let me share the mistake i made. I created a plugin.xml and added the extension point and in the build.properties i forgot to select this newly added plugin.xml. :)

Thanks a lot for the wonderful POST

saikiran said...

Hi Jason

Couple of questions:

First one:

If we are using BIRT Runtime to generate the reports, as i know it is launching a OSGi framework for doing this.

IS this OSGi framework runs on a seperate JVM or the same JVM as the parent application?

Second one:

If i am developing Eclipse based applications, What is the best way to integrate the BIRT Runtime? I create a plugin which includes the birt runtime and provides a service to get the path to this BIRT runtime?

Thanks a lot

Jason Weathersby said...

Saikiran,

1 - Shoule be the same VM.

2 - Alot of different ways to do this. The way you describe is fine. You can also look at
http://wiki.eclipse.org/RCP_Example_%28BIRT%29_2.1 which has an example of using the viewer plugin and an example of using the engine plugin for API work.

Jason

aiyipianni said...
This comment has been removed by a blog administrator.
aiyipianni said...
This comment has been removed by a blog administrator.
sun4suns said...

Hi friend, Jason !
Lucky me, I've read your this entry in many times. Thank for this posting !
In focus, i've just being used BIRT to embed it into my web application for 3 months.
But now, my problem is that whenever i were using BIRT Report Viewer to view my reports. BIRT would open a new connection to my DB. After that, I closed the Viewer GUI (as HTML) but BIRT didn't close the connection automatically. Sothat, it made my system be Out Of Memory Exception.
If i do as following as this guide link
... Shall it help me solving my issue above?

Jason Weathersby said...

What version of BIRT are you using?
The connection should be closing automatically.

sun4suns said...

Hi Jason,
This is BIRT birt-rcp-report-designer-2_3_0 verion which i'm using to design and view the reports on my web apps.
By the way, can you help me where can i download the package that contains these classes as org.eclipse.birt.report.data.oda.jdbc.OdaJdbcDriver
org.eclipse.birt.report.data.oda.jdbc.LogConfig

p.s: I've just download a latest plug-in of Report Engine of BIRT. And then updating the one into the plug-in of BIRT's RPC.

Thank you !