How to reuse database connection in BIRT report

Sree_25
Sree_25
edited February 11, 2022 in Analytics #1

Hi All,
I am using Eclipse BIRT designer (4.5v) to develop the report where I need to use both the JDBC Data Source and the Scripted Data Source. I could give the connections details in JDBC data source and establish the database connection successfully. In the Scripted DS open event, I need to use the same database connection to execute the stored procedure. I can use JDBC data source to make the stored procedure call directly but for my requirements, I need to use only the Scripted data source. Any ideas how I can reuse the already established database connection in open event of scripted data source to make the stored procedure call? Any help is appreciated. Thanks.

Best Answer

Answers

  • jfranken
    jfranken E
    #3

    As you probably already know, you can use you stored procedure as the query for a regular data set. Could you please let me know your reason for using a Scripted data set? I am asking because it would be more efficient and potentially much easier if you use a normal data set based on your JDBC data source. Between the stored procedure and the data set's computed columns, much of the data manipulation that can be done in a Scripted data set can be done in a normal data set, and as I mentioned, the performance will likely improve. Sorry about side-stepping your question for a moment.

    Warning No formatter is installed for the format ipb
  • Sree_25
    Sree_25
    #4

    Thank you for the response. The stored procedures that we use normally return multiple result sets (like five or more result sets). I noticed that with built in options to call the stored procedure, I have to add five data sets (if result sets = 5) and provide the corresponding result set number in order to pull the data for all 5 result sets. Due to this, the same stored procedure is getting executed 5 times and causing the performance issues. So we are trying to use scripted data source and call the stored procedure only once and then process the data returned for all five result sets. Thanks.

  • jfranken
    jfranken E
    #5

    I have not had time to look at how to implement the Scripted data set. I suspect having code that parses five result sets row by row will be slower than the five data set method. Do you have timing information on the five data set option? How many total rows and how long does it take to run?

    Warning No formatter is installed for the format ipb
  • Sree_25
    Sree_25
    #6

    It varies from case to case. Some times the processing time for stored procedures would be more and at the end it may return small result set/s as an output. In that case, the performance will be poor if we use the data set option. And also due to limitations on the back end, we need to avoid multiple execution of same stored procedure for single run of the report. Thanks.

  • jfranken
    jfranken E
    #7 Answer ✓

    I found an example that shows how to connect to a JDBC data source from a scripted data set. This example is old and I have not had time to test it. If I find a more recent example I will post it.

    https://www.eclipse.org/forums/index.php/t/73967/

    Warning No formatter is installed for the format ipb
  • Sree_25
    Sree_25
    #8

    Hi Jeff,

    I tried the approach given in the post you provided. It worked out fine but encountering the JDBC driver issues on server inconsistently. Not sure if these errors are due to explicitly the loading the driver class in the design. Trying to find out a way where we can refer the database connection that is already established in the data source element instead of loading the driver class and passing the user id and password details for second time. Please post if you find any other way of achieving this. Thank you.

Categories

image
OpenText CE Products
Magellan Text Mining
Docs & APIs