How to share same database connection within a report

Hi,

We need to run "alter session ..." statement for each report to set NLS_COMP and NLS_SORT for Oracle database. It seems birt report is opening a new connection for each dataset in the report - even though they all use the same data source. We currently don't use the connection pool, but I guess it will be the same issue even then. Few questions,

1) Is there a way for a report to use the same connection for all its datasets?

2) Is there a way to for multiple table objects to share the resultset from the same dataset, i.e, if two tables are using the same dataset I don't want the sql to be run twice - just run once and use the same resultset?

3) How to alter session for the whole report? I was thinking to add a hidden table at the top of the report and alter session in its dataset? Would that work? Is it possible to do this at Data Source level (inside afteropen method) as a script?

If this is not the right forum for these questions, please redirect me to the right place.

Thanks,
-vivek

Find more posts tagged with

Comments

Virgil Dodson

Hi Vivek, since these questions look like things you would modify inside the BIRT Designer, I am moving this thread to the Designing BIRT Reports forum.

For your questions,

1) Yes, you can have multiple reports share a data connection by setting up a connection pool and then using the JNDI name property of the Data Source if you are in a Java EE container... otherwise, you can always use the Scripted Data Source in BIRT if you have Java classes to control your connection already and these Java classes can supply your data.

2) Yes, newer versions of BIRT contain logic to keep it from re-running the query if multiple tables use the data set... but you can also force this to happen by only assigning one table to the data set... and then binding the second table to the first. You do this by naming the first table, then on the property binding tab of the second table, you can select table 1 for the data.

3) I don't understand this question, what are you trying to accomplish?

Migrateduser

Thanks Virgil for the response. I'm basically trying to alter Oracle session. For ex., I want to run these two queries right after I get the db connection and just before a sql in the data set is run,

"alter session set NLS_COMP=ANSI;"
"alter session set NLS_SORT=BINARY_CI;"

Ideally, I would like to set this in the "afteropen" method of the DataSource, but I don't see a way to do that in Birt right now. Currently, it looks like every data set acquires its own connection (whether I use connection pool or not). So, my question was,

1) Is there a way for the whole report to use the same connection? Even if I use connection pool it will still acquire multiple connections from the pool - one for each data set. Is there a way to control that in Birt so a report takes one connection and releases it only after the whole report has been completed?

2) Is there a way to alter database connection session in Birt? I was suggesting to do this in a hidden table which is at the top of the report, but that requires #1 is fulfilled.

Thanks,
-vivek