2

I have a feeling I'm in a catch-22 situation here, but I'm posting in the hope someone has found a workaround for this at least!

The database for the BIRT reports I work on is being passed in via a hidden parameter called db, so in the data source of my report. So under Property Binding, I tell BIRT to use "jdbc:mysql://localhost/" + params["db"].value as the JDBC Driver URL. This is supposed to use the database name passed in from the URL at runtime in browser, a solution I found online to account for running our reports on computers with different database names.

enter image description here

My issue is that in the same report parameters, the user has to select a company from a list of companies which come from the database. But the report can't populate this list because the database isn't loaded until I press OK on the parameters window, which can't be done until they select a company, and uh-oh I found myself with a catch-22 situation!

enter image description here

I have no idea what I'm suppose to do in this situation, how can I get the report to load the database first, then display the parameters screen based on it?

logicalfox
  • 83
  • 2
  • 13
  • 2
    Since posting this question it appears I will have to take a route similar to that posted here: [link](https://www.eclipse.org/forums/index.php/mv/msg/235486/716568/#msg_716568) use of an external properties file with database info, and utilise beforeOpen to load it. I have created a properties file before, however I am unsure about 2 things: where to save this properties file on our computers, and how to point this code at it? If someone could clarify that for me it would be much appreciated, I will post this as an answer then if it works for future reference. – logicalfox Aug 28 '14 at 10:08

2 Answers2

1

As far as i know we can't do this because there is no dependency mechanism between two report parameters in BIRT, except cascaded parameters. In this case it means the selected value of parameter "db" is never available when the list of items of "Company" is being evaluated.

Therefore the database should be provided using a different approach. You have a couple options here:

Option 1: Session attribute (if you make use of an application server)

Store the selected database in a user session attribute, and then retrieve it in the property binding of the JDBC URL with an expression such:

reportContext.getHttpServletRequest().getSession().getAttribute("db");
  • Advantage 1: the database can be different for each user
  • Advantage 2: the database can be dynamically changed for each user
  • Drawback: Requires to develop a small servlet allowing to select the database and store it in user session

Option 2: Connection Pool (if you make use of an application server) This should always be the favorite way to access a JDBC datasource with BIRT. We just have to fill a JNDI URL in the datasource (see your first screen above). The physical database URL and credentials are defined on each application server in a connection pool.

  • Advantage 1: completely native, nothing specific to be developed
  • Advantage 2: connection pooling are much more efficient than direct-access JDBC
  • Advantage 3: connection pooling prevents "Too many connections" issues
  • Advantage 4: we don't have to hard-code database URL & credentials in reports
  • Drawback: This JNDI URL is shared by all users.

Visit this article to see an example of using a connection pool with BIRT.

Option 3: Externalize DB in Properties file

As you mentionned database informations can also be externalized in a file.

  • Advantage 1: we don't have to hard-code database URL & credentials in reports
  • Advantage 2: we can also develop a small servlet to update this properties file through a GUI
  • Drawback: This database is shared by all users.

Please check this topic to see how to access a properties file through resources of a report

Option 4: Connection profile store

  • Advantage: we don't have to hard-code database URL & credentials in reports
  • Drawback: This database is shared by all users.

Please check this topic to see how to define a connection store

Community
  • 1
  • 1
Dominique
  • 4,272
  • 1
  • 16
  • 21
-1

Answering my own question here for the benefit of others. The best solution to this problem is to use an external connection profile property file as described on this stackoverflow thread link. This didn't work for us originally because I couldn't work out how to make the report file look for the properties file regardless of the computer it was run on. However I discovered it is possible to specify an absolute path when loading/saving the properties file (For BIRT 4.4.0 this is under the drop-down beside the browse button under the connection profile of the data source). This allows the the file to be read on any computer once it is saved under the same absolute path.

The other important thing to note is BIRT has a weird habit of changing which of the 3 methods of loading a data source it will use. During our testing we found that if we didn't leave the property bindings settings blank when using a connection profile, BIRT appeared to randomly choose to load via parameter or load via properties file based on which was edited last, very confusing!

Using a parameter to specify the database is still possible, it just has very limited usage. It worked for us up until this point because while we had the database set as a parameter, the same database was always set in the JDBC which the report used to populate the company parameter. But we no longer want to have to set that database manually, now it will be loaded automatically with no problems (hopefully!).

Community
  • 1
  • 1
logicalfox
  • 83
  • 2
  • 13