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; } } }

Condition execution of SQL in BIRT

RSHAH

Hi All,

I have BIRT report having one of the dataset that fails to execute on Oracle 10g.
The solution is to have a different sql written to support the report on Oracle 10g.

Is there any way in BIRT to fetch the data source on which the BIRT report is running and based on that
execute the required sql ?

I am not using any SDK , just have simple data set binded to report .

For e.g I am looking for something like

var dbName=getDBName() <---- This is just to explain if something like this is available in BIRT
if(dbName = Oracle )
{
..
}
else
{
..
}

Find more posts tagged with

Comments

Hans_vd

What part of your SQL statement does Oracle have problems with?
It might be easier to rewrite the query so that it can be used by any database.

Regards
Hans

RSHAH

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="107487" data-time="1343133892" data-date="24 July 2012 - 05:44 AM">
What part of your SQL statement does Oracle have problems with? 
It might be easier to rewrite the query so that it can be used by any database. 
 
Regards 
Hans </blockquote>
 
Hi, 
 
I have a recursive sql in DB2 created using with clause to get all parent for a given entity, 
it seems recursive support on Oracle 10g is not supported via with clause , we need to use CONNECT by clause 
to do the same 
 
Sample SQL on DB2 
 
with Ancestor(ASCENDENT, DESCENDANT, root) AS 
( 
SELECT RI.ASCENDENT, RI.DESCENDENT, ROLE.DN FROM ROLE_INHERITANCE RI 
INNER JOIN ROLE ON ROLE.DN=RI.DESCENDENT 
 
UNION ALL 
 
SELECT RI.ASCENDENT, RI.DESCENDENT, A.root FROM ROLE_INHERITANCE RI,Ancestor A 
WHERE A.ASCENDENT=RI.DESCENDENT 
) 
Select * from Ancestor 
 
- RSHAH

Hans_vd

That's right.
Recursive subqueries like that are available from Oracle 11g release 2.

How are you building your dataset? Isn't it bound to only 1 datasource?

Regards
Hans

RSHAH

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="107531" data-time="1343207193" data-date="25 July 2012 - 02:06 AM">
That's right. 
Recursive subqueries like that are available from Oracle 11g release 2. 
 
How are you building your dataset? Isn't it bound to only 1 datasource? 
 
Regards 
Hans </blockquote>
 
The product I am working on is supported on n different data source , and reports are build to work irrespective of the underlying db to which its hooked. 
BIRT is configured to work with one datasource at time but the report design (using sql ) has to be generic to work with all supported datasource. 
 
So are you aware of any way to fetch database name when the report is executed , if I can get that then using java script I can conditionally execute the sql based on datasource to which report is configured. 
 
-Thanks

Hans_vd

Hi RSHAH, 
 
I've been fooling around a bit and with it and I managed to get the database url in the dataset beforeOpen method using this statement: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>dbURL = this.dataSource.getExtensionProperty("odaURL");</pre>
 
When used with the sample database, this results in "jdbc:classicmodels:sampledb" 
 
Hope this helps 
Hans

Hans_vd

Another option could be to create a stored procedure on both databases and have them returning the data. At least, if the syntax for calling a stored procedure on both databases is the same of course.

Regards
Hans

RSHAH

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="107586" data-time="1343309947" data-date="26 July 2012 - 06:39 AM">
Hi RSHAH, 
 
I've been fooling around a bit and with it and I managed to get the database url in the dataset beforeOpen method using this statement: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>dbURL = this.dataSource.getExtensionProperty("odaURL");</pre>
 
When used with the sample database, this results in "jdbc:classicmodels:sampledb" 
 
Hope this helps 
Hans </blockquote>
 
 
Thanks for checking this , could you guide me as to where I should be adding this line ? 
I tried adding this by opening one of the dataset of the report (xyz.rptdesign) and adding it under Property Binding tab . 
but it resulted in error 
"Error evaluating Javascript expression. Script engine error: TypeError: Cannot call method "getExtensionProperty" of undefined (property binding#3)" 
 
by looking at the result you pasted, it seems it will just give db name not sure if it will return 
the database type (for e.g. Oracle , DB2 , ms-sql etc) 
Appreciate your help in this , 
 
-RSHAH

Hans_vd

Hi RSHAH,

As I wrote in my post, I call that statement in the beforeOpen event of the dataset.

And don't you just "know" what kind of database you are on from the url? I mean, how can you create a datasource to some database without knowing the DBMS you are on? You have to select the right driver, don't you?

Still I think it's a better idea to ship a stored procedure with the databases and then call it from the dataset.

Regards
Hans