Query as Parameter

[2001 JI Open Discussion]

By: Venkat - venkat_pasham

Query as Parameter

2005-03-01 21:47

Can the entire SQL query be passed as parameter in Jasper Reports. If so, can someone please give me the syntax to do so. I tried something like this P!{query}.....query being the key representing the SQL Query in the Hash Map passed as parameter to the report. Outside the report the same query is working fine.

 

The following error is thrown:

 

"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 'QueryString' at line 1"

 

Thanks,

Venkat

 

 

 

By: pramod - pramodkrao

RE: Query as Parameter

2005-03-02 00:28

what u have done seems right,

also from the message it seems that there is a error in the sql statement that u r using.

pls check ur sql statment or show it here so that it can be verified.

 

Thanks,

pramod.

 

 

By: Venkat - venkat_pasham

RE: Query as Parameter

2005-03-02 07:06

I appreciate ur help.

 

Here is the query as declared in Java:

 

QueryString = "SELECT a.PATIENT_ID,a.HOME_PHONE,a.SERVICE_NAME, " +

"DATE_FORMAT(a.DATE,'%W %M %D %Y') as DATE,a.START_TIME, a.LAST_NAME, " +

"a.FIRST_NAME, b.RESOURCE_NAME from appoint a, appoint_resource b " +

"where a.RESOURCE_ID = b.RESOURCE_ID and CURDATE() > a.DATE and CHECKIN_TIME is NULL and a.START_TIME is not NULL order by a.DATE";

 

There r 2 tables appoint and appoint_resource.

 

When i executed the same query using JDBC connection outside the jasper report, it seemed to be working fine.

 

Thanks,

Venkat

 

 

 

By: Daren O - rckrll106

RE: Query as Parameter

2005-03-02 07:19

Jasper doesn't support dot notation in the select statement. Try SELECT a.PATIENT_ID as PATIENT_ID,a.HOME_PHONE as HOME_PHONE,a.SERVICE_NAME as SERVICE_NAME.....etc...

 

 

By: Doug Berkland - berkland

RE: Query as Parameter

2005-03-02 07:27

Do you have

 

<queryString><![CDATA[P!{QueryString}]]></queryString>

 

or

 

<queryString><![CDATA[$P!{QueryString}]]></queryString>

 

?

 

The second one is correct. If the $ is missing that is your problem.

 

 

By: Venkat - venkat_pasham

RE: Query as Parameter

2005-03-02 11:35

I have it like this

 

<queryString><![CDATA[$P!{QueryString}]]></queryString>

 

and I figured out the mistake too.....it was a simple mistake. I am passing the query as a parameter to the report. So when including it in the Hash Map it wrote something like this parameter.put("query", "QueryString"). I Included the value (QueryString) for the key (query) in double quotes.........which is not correct.....It worked when I removed the quotes.

 

Thanks,

Venkat