JasperServer & dynamic query via parameters

[silenco]

Hi all,

 

I've got a Problem building a dynamic query with JasperServer ... don't know if it's a bug?!

 

I build the Query with some logic encapsulated in dummy parameters:

 

Query / SQL:

Code:

select *
from registration
where $P!{WHERE}

 

WHERE Parameter (default value expression):

Code:

[code]($P{INPUT_DATE_FROM} != null?" and reg_date >= '"+$P{INPUT_DATE_FROM}+"' ":""«») + ($P{INPUT_DATE_THROUGH} != null?" and reg_date <= '"+$P{INPUT_DATE_THROUGH}+"' ":""«»)

 

Running the Report in IReport, everything works fine.

Uploading this to the JasperServer following error occure:

 

Code:

[code]... Caused by: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'null' at line 4 ...

 

... which means $P!{WHERE} returns NULL on the jasper server...

 

Is anyone familiar with that problem or does anyone have found a workaround for that, yet?

 

I searched the forum up and down ... but I didn't find anything similar ...

 

any help is highly appreciated !

Thanks,

Simon

 

iReport: 1.3.1

jasperServer: 1.2.0

Post edited by: silenco, at: 2007/03/02 19:41

[lucianc]

Can you set

Code:

log4j.logger.net.sf.jasperreports.engine.query.JRJdbcQueryExecuter=debug

in WEB-INF/classes/log4j.properties so that JR would log the SQL query?

 

What input controls have you defined for the report unit?

 

Regards,

Lucian

[silenco]

Hi Lucian,

 

many thanks for your reply.

The Log now shows the SQL Query:

Code:

15:06:05,833 DEBUG JRJdbcQueryExecuter,http-8080-Processor23:135 - SQL query string: select *
from r__registration
where null
15:06:05,849 ERROR EngineServiceImpl,http-8080-Processor23:572 - Error while filling report
net.sf.jasperreports.engine.JRException: Error executing SQL statement for : registration_jrxml_1173189673239

 

... for the defined Query:

Code:

[code] select * from registration where $P!{WHERE}

 

regards,

simon

[silenco]

Hi again!

 

... I solved it... more or less:

 

The problem are the queries, which are defined NOT inside the jrmxl ... defining the queries in the JasperServer and referencing them in a Report Unit causes the NULL in the query... defining them inside the jrxml it works.

 

That's a pity because I would like to define one jrxml and different Report Units in the JasperServer with different queries... respectively different jrxml with the same query, but another view (jrxml).

 

I really liked the way defining all parts external in order to reused it...

 

Do you have an idea how to do this?

Is that behaviour purposely or is it a bug?

 

Many Thanks,

Simon

[martynhiemstra]

You can define queries with jasperserver but the problem is they dont support parameters too well.

 

For example, I have a parameter called days_count. A person can indicate a begin and end date and the days between them musnt be more than 50 days. To calculate the amount of days between to dates I use a own made java method called countDays(Begindate, Enddate). days_count is determined when the report is being made. It's a parameter that has a default value which is the method call in a custom class. A problem occures when using a query defined in jasperserver and using that parameter in your query. Lets say you have WHERE $P!{days_count}<=50. The query will be WHERE null<=50. If you put the query in the report everything works fine.

 

The other problem I found is the design flaw in jasperserver. I am trying to create a bean datasource. I found out that it's impossible to pass the report parameters onto a factory or even the report object, making filtering of bean info impossible. This is a design flaw that really limits jasperserver.

 

I choose not to use queries because I dont want my customers executing queries directly on the database. Instead I create bean for them, fill it with information, and pass that on to the report, but that's impossible due to the design of jasperserver. I can't filter the information the bean loads because I have no access to the parameters.

 

It's to bad because I have designed reports outside jasperserver and I got those reports to work with beans and custom parameters, for example input boxes on a website.

 

If you have a enviroment, one you can maintain yourself and you only have 1 or 2 reports, I would recommend that you design your own small spring app that outputs your report via pdf or excel using spring controllers. This will save you alot of time.

[silenco]

Hi martyn,

 

thanks for your reply!

 

Indeed my report application has a spring based & mda developed architecture. With iReport you can define a Spring Datasource, based on your "applicationContext.xml". That feature is very nice...as you can work with your beans... but unfortunately it's not so easy to get that environment running with the JasperServer, respectivley i didn't get it working at all.

 

... I thought about developing my own webfrontend with services executing the reports, too.

... but a few points made me use JasperServer anyway:

 

-> reusable, typesafe input controls, which are defined in the webapp and loosely bound to the reports is quite a nice feature!

-> the analysis function & the creation of drill down tables is a very nice feature too, which I am planning to use in future.

-> Yes, there are still many problems with the JasperServer .. BUT: It's only the version 1.2.0 ... I think there will be a lot development concerning the server in future!!

-> The Scheduler is also nice, espacially for reports, that are often requested and only hourly updated...

 

... for the beginning (13 Reports) I will use JDBC with SQL Queries and little logic in the queries, as it is the ETL process that has to cover that ... later on it could easily be adopted into a Spring architecture ...

 

I think for a quick setup and a few reports, it's perhaps better to develop up your own application...but in the long run, and escepially when your are planning to offer a lot of reports ...etc. your are doing very good with JasperServer.

 

regards,

simon

[thevoice]

Hi silenco,

I am uploading .jrxml file that is designed using iReport tool.

My report work fine when i run it in iReport.

But when i upload the same .jrxml file in jasper server and run it using text input control,it show me "The report is empty."

Can you explain what is reason for such as i am unable to get it.

What i have to do to run report using input control?

What query i have to set run report?

 

Following is query that i used to run report:

 

SELECT first_name,last_name,email_addr

FROM contacts

WHERE first_name!='' AND last_name!='' AND last_name=$P{LAST_NAME}

 

Thank you.