Tuesday, June 23, 2009

How to pass a ResultSet to BIRT report


I tried to pass a JDBC ResultSet (java.sql.ResultSet) and could not find a straight forward way like passing the ResultSet as an argument to the report during generationd. However this is what I finally arrived at. This solution could be used to pass a collection of objects from a java class to the BIRT report.


Steps:-

  1. In the java application where we are planning to generate the report using BIRT engine, execute the sql query and fetch the resultset. Add the resultset as a parameter to the report.

      ResultSet rs = getResultSet() // a function that executes the query and returns resultset

      HashMap params = new HashMap();

      params.put(myResultSet”, rs);

  2. In the report, create a scripted data source. Create a scripted data set with the column names that you wish to use in the report. For e.g lets create a data set with one column “EMP_NAME”.

  3. This step is to glue the columns in the resultset that came as parameter and the columns in the scripted data set. This can be achieved in two ways.

      a) Using javascript function

        1. In the scripted data set, implement the open(), fetch() methods. I'll explain in this another post.

      b) Using a java event handler that extends ScriptedDataSetEventAdapter class (org.eclipse.birt.report.engine.api.script.eventadapter.ScriptedDataSetEventAdapter) and implement the fetch(), open() and beforeOpen() methods. Lets handle one by one.

    1. Create a class called TestHandler (test.TestHandler)
    2. Declare a member variable for the ResultSet

        ResultSet resultSet;

    3. Retrieve the parameter myResultSet set from the java class in Step 1 and store it in the member variable.

      beforeOpen(IDataSetInstance dataSet, IReportContext reportContext) {

        super.beforeOpen(dataSet, reportContext);

        //retrieve the parameter myResultSet set from the java class in Step 1

        resultSet = (ResultSet)reportContext.getParameterValue(“myResultSet”);

        }

    4. Position to resultset to the first row in the open()

      public void open(IDataSetInstance dataSet) {

        super.open(dataSet);

        if(resultSet != null)

          resultSet.next();

      }

    5. Map the column of the resultset and the column of the scripted data set.

      public boolean fetch(IDataSetInstance datSet, IUPdatableDataSetRow row) {

        if(resultSet.isAfterLast())

          return false;// to indicate that there are no more rows left to process

        row.setColumnValue(“EMP_NAME”, resultSet.getString(1));

        resultSet.next();

        return true; // to indicate that more rows to follow

      }

  4. Do the cleanup activities. Close the resultset, statement and release the connection.

  5. Go to the report designer. Go to Outline window. Select the scripted data set created in Step 2. Go to the Property Editor and select "Event Handler" tab. In the Event Handler Class, add the fully qualified class name created in step 3 (test.TestHandler).
  6. All set. Run the java program to initiate the engine and invoke the report and see the results.


9 comments:

  1. Hii ,
    The above post was very useful ..
    Can you please tell how to generate a birt report Using javascript function ..

    Thanks in advance,
    Rahul

    ReplyDelete
  2. Good work!
    Thanks for this article!

    ReplyDelete
  3. This is somethng im searching for the last 2 weeks..!!!
    THANKS A TONNNNN !!!....
    But din understnd wer to create the ScriptedDataSetEventAdapter class u wer mentioning..pls help!!! I m really geting mad working on birt !!

    ReplyDelete
  4. Hello,
    In above example,column name EMP_NAME is fixed.But how to fetch resulset in case of unpredictable columns?Can anyone help me?

    ReplyDelete
  5. sorry guys for being so late in replying.. guess it would help you at some point of time in future!

    ReplyDelete
  6. hey @r@vish@nku - The class needs to be in the classpath; of your java program or web application. If you are getting error from the birt report designer IDE, the class could be kept in the classpath of the IDE. Sorry for the late reply.

    ReplyDelete
  7. @Anonymous - This would need to be done in the mapper. It would take care of displaying different columns in the same place. If the columns are completely unpredictable, I'm not sure whether birt would support accessing columns dynamically using indices.

    ReplyDelete
  8. Hi,

    I got the following error when i tried access the result value.

    java.sql.SQLException: Operation not allowed after ResultSet closed

    Thanks & Regards

    ReplyDelete
  9. I need to know how to glue the columns in the resultset that came as parameter and the columns in the scripted data set by using javascript function. You wrote: "In the scripted data set, implement the open(), fetch() methods. I'll explain in this another post". Could you please to post this information, or send it to my e-mail 24031987a@mail.ru. Thank you.

    ReplyDelete