kc5qoc Posted April 17, 2007 Share Posted April 17, 2007 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 Link to comment Share on other sites More sharing options...
anandharaj Posted April 17, 2007 Share Posted April 17, 2007 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 Link to comment Share on other sites More sharing options...
anandharaj Posted April 17, 2007 Share Posted April 17, 2007 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. Link to comment Share on other sites More sharing options...
kc5qoc Posted April 17, 2007 Author Share Posted April 17, 2007 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 Link to comment Share on other sites More sharing options...
shija03 Posted April 21, 2007 Share Posted April 21, 2007 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. Link to comment Share on other sites More sharing options...
anandharaj Posted April 22, 2007 Share Posted April 22, 2007 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> Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now