Default parameters passed to a query

[2004 IR Help]

By: Mark Rhodes - mrhodes2

Default parameters passed to a query

2002-12-17 02:29

Are you able to use default parameters within iReports to compile/test? The parameter works if I compile without a connection. In this case is prints an enddate in the report header. I'm getting following error when I use the parameter in a query.

 

 

--------------- error from compile window ------------------

C:j2sdk1.4.1_01binjava.exe -Dorg.xml.sax.driver=org.apache.xerces.parsers.SAXParser IReportCompiler -c -pdf -jdrivercom.microsoft.jdbc.sqlserver.SQLServerDriver -jusereswenson -jpasswdcold -jurljdbc:microsoft:sqlserver://nt4.sydranhq.com:1433;DatabaseName=SydranBK -query "select * from webreporting.dbo.rpthalfhourproductivity where userid = 'mrhodes' and salesdate >= '12/10/02' and salesdate <= $P{penddate} order by storeid,salesdate,hhid,shift" "C:Program FilesiReport-0.0.6Reportshalfhourproductivitynographics.xml"

 

File: C:Program FilesiReport-0.0.6Reportshalfhourproductivitynographics.xml

 

Compiling to file... C:Program FilesiReport-0.0.6Reportshalfhourproductivitynographics.jasper

 

Compilation running time : 3406

 

Opening connection....

 

Executing query select * from webreporting.dbo.rpthalfhourproductivity where userid = 'mrhodes' and salesdate >= '12/10/02' and salesdate <= $P{penddate} order by storeid,salesdate,hhid,shift

 

java.sql.SQLException: [Microsoft][sqlServer 2000 Driver for JDBC]Syntax error at token }, line 0 offset 9.

 

at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)

 

at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)

 

at com.microsoft.jdbc.base.BaseEscapeTranslator.parseEscape(Unknown Source)

 

at com.microsoft.jdbc.base.BaseEscapeTranslator.translateEscape(Unknown Source)

 

at com.microsoft.jdbc.base.BaseSQLEscapeProcessor.visit(Unknown Source)

 

at com.microsoft.jdbc.base.BaseSQLTreeTraverser.visit(Unknown Source)

 

at com.microsoft.jdbc.base.BaseSQLTreePreOrderTraverser.traverse(Unknown Source)

 

at com.microsoft.jdbc.base.BaseSQLTreePreOrderTraverser.traverse(Unknown Source)

 

at com.microsoft.jdbc.base.BaseSQLEscapeProcessor.processEscapes(Unknown Source)

 

at com.microsoft.jdbc.base.BaseSQL.translateEscapes(Unknown Source)

 

at com.microsoft.jdbc.base.BaseSQL.processSQL(Unknown Source)

 

at com.microsoft.jdbc.base.BaseSQL.<init>(Unknown Source)

 

at com.microsoft.jdbc.base.BaseStatement.preProcessSQL(Unknown Source)

 

at com.microsoft.jdbc.base.BaseStatement.executeQuery(Unknown Source)

 

at IReportCompiler.main(IReportCompiler.java:145)

 

 

 

Abnormal termination!

 

Following is parameter definition:

<parameter name="penddate" isForPrompting="true" class="java.lang.String">

<defaultValueExpression><![CDATA[new String("12/10/02")]]></defaultValueExpression>

</parameter>

 

 

 

 

By: Giulio Toffoli - gt78

RE: Default parameters passed to a query

2002-12-17 07:52

There is a bug in jasperreports jar shipped with iReport using default parameters. When the parameter is used within a query, in example if I have a query like "SELECT * FROM USERS WHERE NAME='$P{myname}' " and id I set 'Giulio' as default parameter, the parameter is not subsituted creating the query "SELECT * FROM USERS WHERE NAME='Giulio' " (this is your problem, I think). This problem was fixed in the last jasperreports release. Try to use the last released jasperreports.jar. You can find it at http://www.sourceforge.net/projets/jasperreports (remember to change jdk classpath in iReport to force the use of this new jar).

 

Good Look!

 

Giulio

 

 

 

 

 

 

By: Mark Rhodes - mrhodes2

RE: Default parameters passed to a query

2002-12-17 08:39

That is my problem exactly. I already had upgraded the .jar file with jasperreport.0.4.4 dated 12/1/02. Any other thoughts? Did I set the parameter correctly - new String("12/10/02")? If you think this is a jasper reports problem, I can post over there.

 

 

 

 

By: Mark Rhodes - mrhodes2

RE: Default parameters passed to a query

2002-12-19 16:05

solved - had to clear the Query SQL when I do the compile. Your tip on subreports, made me think to try this.

 

 

 

 

By: Giulio Toffoli - gt78

RE: Default parameters passed to a query

2002-12-20 01:05

Offcourse!

 

I'm happy that you have solved. Stay tuned. Before xmas I'll release ireport 0.0.7, a gift for all iReport users!

 

Giulio