Eclipse Community Forums: BIRT » execute DML in events, execution order of DataSets

Home » Archived » BIRT » execute DML in events, execution order of DataSets(I want to execute SQL DML statements, call stored procedures, etc in report event handlers.)

execute DML in events, execution order of DataSets [message #672185]

Fri, 20 May 2011 04:53

Eclipse User

I have a report where part of the data has to be prepared inside the Oracle database using a stored procedures (for various reasons).

So I need to call pkg.init(...) at the beginning of the report execution and pkg.cleanup() at the end of the execution (where pkg is a PL/SQL package = a program executed inside the DB).

The SQL queries in in the report's main DataSets rely on the fact that pkg.init() has been called before.

I managed to do this using two JDBC DataSets "dsinit" and "dscleanup", where "dsinit" looks like this: "{call pkg.init(?)}" and "dscleanup" looks like this: "{call pkg.cleanup()}"; and using hidden lists for these DataSets at the top and the bottom of the layout.

However, after I moved the lists from the .rptdesign to a .rptlibrary and let the report reference them from there, this does not work any more. By tracing the afterOpen events, I found that the "dsinit" opened too late; the main DataSets where executed earlier (resulting in missing output data).

How can I force the execution order of the DataSets to be like I want it?

The need to create DataSets and List or Tables in the layout just to execute an SQL statements seems strange for a report generator tool like BIRT.

Is there a recommended way to execute simple DML statements or stored procedure calls in BIRT event handlers?

If yes, I could use the beforeFactory/afterFactory events of the report to call my init/cleanup code.

If no - why not?

I think about creating a JDBC Connection Wrapper Java class that expects an additional "connectionID" property and then stores the connection (once opened) in an internal Map (String connectionID => Connection) before it returns it to the caller.
A related Java class could then offer methods like
static void executeDML(String connectionID, String dmlStatement);
static void commit(String connectionID);
static void rollback(String connectionID);

Does that sound reasonable?

Henning

Re: execute DML in events, execution order of DataSets [message #672279 is a reply to message #672185]

Fri, 20 May 2011 10:00

Eclipse User

BIRT only calls the datasets if they are used to prevent wasted calls.
We are looking at supporting multiple calls within one dataset that
should help in this case. You could always call out to jdbc from a
script but I am not certain that will be any more user friendly than
adding a table/list to the top or bottom of the report. If you want to
guarantee the dsinit if fired first you could bind a grid to the dsinit
dataset and then put the grid in the library and then nest your other
tables/list in it.

Jason

On 5/20/2011 4:53 AM, forums-noreply@eclipse.org wrote:
> I have a report where part of the data has to be prepared inside the
> Oracle database using a stored procedures (for various reasons).
>
> So I need to call pkg.init(...) at the beginning of the report execution
> and pkg.cleanup() at the end of the execution (where pkg is a PL/SQL
> package = a program executed inside the DB).
>
> The SQL queries in in the report's main DataSets rely on the fact that
> pkg.init() has been called before.
>
> I managed to do this using two JDBC DataSets "dsinit" and "dscleanup",
> where "dsinit" looks like this: "{call pkg.init(?)}" and "dscleanup"
> looks like this: "{call pkg.cleanup()}"; and using hidden lists for
> these DataSets at the top and the bottom of the layout.
>
> However, after I moved the lists from the .rptdesign to a .rptlibrary
> and let the report reference them from there, this does not work any
> more. By tracing the afterOpen events, I found that the "dsinit" opened
> too late; the main DataSets where executed earlier (resulting in missing
> output data).
>
> How can I force the execution order of the DataSets to be like I want it?
>
> The need to create DataSets and List or Tables in the layout just to
> execute an SQL statements seems strange for a report generator tool like
> BIRT.
>
> Is there a recommended way to execute simple DML statements or stored
> procedure calls in BIRT event handlers?
>
> If yes, I could use the beforeFactory/afterFactory events of the report
> to call my init/cleanup code.
>
> If no - why not?
>
> I think about creating a JDBC Connection Wrapper Java class that expects
> an additional "connectionID" property and then stores the connection
> (once opened) in an internal Map (String connectionID => Connection)
> before it returns it to the caller.
> A related Java class could then offer methods like
> static void executeDML(String connectionID, String dmlStatement);
> static void commit(String connectionID);
> static void rollback(String connectionID);
>
> Does that sound reasonable?
>
> Henning

Re: execute DML in events, execution order of DataSets [message #672306 is a reply to message #672279]

Fri, 20 May 2011 11:59

Eclipse User

Thanks for the feedback, I'll try it that way next week.

Today I wrote code for the idea I mentioned.
I used http //birtworld.blogspot.com/2007/01/birt-connection-pooling-continued.html as a template.
Instead of injecting the Connection from outside, my code stores the connection in a static Map (using the OdaPropertyConfigId as a key).

Unfortunately, I'm struggling with class loader issues here:

After open() exits, the map contains a connection (logged from inside MyJdbcDriver).

But when I try to get the connection from Javascript, the map is empty (again logged from within the class).
I guess (though it is declared static), Javascript uses a different ClassLoader than the plugin...

Can I somehow access the (JS) reportContext from within the plugin instead? That would help.

Another oddity: it seems like the plugin is only used once I used the preview (not if I use run/view as pdf). Why is that?

Re: execute DML in events, execution order of DataSets [message #672371 is a reply to message #672306]

Fri, 20 May 2011 16:28

Eclipse User

Where are you creating the connection? Are you using the viewer or RE
API? If the connection is in the app context you should be able to get
it using reportContext.getAppContext().get("Whatevervariablenameugaveit");

anywhere in js (script or expression).

Jason

On 5/20/2011 11:59 AM, forums-noreply@eclipse.org wrote:
> Thanks for the feedback, I'll try it that way next week.
>
> Today I wrote code for the idea I mentioned.
> I used http
> //birtworld.blogspot.com/2007/01/birt-connection-pooling-continued.html
> as a template.
> Instead of injecting the Connection from outside, my code stores the
> connection in a static Map (using the OdaPropertyConfigId as a key).
>
> Unfortunately, I'm struggling with class loader issues here:
>
> After open() exits, the map contains a connection (logged from inside
> MyJdbcDriver).
>
> But when I try to get the connection from Javascript, the map is empty
> (again logged from within the class).
> I guess (though it is declared static), Javascript uses a different
> ClassLoader than the plugin...
>
> Can I somehow access the (JS) reportContext from within the plugin
> instead? That would help.
>
> Another oddity: it seems like the plugin is only used once I used the
> preview (not if I use run/view as pdf). Why is that?

Re: execute DML in events, execution order of DataSets [message #673336 is a reply to message #672371]

Mon, 23 May 2011 05:44

Eclipse User

You wrote:
> Where are you creating the connection? Are you using the viewer or RE
> API? If the connection is in the app context you should be able to get
> it using reportContext.getAppContext().get("Whatevervariablenameugaveit");

Yes, I know. That's why I asked if it's possible to access the reportContext
from within the plugin.

Here's my code (slightly shortened, for full version see attached Eclipse project):

public class MyJdbcDriver extends OdaJdbcDriver {
	
  private final String contextKey = "lisa.myJDBC";
  private Logger log = Logger.getLogger(this.getClass().getName());

  public IConnection getConnection(String connectionClassName)
  throws OdaException {
    return new MyJdbcConnection();
  }
	
  private HashMap<String, Connection>getConnContext() {
    return ConnectionMap.theMap;
  }

  private class MyJdbcConnection 
  extends org.eclipse.birt.report.data.oda.jdbc.Connection {
    private String name = null;

    public void open(Properties connProperties) throws OdaException {
      name = connProperties.getProperty("OdaPropertyConfigId");
      super.open(connProperties);
      getConnContext().put(name, super.jdbcConn);
    }

    public void close() throws OdaException {
      super.close();
      getConnContext().remove(name);
    }

  }
}

and ConnectionMap is just

public class ConnectionMap extends HashMap<String, Connection> {
  public static ConnectionMap theMap = new ConnectionMap();
}

Then I have a class Sql which uses ConnectionMap.theMap to access the open connections and execute SQL (straightforward).

My first attempt was to use the context supplied in setAppContext() for storing the connections, but that didn't work either.

Attachment: testjdbc.zip
(Size: 9.78KB, Downloaded 350 times)

Re: execute DML in events, execution order of DataSets [message #673443 is a reply to message #673336]

Mon, 23 May 2011 12:36

Eclipse User

You could always try putting it in the app context

public void setAppContext( Object context ) throws OdaException
{
HashMap ctx = (HashMap)context;
ctx.put("myobj", myobj);

}

Then in script try reportContext.getAppContext().get("myobj");

I still think this may cause classpath issues. You may need to add your plugin to the manifest list for the report engine plugin.

Jason

Re: execute DML in events, execution order of DataSets [message #673615 is a reply to message #673443]

Tue, 24 May 2011 05:03

Eclipse User

You are right.

I put the ConnectionMap into the AppContext (had to supply the AppContext as an additional constructor argument in the Sql class), and now when constructing an Sql object from BIRT script, it finds the ConnectionMap, but fails here:

		log.info("getConnectionMap aufgerufen mit Kontext " + System.identityHashCode(ctx));
		final String connMapKey = "myjdbcConnectionMap";
		ConnectionMap connMap = (ConnectionMap) ctx.get(connMapKey);

with a ClassCastException:

WARNUNG: A BIRT exception occurred: Error evaluating Javascript expression. Script engine error: Wrapped java.lang.ClassCastException: org.eclipse.birt.report.data.testjdbc.ConnectionMap cannot be cast to org.eclipse.birt.report.data.testjdbc.ConnectionMap (#15)

Is it possible to work around this using reflection?

Now the S

Re: execute DML in events, execution order of DataSets [message #673640 is a reply to message #673615]

Tue, 24 May 2011 06:06

Eclipse User

To answer my own question: Yes, it is possible using reflection.

Now, to give a status summary:
I am able to execute SQL statements in BIRT event scripts like this:

var sql = new org.eclipse.birt.report.data.testjdbc.Sql(reportContext.getAppContext(), "org.eclipse.birt.report.data.oda.jdbc_lisa");
log.info("Versuche SQL auszuführen!");
var d = sql.executeQuery("select SYSDATE from DUAL");
log.info("d=" + d);

resulting in this output (when I use the Preview tab):

24.05.2011 11:39:09 org.eclipse.birt.report.data.testjdbc.Sql executeQuery
INFO: executeQuery org.eclipse.birt.report.data.oda.jdbc_lisa: select SYSDATE from DUAL
24.05.2011 11:39:09 org.eclipse.birt.report.data.testjdbc.Sql getConnection
INFO: getConnection aufgerufen mit Kontext 18699674
24.05.2011 11:39:09 org.eclipse.birt.report.data.testjdbc.Sql getConnection
INFO: myjdbcConnectionMap im appContext gefunden!
24.05.2011 11:39:09 org.eclipse.birt.report.data.testjdbc.Sql getConnection
INFO: Method:public java.lang.Object java.util.HashMap.get(java.lang.Object)
24.05.2011 11:39:09 org.eclipse.birt.report.data.testjdbc.Sql getConnection
INFO: Connection: oracle.jdbc.driver.T4CConnection@1724bd0
24.05.2011 11:39:09 sun.reflect.GeneratedMethodAccessor8 invoke
INFO: d=[[2011-05-24 11:39:09.0]]

I still have to find out if and how this is going to work with the "Run/ View report as PDF" menu and from within the application.

I'll keep you informed.

Re: execute DML in events, execution order of DataSets [message #674046 is a reply to message #673640]

Wed, 25 May 2011 12:30

Eclipse User

I have it all up and running now.

I can execute SQL using a connection from a datasource per BIRT Javascript in DataSet events or layout item events, including bind variables, DML, querying etc.

This now allows me to call the pkg.init(?) / pkg.cleanup() as the first/last SQL command in the session without having to use DataSets and List items for these statements. Smile

However, I cannot execute SQL in the ReportDesign events beforeFactory/afterFactory, simply because at that time there is no DB connection open Wink

Re: execute DML in events, execution order of DataSets [message #710119 is a reply to message #674046]

Thu, 04 August 2011 14:19

Eclipse User

Hi,
I am trying to do something similar by calling a stored procedure inside AfterOpen trigger of the DataSource.
I tried your last sample and it gives me the following error:
[JavaPackage org.eclipse.birt.report.data.testjdbc.Sql] is not a function, it is object

Do you mind explaining how did you achieve this?

Thanks

Re: execute DML in events, execution order of DataSets [message #710142 is a reply to message #710119]

Thu, 04 August 2011 14:49

Eclipse User

Can you post the script? You may want to use importPackage like

importPackage(Packages.org.eclipse.birt.report.data.testjdbc);
var sqlobj = new Sql();
sqlobj.someMethod();

Jason

On 8/4/2011 2:19 PM, VinceG wrote:
> Hi,
> I am trying to do something similar by calling a stored procedure inside
> AfterOpen trigger of the DataSource.
> I tried your last sample and it gives me the following error:
> [JavaPackage org.eclipse.birt.report.data.testjdbc.Sql] is not a
> function, it is object
>
> Do you mind explaining how did you achieve this?
>
> Thanks

Re: execute DML in events, execution order of DataSets [message #710154 is a reply to message #710142]

Thu, 04 August 2011 15:01

Eclipse User

Thank you for your help.

That's all I have in the afterOpen script and similar issue:
Script engine error: ReferenceError: "Sql" is not defined.

Do you know where can I find any API regarding that testJDBC class?

Thanks

Re: execute DML in events, execution order of DataSets [message #710188 is a reply to message #710154]

Thu, 04 August 2011 15:45

Eclipse User

That class was an example class created by the original poster.

Jason

On 8/4/2011 3:01 PM, VinceG wrote:
> Thank you for your help.
>
> That's all I have in the afterOpen script and similar issue:
> Script engine error: ReferenceError: "Sql" is not defined.
>
> Do you know where can I find any API regarding that testJDBC class?
>
> Thanks
>
>

Re: execute DML in events, execution order of DataSets [message #710197 is a reply to message #710188]

Thu, 04 August 2011 15:46

Eclipse User

If you have a Java Class that calls your stored procedure you can call
it from BIRT script.

Jason

On 8/4/2011 3:45 PM, Jason Weathersby wrote:
> That class was an example class created by the original poster.
>
> Jason
>
> On 8/4/2011 3:01 PM, VinceG wrote:
>> Thank you for your help.
>>
>> That's all I have in the afterOpen script and similar issue:
>> Script engine error: ReferenceError: "Sql" is not defined.
>>
>> Do you know where can I find any API regarding that testJDBC class?
>>
>> Thanks
>>
>>
>

Re: execute DML in events, execution order of DataSets [message #710229 is a reply to message #710197]

Thu, 04 August 2011 16:51

Eclipse User

I started using a Java class as well, for instance a class that will extend DataSourceEventAdapter, but I could not figure out how to get the connection object. If I can get the connection from the IDataSourceInstance then I should be all set to call a stored procedure from there. Any idea?

Sorry, I got where the testJDBC class comes from now, I would give that a try; I just thought BIRT would provide something more trivial.

Re: execute DML in events, execution order of DataSets [message #710259 is a reply to message #710229]

Thu, 04 August 2011 17:16

Eclipse User

You could use the data engine directly from script. Take a look at this
example:
http://www.birt-exchange.org/org/forum/index.php/topic/22593-calling-a-stored-procedure/page__s__9b2d0cde65a6916e94afd096da17236d

Jason

On 8/4/2011 4:51 PM, VinceG wrote:
> I started using a Java class as well, for instance a class that will
> extend DataSourceEventAdapter, but I could not figure out how to get the
> connection object. If I can get the connection from the
> IDataSourceInstance then I should be all set to call a stored procedure
> from there. Any idea?
>
> Sorry, I got where the testJDBC class comes from now, I would give that
> a try; I just thought BIRT would provide something more trivial.

Re: execute DML in events, execution order of DataSets [message #710873 is a reply to message #710259]

Fri, 05 August 2011 10:19

Eclipse User

Update: getting some progress. The link helped a lot.

One issue with the example is that it creates the datasource (connection) because code is in beforeFactory trigger, in my case it would be in afterOpen of the Datasource so that dataset can run on same connection.

I need something like:
dataEngine.defineDataSource ( reportContext.getDesignHandle().findDataSource("MyDATASOURCE")) but that doesn't work.

Re: execute DML in events, execution order of DataSets [message #710919 is a reply to message #710873]

Fri, 05 August 2011 11:19

Eclipse User

I could donate the code for executing SQL to the Eclipse BIRT project but I won't have the time to maintain it.
However, I would much prefer if a JDBC Datasource had an official script API to let me execute SQL (including bind vars) in the current connection.

Henning

Re: execute DML in events, execution order of DataSets [message #713645 is a reply to message #710919]

Mon, 08 August 2011 13:28

Eclipse User

It would be great if you could open an enhancement and post your work
around with a note to add a script option to do this.

Jason

On 8/5/2011 11:19 AM, h.vonbargen wrote:
> I could donate the code for executing SQL to the Eclipse BIRT project
> but I won't have the time to maintain it.
> However, I would much prefer if a JDBC Datasource had an official script
> API to let me execute SQL (including bind vars) in the current connection.
>
> Henning

Previous Topic:	(no subject)
Next Topic:	Urgent : Birt Viewer - To Jason

Goto Forum:

-=] Back to Top [=-

Current Time: Wed Jul 23 08:47:47 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter