Executing arbitrary SQL in event scripts

Before switching to BIRT, we were using Oracle Reports. A very nice feature of that product is the ability to execute SQL and PL/SQL inside triggers (=events in BIRT).

I developed a plugin to enable this in BIRT as well.

While this is working very well (including bind variables), it has two major drawbacks:

* It uses a dirty hack (Reflection API) to access the actual JDBC Connection of a ODA JDBC Data Source.
* It only works if the JDBC Data Source is already open, e.g. if one of the Data Sets using the Data Source has already been accessed from the layout. This means I can not use SQL in the beforeFactory event.

So what I'd like to know:
Is it possible to to test if an ODA JDBC Data Source is already open (connected to the DB) from a script and to open it in case it's not?

I would like to donate the code to the community if someone would eliminate the two problems given above and include it into the Open Source Eclipse BIRT release.

Find more posts tagged with

Comments

mwilliams

You could probably set a global variable in your dataSource script and check that global variable's value in your other script areas to check whether a connection is made, yet. As for enhancement requests, you can file those in the bugzilla at www.eclipse.org/birt. If you include the bugzilla request link, in here, others can view and vote for your request.

hvb

To answer my own question: 
The following code (untested) should work to open the Data Source. Use it in the beforeFactory event of the report: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
importPackage( Packages.org.eclipse.birt.report.model.api );
importPackage(Packages.org.eclipse.birt.data.engine.api);
importPackage(Packages.org.eclipse.birt.report.model.api);
importPackage(Packages.org.eclipse.birt.data.engine.api.querydefn);
importPackage(Packages.org.eclipse.birt.data.engine.core);

var myconfig = reportContext.getReportRunnable().getReportEngine().getConfig();
var de = DataEngine.newDataEngine( myconfig, null );
var odaDataSet = new OdaDataSetDesign( "Test Data Set" );
// Use the name of the Data Source you want to open here:
odaDataSet.setDataSource( "MyJdbcDataSource");
odaDataSet.setExtensionID( "org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet" );
odaDataSet.setQueryText( "select 'X' as DUMMY from DUAL");
de.defineDataSet( odaDataSet );
var queryDefinition = new QueryDefinition( );
queryDefinition.setDataSetName( odaDataSet.getName() );
queryDefinition.setAutoBinding(true);
var pq = de.prepare( queryDefinition );
var qr = pq.execute( null );
</pre>

mwilliams

Thanks for the update!

hvb

Unfortunately, the solution I proposed doesn't work. 
 
The problem is that I cannot access the current Data Source (or Data Engine) from script. 
de.defineDataSet(odaDataSet) fails with an error 
"Data source is not defined: MyJdbcDataSource" 
 
But the Data Source is definitely defined in the report. 
 
Seems as if it is only defined inside the current Data Engine instance, which I cannot access. Note that all the examples in the internet 
* only use newDataEngine - which creates a new instance, as the name says. 
* always create a new Data Source, copying the JDBC connection properties from the existing one.

shree55

need to add this

var odaDataSource = new OdaDataSourceDesign( "Test Data Source" );
odaDataSource.setExtensionID( "org.eclipse.birt.report.data.oda.jdbc" );
odaDataSource.addPublicProperty( "odaURL", dsrc.getProperty("odaURL").toString() );
odaDataSource.addPublicProperty( "odaDriverClass", dsrc.getProperty("odaDriverClass").toString());
odaDataSource.addPublicProperty( "odaUser", dsrc.getProperty("odaUser").toString() );
odaDataSource.addPublicProperty( "odaPassword", "/db password/" );