parameters in dynamic query string

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