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

Dynamic query modification

libelule

Hi everybody, 
 
here is my problem : 
 
i have a dataset, with a base query : 
 
select lib_support, titre, count (public.consultation.id_consult) 
from consultation,piste,support 
where public.consultation.id_media = public.piste.id_piste and public.support.id_support = public.consultation.id_support 
and public.consultation.date_consult between ? and ? 
group by lib_support,titre 
order by count (public.consultation.id_consult) desc 
limit ? 
 
in the "beforeOpen" method of the dataset script, 
 
i modify my base query according to my optional params. 
For each param, i have this : 
 
<blockquote class='ipsBlockquote' >
 
if ( params["bla"].value != null ) { 
 
this.queryText = "select lib_support, titre, count (public.consultation.id_consult) 
from consultation,piste,support 
where public.consultation.id_media = public.piste.id_piste and public.support.id_support = public.consultation.id_support 
and public.consultation.date_consult between ? and ? 
 
and table.param ='"+param+"' 
 
group by lib_support,titre 
order by count (public.consultation.id_consult) desc 
limit ? "; 
 
} 
 </blockquote>
 
this works for each single param, but if i combine all the params, in the report, they are not all considered. 
 
 
if i make this : 
 
if ((parm1 != null) || (param2 != null))...... {... } 
 
i have a stackoverflowerror, even if i have increased the " -Xmx " value in eclipse. 
 
and i can't split my base query.... 
 
 
so, is it possible in the script, to split the base query and start from : 
 
queryText = "where......" ? 
 
Thanks in advance, 
 
Regards,

Find more posts tagged with

Comments

johnw

Without seeing the actual report, I am going to go so far as to say that you probably only have a single parameter in your dataset, so when you start with multiple paramters, your only binding to a single dataset parameter. You can modify the where clause, but you will run into some minor performance issues with databases that use an execution plan, such as Oracle or MySQL, since you are not using parameter binding correctly.

I would check your data set, make your base query without the where clause, and dynamically add your query parameters at runtime. you will need to do this in the reports initialize or beforeFactory in order to get access to the datasets design handle.

libelule

hi Johnw, 
 
i don't exactly get what you are saying. 
 
So you are saying that i only have to declare one parameter in my dataset, if i do so my dataset query would be : 
 
<blockquote class='ipsBlockquote' >
 
select lib_support, titre, count (public.consultation.id_consult) 
from consultation,piste,support 
group by lib_support,titre 
order by count (public.consultation.id_consult) desc 
limit ? 
 </blockquote>
 
is that what you are saying.....? 
 
regards

johnw

No, what I am saying is that you don't declare any, and at run time add them to your dataset.

So your base query would look like:

"select * from table".

At runtime (either in the data sets onOpen or the reports beforeFactory), you modify the query to be:
queryText += " where ";
for (x = 0; x < numberOfParams; x++)
{
queryText += " where field" + x + " = ?";
}

Where fieldX would repeat for the number of parameters you want to pass in (assuming it is dynamic).

In either the initialize or beforeFactory events, you get the the data set
dataHandle = reportContext.getDesignHandle().findElement("dataSet");

and you add the parameters:
for (x = 0; x < numberOfParams; x++)
{
dataHandle.getInputParameters.add();
}

At least, thats the high level idea. the specifics need to verified (doing this off the top of my head).

Remember, it is parameter binding, so if the dataset doesn't have enough input parameter declarations, it will error out, and if there are too few, it will ignore them. You need to add them, and just be sure to pass them in the correct order.

libelule

Hi johnw,

my base query must be :

select lib_support, titre, count (public.consultation.id_consult)
from consultation,piste,support
where public.consultation.id_media = public.piste.id_piste and public.support.id_support = public.consultation.id_support
and public.consultation.date_consult between ? and ?
group by lib_support,titre
order by count (public.consultation.id_consult) desc
limit ?

i am just looking for a way to retrieve the "where" clause.

if i do like you said, with a base query like :

"select * from table".

i no longer need to follow your procedure cause i know how to handle regular query.

Thanks for your reply,

regards,

johnw

OK, the query comes back as a string. To get access to the where clause, you just do a queryString.split("where")[1], or something along those lines. Your original logic is correct. You can modify that where clause, but keep in mind, if you are adding in data set parameter bindings to that where clause (adding in additional ? marks), you will need to add entries to the input parameters collection in the data set, and bind those values correctly.

libelule

Hi Johnw,

Thank you very much, i will try that out,

Regards,

johnw

Give this a look. It seems like what you are trying to do. Although it is not using data set parameters, it is doing a string substitution, it should accomplish the same task and not have the same error. 
 
I would add some validation logic to the parameters to prevent sql injections is the only addition. 
 
<a class='bbc_url' href='http://www.birt-exchange.org/devshare/designing-birt-reports/806-dynamic-query-modification/#description'>Dynamic Query Modification - Designs & Code - BIRT Exchange</a>

libelule

Thank you very much JOHNW,

Here is how i solved my problem, for those who will have the same issue :

i have a dataset, with a base query :

select lib_support, titre, count (public.consultation.id_consult)
from consultation,piste,support
where public.consultation.id_media = public.piste.id_piste and public.support.id_support = public.consultation.id_support
and public.consultation.date_consult between ? and ?

then, in the "beforeOpen" method of the dataset script,

i modify my base query according to my optional params.
For each param, i have this :

Quote:

if ( params["bla"].value != null ) {

this.queryText += "and table.param ='"+param+"'

}
...

this.querytext += "group by lib_support,titre
order by count (public.consultation.id_consult) desc
limit ? ";

Thank you for your help JOHNW,

Regards,