nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to make the database connection dynamically?

appform

Hi All,

I want to make the database connection dynamically instead of connecting from iserver everytime.

Regards,
Swapna.

Find more posts tagged with

Comments

HPP

hi
Can u specify ur requirement more clearly, u want to make the connection dynamically, or more precisely the result set columns must be dynamic..

appform

Hi,

Suppose I am having 2 data sources,from php script i will send the data source name and in birt report designer it will connect to that data source.

How to do this ? Is it Possible?

Regards,
Swapna.

kclark

It sounds like you want to use a scripted datasource. You want to be able to create the data source and data set from script if I'm understanding correctly. <a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/544-sample-birt-scripted-data-source-example/'>Here's a devshare showing how to use scripted datasources.</a>

appform

Hi,

Thanks for your quick response!!!

So to provide a little more detail.....we are a multi-tenant system with a set of base tables (company details, user details), each new client once registered will then have their company and user details entered into the base table and also get their own database for their own content for the application...the two will be connected via database ID say......hope this architecture makes sense.

The problem we are facing at present is as follows:

(1) We build a standard BIRT report and connect to a single datasource say CLIENT A - this is fine and we have no problem doing this
(2)When next client registers then we have a problem as there does not appear to be a way to dynamically send the database to use into the BIRT report so it pulls back data from the correct data source (it seems our only option is to copy reports into a new folder for the new client (we can do this automatically) and then change the data source manually for each client - this is not really an option)

We are using php/javascript.....KClark thanks for the response but looks like it is in java and not 100% sure how that script is working - is there a PHP method of doing this as we dont have java expertise at present and requirement is urgent.

Regards,
Swapna.

Tubal

How will the report server know which client is trying to run the report? Would you enter this at runtime? 
 
There are multiple ways to do this. 
 
You could pass a parameter in the URL of the report, something along the lines of: 
<pre class='_prettyXprint _lang-auto _linenums:0'>http://myreportserver.com/birt/frameset?__report=report/myreport.rptdesign&__client=myclientid</pre>
 
You would need a hidden parameter called 'client' as part of your report that would accept the data from the URL. 
 
then in the beforeFactory event of your report, you would write a script to change the datasource attributes. 
 
Something along the lines of: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>//this puts the datasource you want to change (I called it myDataSource) in a variable called 'ds'
ds = reportContext.getDesignHandle().findDataSource("myDataSource");
//this changes the db connection URL to "jdbc:postgresql://mydbserver.com/db-myclientid"
ds.setProperty( "odaURL", "jdbc:postgresql://mydbserver.com/db-" + params["client"]);
</pre>
 
Of course, this way isn't secure, as any client could just change their clientid to someone else and see their data. But you get the general idea. 
 
If your php app uses cookies, you can also retrieve a cookie holding their client id, assuming our cookie is named client: 
 
(this would also go in the beforeFactory event of your report) 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>importPackage( Packages.javax.servlet.http );

var request = reportContext.getHttpServletRequest();
var cookies = request.getCookies();
var client="";
for (i=0; i< cookies.length; i++)
{
if(cookies[i].getName().equals("client")){
client=cookies[i].getValue();
}
}

ds = reportContext.getDesignHandle().findDataSource("myDataSource");
ds.setProperty( "odaURL", "jdbc:postgresql://mydbserver.com/db-" + client);</pre>