Monday, January 30, 2006

Swapping Databases at Runtime with BIRT 2.0

Swapping Databases at Runtime with BIRT 2.0

Often it is desirable to compose a report against a development system before promoting it to a production system.  Although this could be done prior to BIRT 2.0, it required scripting.  A new Database Property Binding editor is now available that speeds this process.

To use the Property Binding editor, edit the data source as shown below.



  Select the Property Binding item.



Within this editor, the JDBC Driver Class, JDBC Driver URL, User Name and Password properties can be changed dynamically.  The values for these properties can be typed in or entered through the BIRT Expression Builder, which is accessed by clicking the elipse icon ("...").  

To swap the database at runtime the JDBC Driver URL will need to be modified.
In this example, this will be accomplished by adding a report parameter that determines if the report is to run in Production or QA.  This parameter will be accessed in the expression set on the JDBC Driver URL property.



The JDBC URL is swapped based on the Production boolean parameter using this expression.

if ( params["Production"] ){
     "jdbc:mysql://localhost/production";
}else{
     "jdbc:mysql://localhost/qa";
}

That’s all there is to it.  

6 comments:

Anonymous said...

I have tried this and I cant get it to work work.

I tried the following:

if (params["DEPLOYMENT"]=="test") {"jdbc:oracle:thin:@test:1521:bos"; } else {"jdbc:oracle:thin:@live:1521:bos"; }

The article doesnt say what "params" is, how it is changed or where it comes from. I assume it is an env. var. On windows I am setting this as a system env. variable.

the error I get is something like
Script engine error: DEPLOYMENT not found

All we want to do is write a report which can be deploy to a test enviroment or a live environment, i.e. point to a test db or a live db.

Anonymous said...

I have tried this and I cant get it to work work.

I tried the following:

if (params["DEPLOYMENT"]=="test") {"jdbc:oracle:thin:@test:1521:bos"; } else {"jdbc:oracle:thin:@live:1521:bos"; }

The article doesnt say what "params" is, how it is changed or where it comes from. I assume it is an env. var. On windows I am setting this as a system env. variable.

the error I get is something like
Script engine error: DEPLOYMENT not found

All we want to do is write a report which can be deploy to a test enviroment or a live environment, i.e. point to a test db or a live db.

Jason Weathersby said...

I should described it more clearly.
The params["DEPLOYMENT"] is a report parameter. In your code define it as a string and set it's default vaue to test not "test".

Jason

Anonymous said...

To add to this, I've successfully implemented this idea, and it works great in my charts and such, thanks! However, I have an addition parameter which is a dynamic list box that depends on this swapping of databases. How would you get this parameter to refresh when the database is changed?

Anonymous said...

This works great, but we want the report to chose the db based on an OS evniroment variable. According to the field guide, config[] should do this, but substituting config["Deployment"] for params["Deployment"] after setting the system parameter in XP, it doesnt work. I even tried setting it as a -D aparameter in the eclipse startup shortcut, but no joy. Anyone tried this?

Anonymous said...

Good ! It works fine. But I wish that DB details should not be shown explicitly as parameters...
Anyother go?