Execute a query or a stored procedure in each row

Hello,

Is it possible to execute a stored procedure in each row by using BIRT javascript. For example, I have a table that contains a list of persons. In this table, there is a column called pageNumber, which indicates the page number of each person that appears in the generated report. By default, this page number is null. After the report is created, I want to write back the page number value for each person to the database.

I cannot do this "write back" before the report is created because I don't know the each person's page number.

How do I do this?

Thank you.

Larry

Find more posts tagged with

Comments

bhanley

Sounds like you are trying to generate a Table of Contents? If this is so, BIRT can do this for you (depending on your output type) by default. New groups and sections will automatically get added to a Table of Contents. Generate your report in PDF inside the designer to see it work.

Larrykash

No, I'm not making a table of content or making groups.
Assume I have a table that looks like this:
CREATE TABLE persons
{
pid int
name string
}

After the person data are displayed in a PDF report, I want to update another table in the db like:
UPDATE personPageNumber
SET PageNumber = @pageNum where pid = @pid

This query is in a stored procedure, but how do I execute it dynamically in the report? The page number is unknown before the report is created.

bhanley

I understand what you are looking for now. BIRT does not expose a straightforward ability to do a write-back to the database. You would need to develop a Java-based event handler to process the data and fire off the stored procedure. Since you do not require any actual user input this should not be too difficult a task.

Larrykash

Thanks for replying. I have a further question. How do I use the Java event handler with my BIRT report? I read the instruction on this page, but it's not clear. 
<a class='bbc_url' href='http://www.eclipse.org/birt/phoenix/deploy/reportScripting.php'>Report Scripting</a> 
 
Where do I put the class file or jar file? so my report can find it? 
By the way, I'm using Actuate10 BIRT Report Designer Professional. 
Thank you.

bhanley

If you are using an iServer to access your reports, you place the JAR in the Volume's resources folder. If you are rendering your report without an iServer via a standard application server, just put your JARs somewhere that will get loaded on Start up (like the WEB-INFlib directory). Lastly, if you create your event handlers inside the same workspace as your BIRT reports, you will be able to invoke them while testing out your development.

After you create your Java Object, do not forget to bind it to the report design via the "Event Handler" property found on the Properties editor for the component you are extending (a Table in your case I believe).

Larrykash

It works! Thanks. I found if I'm using the Java event handler, I have to make the the item's Javascript area empty.