Friday, December 02, 2005

Using a supplied connection with BIRT

In an earlier post, I said that I would try to use the new setAppContext feature within BIRT to implement connection pooling. Well in truth, I didn’t implement connection pooling but instead built an ODA that uses a supplied connection in place of the one BIRT would normally establish on its own. To understand where this would be useful, imagine that you have a J2EE application built that already has a connection pool set up. Ideally when your application calls a BIRT report, it would be nice if BIRT would use a connection that is already created. This is where the setAppContext method is very helpful.

A snippet of ReportRunner.java, which is part of the engine API and shows how to execute a report using the API, is shown below.


HTMLRenderContext renderContext = new HTMLRenderContext();

renderContext.setImageDirectory("image"); //$NON-NLS-1$



HashMap contextMap = new HashMap();

contextMap.put( HTMLRenderContext.CONTEXT_NAME, renderContext );



//Get a connection from the pool
testConn = setupJdbcConnection();
contextMap.put("org.eclipse.birt.report.data.oda.subjdbc.SubOdaJdbcDriver", testConn);

task.setContext( contextMap );


I have added two lines to this code, which are bolded. The first retrieves a java.sql.Connection object. In my example I created this connection directly. In other applications this would be returned from a connection pool call. The next line adds this Connection object to the context map. As the key for this map entry I am using the name of the intended recipient plug-in.

Similar lines of code would be added to an application that is using the engine APIs to call BIRT. Also note that task.setContext will change to task.setAppContext( Map) in BIRT 2.0 M3 and later.

Now my connection is within BIRT. The next step is to configure BIRT to use it. By default the setAppContext feature was created to allow application contextual information to be passed to the data layer. This normally would require an ODA to be built to use it. However there are other ways to get at and use this context. One way is to extend an existing ODA. The ideal choice is the JDBC ODA.

The JDBC ODA uses the OdaJdbcDriver class as the entry point to the ODA. So you may want to create a new Plug-in that extends this class.


public class SubOdaJdbcDriver extends OdaJdbcDriver

private java.sql.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();
}
}
.
.
.

The only methods we are concerned about are the setAppContext and getConnection methods.

As you can see the setAppContext method just retrieves the Connection set earlier in the caller code and stores it.

The getConnection method checks to see if the passedInConnection is not null. If it is null we want the BIRT framework to make the connection using the existing JDBC Connection class. This allows the report designer to continue to use standard BIRT connections at design time and while in run time it retrieves the connection from the calling applicaton.

The appContextDBConnection class is just an extended version of the JDBC ODA Connection class. This can be added as an inner class to the SubOdaJdbcDriver class.

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


In this class the only thing we are really interested in is overriding the open and close methods.


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

}

public void close( ) throws OdaException
{
if ( jdbcConn == null )
{
return;
}
jdbcConn.close();
jdbcConn = null;
}


The jdbcConn variable holds the java.sql.Connection object that is used to make the queries. So in the open method we just set that variable equal to the one that is passed in. The close method just nulls the connection, although ideally it would return the connection to the pool.

There is only one issue. The variable jdbcConn in the existing JDBC ODA is private. You have two choices, make a local version of jdbcConn and override all the methods that use this variable or change the BIRT source for the JDBC ODA to make jdbcConn protected. Post BIRT M3 this variable will be made protected.

If you choose not to change the BIRT source the following methods will have to be added to the appContextDBConnection class in addition to the open and close methods.

commit
getNaxQueries
getMetaData
isOpen
newQuery
rollback

The code for these methods should be exact copies of the methods in org.eclipse.birt.report.data.oda.jdbc.Connection class.

Remember to add the local copy of jdbcConn to the appContextDBConnection class.


private java.sql.Connection jdbcConn = null;


For this new plug-in copy the plugin.xml from the JDBC plugin and modify the driverClass line to point to the new SubOdaJdbcDriver class.



<datasource id="org.eclipse.birt.report.data.oda.subjdbc" odaversion="3.0" driverclass="org.eclipse.birt.report.data.oda.subjdbc.SubOdaJdbcDriver" defaultdisplayname="Example Context JDBC Data Source" setthreadcontextclassloader="false">



Also change the name, change the library tag and add the JDBC plug-in to the requires tag.


<plugin class="org.eclipse.birt.report.data.oda.subjdbc.plugin.SubjdbcPlugin" id="org.eclipse.birt.report.data.oda.subjdbc" name="Subjdbc Plug-in" version="1.0.0">

<runtime>
<library name="subjdbc.jar">
<export name="*">
</library>
</runtime>

<requires>
.
.
<import plugin="org.eclipse.birt.report.data.oda.jdbc"/>
</requires>




You will need to create a plug-in for the ui as well. This plug-in requires no code, just the plugin.xml

Copy the plugin.xml for the JDBC ui plug-in and change the odaDataSourceUI tag to point to the subjdbc plug-in.


<extension point="org.eclipse.birt.report.designer.ui.odadatasource">
<odadatasourceui id="org.eclipse.birt.report.data.oda.subjdbc">


Change the name of this plug-in as well. This plug-in exports no jar file and only need the JDBC ui plug-in to work.


<plugin class="" id="org.eclipse.birt.report.data.oda.subjdbc.ui" name="Ui Plug-in" version="1.0.0">

<requires>
<import plugin="org.eclipse.birt.report.data.oda.jdbc.ui"/>
</requires>


Export the two plug-ins and add them to your designer. Finally, add the subjdbc plug-in to the viewer plug-in and you are ready to develop reports that leverage supplied connections.

29 comments:

AlBlue said...

I can't believe that you actually showed some code with the most pointless ever constructor in Java:

new String("org.eclipse.birt...")

Strings are immutable, so the only difference between that and just using

"org.eclipse.birt..."

is that it takes marginally longer to calculate and uses marginally more space in memory with no additional benefits.

Probably not the kind of thing you want advertised on your blog ;-)

Jason Weathersby said...

alblue,

Thanks, I will fix and republish.

Jason

Anonymous said...

It took me quite a while to get it right. The difficulties lie in having to understand the Eclipse plugin architecture as well as the ODA architecture.

May I suggest that this capability be merged into the JDBC datasource? This extension is completely transparent, but makes perfect sense for those who use a persistent framework like hibernate.

Thanks for the great progress in BIRT.

Best regards,
Yee

Anonymous said...

I cannot believe how HARD it was to discover how to do this. The documentation describing this feature is so obtuse that it makes no explanation in *how* to use it. I also am astounded that this is NOT included with the standard JDBC driver. Why should I have to write code like this when it could be so easily provided?

Anonymous said...

OK. I try to use this, but how BIRT know to use this plug-in instead the org.eclipse.birt.report.data.oda.jdbc plug-in ? I download the ConnectionPoolExample.zip from eclipse.birt news by news that have title "ODA/JDBC extension"
Thanks

Anonymous said...

Please add this function to BIRT !!!

Its odd to have to deal with the plugin mechanism and setup a specific deployment for it just to pass in a connection.

Thanks!
Mario

Jason Weathersby said...

Stefano,

The title is wrong. When you deploy it it will show a new connection type in the data source wizard.

Jason

Anonymous said...

Article is really helpful. Is it possible to mail me the actual code required to do this connection pool work?
My emailid: gvmanjunatha@vsnl.net

Anonymous said...

Hi Jason,

Your article is really what I was looking for, but I think that for
anyone who has never developped a plug-in for eclipse, many questions are not answered at all.

For example "Copy the plugin.xml for the JDBC ui plug-in..." doesn't tell me where to copy... (the stand-alone plugin.xml)
As well as "Exporting the plug-ins and adding them to the designer" isn't really helpful.

I would appreciate a more detailed information.

Thanks a lot
Axel

Jason Weathersby said...

Axel,

Good point. By the way there is now a better way to do this in the newer BIRT version. I will try to do a more detailed example on the BIRT wiki.

Jason

Anonymous said...

Hello Jason,

Anyway I'm trying to create a "passedInConnection".
My Plugin is available in the report view, but at runtime, I get following error:

WARNUNG: Failed to create new instance of JDBC driver:paramconnect.axl.plugin.SubOdaJdbcDriver
java.lang.ClassCastException: paramconnect.axl.plugin.SubOdaJdbcDriver
at org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager.loadAndRegisterDriver(JDBCDriverManager.java:620)
...

At debug mode I see that all drivers are found in the library.
Still I don't know what CAST is not performed.

Any suggestions ?

Yours Axel

Anonymous said...

Hi Jason,


Thanks for this code ... But likes other, I have problems when I must modify the plugin.xml. I use birt 2.1.1 and I think the plugin.xml has changed significaly. is it possible to adapt the pages you write ?

Jason Weathersby said...

Fred,

You are correct there has been a change. If you send me an email at jasonweathersby at alltel.net I will send you a new plugin that functions like the old one. I have posted it to the news group as well.

Jason

Anonymous said...

Hey,
I am trying to extend BIRT by adding a new data source. But while creating data set for that, I am not being able to find how do I add a my custom page in the existing Data set wizard page..
I have designed an SWT page that I want to be showed up after I select my data source in new data set wizard and click next. For that I have added dataSetPage elements to "org.eclipse.datatools.connectivity.oda.design.ui.dataSource"...and provided my class name in its "wizard page class" attribute.
Where else do I need to make changes so that my page comes up when I click NEXT?
Waiting for help. Thankyou.

Nil Ratan said...

Jason,
I am trying to run BIRT using Php-Java bridge, which works ok, but I have a problem with XML datasource.

On the first run the report is created sucessfully, while on the subsequent run it can not open the connection to that XML file... till I restart the Apache Webservr.

I doubt it keep some kind of closed connection in the memory, or cache...

Any guidance will be a great help for me...

Thanks in advance
Nil Ratan

Jason Weathersby said...

What version of BIRT are you using?
Verify that the task that is running the report gets closed. eg task.close()

Jason

Anonymous said...

I have the same error as Nil Ratan using PHP JAVA Bridge. No subsequnet runs are possible. I connect to Oracle database and do task.close(). Any ideas?

Jason Weathersby said...

Did you take a look at this post?
http://birtworld.blogspot.com/2007/01/birt-connection-pooling-continued.html
I added this post because of changes to the DTP project.

Jason

Anonymous said...

Hello Jason, i found the solution for PHP/Java Bridge here http://www.eclipse.org/newsportal/article.php?id=19480&group=eclipse.birt#19480.

Sunrise said...

Could anybody send me the ConnectionPoolExample.zip at susantaghosh at hotmail,com ?

Thanks,
susanta

Jason Weathersby said...

I sent you an email with the source.

Jason

shaik said...

Hi

1) If I get connection in report from a connection pool by using jndi then to get connection its taking only 0.5 seconds but to fetch records(to execute dataset) from database its taking 22 seconds(to fetch 260 records i.e ~ 85ms per record) so total time to run this report its taking 24 seconds

2) if i give all connection parameters while creating DataSource in report (i.e if i wont use jndi connection pool to connect) then to execute same report its taking only 6 seconds (in this 5 seconds to create connection and only 0.4 seconds to fetch 260 records)

Can anybody plz help me to figure out this why there is much time difference with jnid connection and without jndi connetion

Thanks & Regards
Shaik

Anonymous said...

why not provide an interface to accept Connection in the comming version of BIRT?
I think it's quite useful and used freequently.

Jason Weathersby said...

The JDBC driver currently supports doing this with JNDI.

Jason

Unknown said...

Jason,

The snippet of code you posted here is an example of what is showing up in the BIRT codebase supporting the jdbc driver.

public void close( ) throws OdaException
{
if ( jdbcConn == null )
{
return;
}
jdbcConn = null;
}

So, why is it okay to ever set a connection to null before having closed it? This leaves the connection open on the database. This kind of "I don't care about the non-JVM resources" coding is in the jdbc code found in the BIRT CVS source for the jdbc hierarchy where the codebase sets resultsets to NULL instead of closing them.

Jason Weathersby said...

Galen,

Good point, but that is why I put this statement in the blog post:

The jdbcConn variable holds the java.sql.Connection object that is used to make the queries. So in the open method we just set that variable equal to the one that is passed in. The close method just nulls the connection, although ideally it would return the connection to the pool.

If the application is passing in the connection, It may be responsible for cleaning up the connection.

Jason

Unknown said...

If the accepting code is to not implement the close() method, then do not implement it. But, to have the "close()" method set a connection object to null is not implementing the "close()" method. After setting the connection = NULL, from the JVM's point of view, everything is just great, but from the database's point of view, the connection is still open and until either BIRT is shutdown or someone manually kills it, the connection will remain open. Its a finite resource and needs to be handle that way.

Jason Weathersby said...

Thanks for your comment.
I updated the post.
You might also want to look at:

http://birtworld.blogspot.com/2007/01/birt-connection-pooling-continued.html


http://birtworld.blogspot.com/2008/11/birt-connection-pooling-continued-again.html

Generic Viagra said...

I was using this supplied connection with a friend and it is incredible we couldn't believe how fast is to use it with that.