parameters in dynamic query string

[2002 JI Open Discussion]

By: Elba - elbas

parameters in dynamic query string

2003-01-13 16:23

Hi Teodor, I have a problem with dynamic query string,

I'm using the syntax : $P!{param_name}, to allow

to the user to provide this parameter or not.

So, when the two parameters are not provided, my messages

on the console about compiling, filling and printing in pdf

are successfully, but I can't see anything on the page,

nothing is displayed.

But when I provide only one of the parameters even both,

I got an error message like:

 

java.sql.SQLException: Invalid column name 'SCFI'

NESTED BY :

dori.jasper.engine.JRException: Error executing report query :

select elec.ce_marca, lab.lab_nombre, nom.nom, cli.cli_empresa

from C_ELECTRONICA elec, LABORATORIOS lab, NOMS nom, CLIENTES cli

where elec.CE_LABORATORIO=lab.ID_REGISTRO

and elec.CE_NORMA=nom.ID_REGISTRO

and elec.CE_EMPRESA=cli.ID_REGISTRO

and nom.nom=NOM-016-SCFI-1993

and cli.cli_empresa=null

 

The parameters provided use '-' and ',' .

How can I fix that?

elbas

 

 

 

 

 

By: Teodor Danciu - teodord

RE: parameters in dynamic query string

2003-01-13 23:46

 

Hi,

 

You hav to quote the value that you are filtering on,

since it is a character sequence and not a column

nam i guess:

 

...

and nom.nom='NOM-016-SCFI-1993'

...

 

or

 

...

and nom.nom="NOM-016-SCFI-1993"

...

 

I hope this helps.

Teodor

 

 

 

 

 

By: Elba - elbas

RE: parameters in dynamic query string

2003-01-14 09:03

I'm still have the problem, when both parameters are provided,

the report is ok but when one of the parameters are missing,

or all the parameters are missing, I got the message:

This file cannot be opened because it has no pages.

I'm trying with:

select e.marca, lab.name, nom.nom, cli.empresa

from table1 e, table2 lab, table3 nom, table4 cli

where e.lab=lab.ID

and e.nom=nom.ID

and nom.nom='$P!{FiltroNorma}'

and cli.cli_empresa='$P!{FiltroEmpresa}'

 

It's wrong the ! in the query?

elbas

 

 

 

 

 

By: Teodor Danciu - teodord

RE: parameters in dynamic query string

2003-01-14 23:18

 

Hi,

 

There is nothing wrong.

Is just that when one of the parameters are missing,

the query filter becomes something like this:

(parameter FiltroNorma missing)

 

...

and nom.nom=''

...

 

or worse

 

...

and nom.nom='null'

...

 

I'm sure with such a filter, the query will return

no rows and by default, the engine will not create

any pages.

If you want it to create something however, use

the "whenNoDataType" attribute.

 

But I think the way you have created your query

is not approapriate as I'm sure that when one

of the parameters is missing, you want it to be

ignored in the filter. That means that the WHERE

clause should be build at runtime, depending

on the mentioned conditions.

 

I hope this helps.

Teodor