SQL query diff between iReport and JasperServer

[kc5qoc]

I have designed a report which works flawlessly in iReport but when I publish the report to jasperserver I get the following errors:

 

 

 

The query that I am using is:

 

"SELECT * FROM auth_analysis_ip WHERE ACT_ADMIN_DT >= $P{Date_Start} and ACT_ADMIN_DT <= $P{Date_End} ORDER BY afac_name,mem_id"

 

I know it looks odd to see $P{Param} instead of $P!{Param} which is contrary to the "iReport User Manual" but that is what works.

 

I am using oracle 10g and the oracle jdbc driver jar "ojdbc14.jar"

 

I have tried 2 versions of the query - one as above and one with the "iReport User Manual" syntax $P!{param} and neither seem to work.

 

Does anyone have any idea what is going wrong here ?

 

Here is the error file:

 

[file name=jasperserver_error.zip size=2195]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/jasperserver_error.zip[/file]

Post edited by: kc5qoc, at: 2007/04/16 17:28

[anandharaj]

Hi,

 

There is no diff in sql query. You need to create the parameter (Input Control) in JasperServer so that it can pass the value in sql query. Make sure the param name is same as wht you define in JRXML file

[anandharaj]

I checked your error file, it seems like problem with data type. You are trying pass a string to int. May be you can do the cast to convert it to ind before execute the query.

 

If im wrong, please correct me.

[kc5qoc]

anandharaj wrote:

There is no diff in sql query. You need to create the parameter (Input Control) in JasperServer so that it can pass the value in sql query. Make sure the param name is same as wht you define in JRXML file

 

anandharaj wrote:

I checked your error file, it seems like problem with data type. You are trying pass a string to int. May be you can do the cast to convert it to ind before execute the query. If im wrong, please correct me.

 

I have defined the input parameters to the report as "Date_Start" and "Date_End" using both Datatype "Date" and Datatype "Date/Time" with the same results - they do not work.

 

I am enclosing my jrxml for the report where you will see the input params defined as java.util.Date with default values.

 

[file name=ip_auth.zip size=4858]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/ip_auth.zip[/file]

 

Is there a way to see what JasperServer is passing as $P{Date_Start} ?

Is there a way to see the actual query string passed to the datacource ? Some kind of debug flag ?

 

I am thinking it may be passing some variant of the java.util.Date - not a long value. Maybe it is passing the the string "Mon Jan 18 2006 12:31 AM MDT" or something like that - I do not know as I have not come across a way to see what is actually being passed to oracle as a query.

 

In reality the query should not work in iReport since the value it is comparing is of type java.sql.Timestamp but that is another issue entirely since JasperServer does not have an input control of type java.sql.Timestamp.

 

Thank you for your help on this issue - It is appreciated.

Post edited by: kc5qoc, at: 2007/04/17 15:55

[shija03]

I don't know if there is a way to tell from JasperServer what queries are taking place - you should at the web.xml file of jasperserver - perhaps there is a debug option. I use SQL Server, and there is a trace option which allows me to see what queries are taking place, MySQL also has a trace option for it's daemon, I couldn't imagine that Oracle wouldn't have a listener of some sort which would allow you to see what queries are taking place.

[anandharaj]

Hi,

Code:

<parameter name="Date_Start" isForPrompting="true" class="java.util.Date">
     <defaultValueExpression ><![CDATA[new java.util.Date(106,0,10)]]></defaultValueExpression>
</parameter>
<parameter name="Date_End" isForPrompting="true" class="java.util.Date">
     <defaultValueExpression ><![CDATA[new java.util.Date(106,0,10)]]></defaultValueExpression>
</parameter>

 

For the above code, change the isForPrompting to false

 

Code:

[code] <queryString><![CDATA[sELECT * FROM auth_analysis_ip WHERE ACT_ADMIN_DT >= $P{Date_Start} and ACT_ADMIN_DT <= $P{Date_End} ORDER BY afac_name,mem_id]]></queryString>

 

For the above query, please try add date function. e.g:

Code:

[code] <queryString><![CDATA[sELECT * FROM auth_analysis_ip WHERE (DATE(ACT_ADMIN_DT) between $P{Date_Start} and $P{Date_End}) ORDER BY afac_name,mem_id]]></queryString>