nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Calling a stored Procedure

jballnik

Hi Gang,

Does anyone know how to call a stored procedure(ORACLE) using a BIRT report. This stored procedure will NOT be passing anything back or in, it just simply runs several update statements.

Has anyone done this before??

thanks,
John

Find more posts tagged with

Comments

sschafer2

You can do this with plain JDBC in the beforeFactory method of the report.

micajblock

This is not something trivial or out of the box. One has to code to the data engine. The reason why it is not published is that it is not finalized yet. Below is a code example that might work for you. It assumes that you have a data source with the right connection properties named 'Data Source' 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
importPackage(Packages.java.lang);
importPackage(Packages.java.util);
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 dsrc = reportContext.getDesignHandle().findDataSource("Data Source");

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

var odaDataSet = new OdaDataSetDesign( "Test Data Set" );
odaDataSet.setDataSource( odaDataSource.getName( ) );
odaDataSet.setExtensionID( "org.eclipse.birt.report.data.oda.jdbc.SPSelectDataSet" );
odaDataSet.setQueryText( "{call procedure_name(parm1,parm2)}");

de.defineDataSource( odaDataSource );
de.defineDataSet( odaDataSet );

queryDefinition = new QueryDefinition( );
queryDefinition.setDataSetName( odaDataSet.getName() );
queryDefinition.setAutoBinding(true);

var pq = de.prepare( queryDefinition );

var qr = pq.execute( null );
</pre>

jballnik

Thanks Gang...but is there anyone that has done this for Maximo specific BIRT reports??

micajblock

It does not matter if this is Maximo. The code I have can be in the beforeFactory event of ANY BIRT report.

VinceG

<blockquote class='ipsBlockquote' data-author="'mblock'" data-cid="78625" data-time="1308242450" data-date="16 June 2011 - 09:40 AM">
This is not something trivial or out of the box. One has to code to the data engine. The reason why it is not published is that it is not finalized yet. Below is a code example that might work for you. It assumes that you have a data source with the right connection properties named 'Data Source'</blockquote>
 
I was wondering if it would be possible to do something similar but inside the AfterOpen event handler of the Datasource, where it would reuse the connection made by the datasource already? 
 
The idea is to call a stored proc after the connection is open on the same connection. 
Thanks

micajblock

<blockquote class='ipsBlockquote' data-author="'VinceG'" data-cid="81194" data-time="1312569721" data-date="05 August 2011 - 11:42 AM">
I was wondering if it would be possible to do something similar but inside the AfterOpen event handler of the Datasource, where it would reuse the connection made by the datasource already? 
 
The idea is to call a stored proc after the connection is open on the same connection. 
Thanks </blockquote>
 
I do not believe the actual connection is exposed to scripting. 
 
One thing you could look at is implementing a driver bridge extension point to keep the connection. The bridge extension point is described here: 
<a class='bbc_url' href='http://birtworld.blogspot.com/2007/01/birt-connection-pooling-continued.html'>http://birtworld.blogspot.com/2007/01/birt-connection-pooling-continued.html</a>