Got a "Error evaluating expression" error

[tuanfgy]

Hi all,

Please help me to fix this bug. I got this error message when create a report with union data type using Jasper Report

 

org.quartz.JobExecutionException: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression :

    Source text : $P{Test}.getArtSection().getSQLSelect($V{Test_WHERE},$V{1ExecutionTime}) [see nested exception: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression :

    Source text : $P{Test}.getArtSection().getSQLSelect($V{Test_WHERE},$V{1ExecutionTime})]

 

I think the error occur because the below code.

subparm = new JRDesignSubreportParameter();
        subparm.setName("QUERY");
        expression = new JRDesignExpression();
        expression.setValueClass(java.lang.String.class);
        expression.addParameterChunk(section.getName());
        expression.addTextChunk(".getArtSection().getSQLSelect(");
        expression.addVariableChunk(section.getName()+"_WHERE");
        expression.addTextChunk(",");
        expression.addVariableChunk("1ExecutionTime");
        expression.addTextChunk(")");
        subparm.setExpression(expression);
        subrpt.addParameter(subparm);

 

Do you see any problem with this code. I you need to see all code please find the attachment.

Thanks in advance

[szaharia]

Hi,

The problem might come from the 1ExecutionTime variable that wasn't declared in the report. You need to declare it in order to use it, the same way as you declared the "var" variable:

JRDesignVariable var_1ExecutionTime = new JRDesignVariable();
var.setName("1ExecutionTime");
//the other settings here;
addVariable(var_1ExecutionTime);

Regards,

sanda

[tuanfgy]

Hi Sanda,

I did acccording to you. But I got this error:

org.quartz.JobExecutionException: net.sf.jasperreports.engine.JRException: Duplicate declaration of variable : 1ExecutionTime [see nested exception: net.sf.jasperreports.engine.JRException: Duplicate declaration of variable : 1ExecutionTime]

I think the "1ExecutionTime" was declared some where. I'm new with JR so that I don't know many. Could you please help to check my code and show me where is not correct

 

JRDesignVariable var_1ExecutionTime = new JRDesignVariable();

var_1ExecutionTime.setName("1ExecutionTime");

subparm = new JRDesignSubreportParameter();

subparm.setName("QUERY");

expression = new JRDesignExpression();

expression.setValueClass(java.lang.String.class);

expression.addParameterChunk(section.getName());

expression.addTextChunk(".getArtSection().getSQLSelect(");

expression.addVariableChunk(section.getName()+"_WHERE");

expression.addTextChunk(",");

//expression.addVariableChunk("1ExecutionTime");

expression.addTextChunk(")");

// expression.addVariableChunk(section.getName()+"_QUERY");

subparm.setExpression(expression);

subrpt.addParameter(subparm);

addVariable(var_1ExecutionTime);

 

All code of this file is in attachment.

 

Thank you for your support.

Tuan

[szaharia]

Thank you for the feedback, Tuan.

While focusing on the buildSectionSubReport(...) code I noticed that the 1ExecutionTime variable was not created there, so I assumed that its declaration was missing from the report. But the new error message states it was already created, probably from outside the class, so there is no more need to declare it in buildSectionSubReport(...).
We have to focus on other possible causes of the issue.
The expression, as it results from the initial error message, appears to be well formed:

$P{Test}.getArtSection().getSQLSelect($V{Test_WHERE},$V{1ExecutionTime})

There are no syntax errors here, so it should be properly evaluated if:

1. A parameter named "Test" was declared in the report and its value is not null;
2. getArtSection() and getSQLSelect(...) represent valid methods that can be called from the $P{Test} object; getArtSection() in this case may not return null;
3. $V{Test_WHERE} and $V{1ExecutionTime} are variables declared in the report and their types correspond to the getSQLSelect(...) arguments;
4. getArtSection() and getSQLSelect(...) are not throwing exceptions at runtime

Try to check if the above conditions are satisfied. For (4) you can test the expression using proper values for parameters or variables, to see what happens.
 
Regards,
sanda

[szaharia]

Another helpful thing is to get a complete stacktrace of the error, if possible.

[tuanfgy]

Hi Sanda, I don't know which log is stacktrace. I don't is this or not? Could you please review it.

  12/05/2012 10:10:57 [iNFO] Added trigger: Trigger 'vsreports.jobid=40':  triggerClass: 'org.quartz.SimpleTrigger isVolatile: false calendar: 'null' misfireInstruction: 1 nextFireTime: Wed Dec 05 10:10:57 ICT 2012
12/05/2012 10:10:58 [iNFO] Job vsreports.jobid=40 threw a JobExecutionException:
org.quartz.JobExecutionException: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression :
Source text : $P{Test}.getArtSection().getSQLSelect($V{Test_WHERE},$V{1ExecutionTime}) [see nested exception: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression :
Source text : $P{Test}.getArtSection().getSQLSelect($V{Test_WHERE},$V{1ExecutionTime})]
at com.lucent.vital.vq.reportserver.quartz.QuartzReportJob.execute(QuartzReportJob.java:122)
at org.quartz.core.JobRunShell.run(JobRunShell.java:199)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)
Caused by: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression :
Source text : $P{Test}.getArtSection().getSQLSelect($V{Test_WHERE},$V{1ExecutionTime})
at net.sf.jasperreports.engine.fill.JREvaluator.evaluate(JREvaluator.java:195)
at net.sf.jasperreports.engine.fill.JRCalculator.evaluate(JRCalculator.java:589)
at net.sf.jasperreports.engine.fill.JRCalculator.evaluate(JRCalculator.java:557)
at net.sf.jasperreports.engine.fill.JRFillSubreport.getParameterValues(JRFillSubreport.java:552)
at net.sf.jasperreports.engine.fill.JRFillSubreport.getParameterValues(JRFillSubreport.java:460)
at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluateParameterValues(JRFillSubreport.java:359)
at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluateSubreport(JRFillSubreport.java:339)
at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluate(JRFillSubreport.java:263)
at net.sf.jasperreports.engine.fill.JRFillElementContainer.evaluate(JRFillElementContainer.java:258)
at net.sf.jasperreports.engine.fill.JRFillBand.evaluate(JRFillBand.java:499)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillColumnBand(JRVerticalFiller.java:2026)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillDetail(JRVerticalFiller.java:757)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportStart(JRVerticalFiller.java:269)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:127)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:942)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:860)
at net.sf.jasperreports.engine.fill.AsynchronousFillHandle$ReportFiller.run(AsynchronousFillHandle.java:148)
at java.lang.Thread.run(Thread.java:662)
Caused by: com.lucent.vital.vq.exceptions.vqSQLException: BEGIN CREATE TABLE VART_TMP_1354677058073(Available FLOAT,Color VARCHAR(20),DateTime DATETIME,FullNamebyFirstName VARCHAR(100),FullNamebyLastName VARCHAR(100),ItemCode VARCHAR(60),ItemDescription VARCHAR(1024),Make VARCHAR(80),Model VARCHAR(80),PaymentType VARCHAR(20), VARCHAR(10),Total FLOAT,UnitCost FLOAT,UnitPrice FLOAT);INSERT INTO VART_TMP_1354677058073 SELECT Avail AS Available,Color AS Color,null AS DateTime,null AS FullNamebyFirstName,null AS FullNamebyLastName,Code AS ItemCode,Description AS ItemDescription,Make AS Make,Model AS Model,null AS PaymentType, AS ,Total AS Total,Cost AS UnitCost,Price AS UnitPrice FROM INV_TABLE;INSERT INTO VART_TMP_1354677058073 SELECT null AS Available,Color AS Color,Dttm AS DateTime,FNAME + ' ' + MI + '. ' + LNAME AS FullNamebyFirstName,LNAME + ', ' + FNAME + ' ' + MI + '.' AS FullNamebyLastName,Code AS ItemCode,Description AS ItemDescription,Make AS Make,Model AS Model,BillingType AS PaymentType, AS ,null AS Total,Cost AS UnitCost,Price AS UnitPrice FROM TRANS_BILLING_VIEW WHERE TransType IN (0) AND FNAME + ' ' + MI + '. ' + LNAME LIKE 'Tuan' AND LNAME + ', ' + FNAME + ' ' + MI + '.' LIKE 'Vuong' AND (Dttm>={ts '2012-12-03 00:00:00'} AND Dttm<{ts '2012-12-06 00:00:00'}) AND (CONVERT(DATETIME, CONVERT(VARCHAR, Dttm, 1))={ts '2012-11-26 00:00:00'}) AND FiscalQuarter=3; END: CREATE TABLE permission denied in database 'DUED'.
at com.lucent.vital.vq.report.AggrDataType.getSQLStatement(AggrDataType.java:624)
at com.lucent.vital.vq.report.exec.SectionExec.getMSSQLSelect(SectionExec.java:1854)
at com.lucent.vital.vq.report.exec.SectionExec.getSQLSelect(SectionExec.java:1826)
at com.lucent.vital.vq.report.exec.SectionExec.getSQLSelect(SectionExec.java:1795)
at DUED32Test322_1354677058519_410226.evaluate(DUED32Test322_1354677058519_410226:449)
at net.sf.jasperreports.engine.fill.JREvaluator.evaluate(JREvaluator.java:182)
... 17 more
Caused by: java.sql.SQLException: CREATE TABLE permission denied in database 'DUED'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:723)
at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1160)
at com.lucent.vital.vq.report.AggrDataType.getSQLStatement(AggrDataType.java:620)
... 22 more
12/05/2012 10:10:58 [iNFO] Finalized trigger: Trigger 'vsreports.jobid=40':  triggerClass: 'org.quartz.SimpleTrigger isVolatile: false calendar: 'null' misfireInstruction: 1 nextFireTime: null
12/05/2012 10:11:09 [iNFO] Cron schedule upgrade job triggered at Wed Dec 05 10:11:09 ICT 2012
12/05/2012 10:14:36 [iNFO] Scheduler Quartz Scheduler_$_VitalSuite shutting down.
12/05/2012 10:14:36 [iNFO] Scheduler Quartz Scheduler_$_VitalSuite paused.
12/05/2012 10:14:36 [WARN] Database connection shutdown unsuccessful.
java.sql.SQLException: There is no DataSource named 'vnmaster'
at org.quartz.utils.DBConnectionManager.shutdown(DBConnectionManager.java:136)
at org.quartz.impl.jdbcjobstore.JobStoreSupport.shutdown(JobStoreSupport.java:651)
at org.quartz.core.QuartzScheduler.shutdown(QuartzScheduler.java:610)
at org.quartz.core.QuartzScheduler.shutdown(QuartzScheduler.java:556)
at org.quartz.impl.StdScheduler.shutdown(StdScheduler.java:198)
at com.lucent.vital.scheduler.VSQuartzScheduler.stop(VSQuartzScheduler.java:258)
at com.lucent.vital.scheduler.VSQuartzScheduler.main(VSQuartzScheduler.java:385)
12/05/2012 10:14:36 [iNFO] Scheduler shutdown
12/05/2012 10:14:36 [iNFO] Scheduler un-bound from name 'Quartz Scheduler_$_VitalSuite' in RMI registry
12/05/2012 10:14:36 [iNFO] Scheduler Quartz Scheduler_$_VitalSuite shutdown complete.
12/05/2012 10:14:43 [iNFO] Scheduler bound to RMI registry under name 'Quartz Scheduler_$_VitalSuite'
12/05/2012 10:14:43 [iNFO] Initialized Scheduler Signaller of type: class org.quartz.core.SchedulerSignalerImpl
12/05/2012 10:14:43 [iNFO] Quartz Scheduler v.1.7.3 created.
12/05/2012 10:14:43 [iNFO] JobFactory set to: com.lucent.vital.scheduler.VSScheduleJobFactory@12452e8
12/05/2012 10:14:43 [iNFO] New VSSchedulerListener created for Quartz Scheduler
12/05/2012 10:14:43 [iNFO] Connecting to vnmaster database on VSDEV-94-36
12/05/2012 10:14:45 [iNFO] Job execution threads will use class loader of thread: main
12/05/2012 10:14:45 [iNFO] Connecting to vnmaster database on VSDEV-94-36
12/05/2012 10:14:45 [iNFO] Enabling jobs for role VNMaster
12/05/2012 10:14:45 [iNFO] Disabling jobs for role VAMaster
12/05/2012 10:14:45 [iNFO] Disabling jobs for role VHMaster
12/05/2012 10:14:45 [iNFO] Disabling jobs for role VNReptServer
12/05/2012 10:14:45 [iNFO] Disabling jobs for role VAReptServer
12/05/2012 10:14:45 [iNFO] Disabling jobs for role VHReptServer
12/05/2012 10:14:45 [iNFO] Enabling jobs for role VNAggr
12/05/2012 10:14:45 [iNFO] Disabling jobs for role VAAggr
12/05/2012 10:14:45 [iNFO] Disabling jobs for role VHPost
12/05/2012 10:14:45 [iNFO] Enabling jobs for role VNPoller
12/05/2012 10:14:45 [iNFO] Disabling jobs for role VAHConfig
12/05/2012 10:14:45 [iNFO] Disabling jobs for role VAPost
12/05/2012 10:14:45 [iNFO] Enabling jobs for role VARTMaster
12/05/2012 10:14:45 [iNFO] JDBCJobStore threads will inherit ContextClassLoader of thread: main
12/05/2012 10:14:45 [iNFO] Using thread monitor-based data access locking (synchronization).
12/05/2012 10:14:45 [iNFO] Removed 1 Volatile Trigger(s).
12/05/2012 10:14:45 [iNFO] Removed 1 Volatile Job(s).
12/05/2012 10:14:45 [iNFO] class com.lucent.vital.scheduler.VSJDBCJobStore initialized.
12/05/2012 10:14:45 [iNFO] Quartz scheduler 'Quartz Scheduler' initialized from default resource file in Quartz package: 'quartz.properties'
12/05/2012 10:14:45 [iNFO] Quartz scheduler version: 1.7.3
12/05/2012 10:14:45 [iNFO] Freed 0 triggers from 'acquired' / 'blocked' state.
12/05/2012 10:14:45 [iNFO] Recovering 0 jobs that were in-progress at the time of the last shut-down.
12/05/2012 10:14:45 [iNFO] Recovery complete.
12/05/2012 10:14:45 [iNFO] Removed 0 'complete' triggers.
12/05/2012 10:14:45 [iNFO] Removed 0 stale fired job entries.
12/05/2012 10:14:45 [iNFO] Monitoring cron file D:PROGRA~2VSCommonprogramcron.txt
12/05/2012 10:14:45 [iNFO] Added trigger: Trigger 'VSCronManagement.cronReader':  triggerClass: 'org.quartz.SimpleTrigger isVolatile: true calendar: 'null' misfireInstruction: 0 nextFireTime: Wed Dec 05 10:14:45 ICT 2012
12/05/2012 10:14:45 [iNFO] Scheduler Quartz Scheduler_$_VitalSuite started.
12/05/2012 10:14:46 [iNFO] Cron schedule upgrade job triggered at Wed Dec 05 10:14:46 ICT 2012
12/05/2012 10:15:43 [iNFO] Added trigger: Trigger 'vsreports.jobid=41':  triggerClass: 'org.quartz.SimpleTrigger isVolatile: false calendar: 'null' misfireInstruction: 1 nextFireTime: Wed Dec 05 10:15:43 ICT 2012
12/05/2012 10:15:48 [iNFO] Job vsreports.jobid=41 threw a JobExecutionException:
org.quartz.JobExecutionException: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression :
Source text : $P{Test}.getArtSection().getSQLSelect($V{Test_WHERE},$V{1ExecutionTime}) [see nested exception: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression :
Source text : $P{Test}.getArtSection().getSQLSelect($V{Test_WHERE},$V{1ExecutionTime})]
at com.lucent.vital.vq.reportserver.quartz.QuartzReportJob.execute(QuartzReportJob.java:122)
at org.quartz.core.JobRunShell.run(JobRunShell.java:199)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)
Caused by: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression :
Source text : $P{Test}.getArtSection().getSQLSelect($V{Test_WHERE},$V{1ExecutionTime})
at net.sf.jasperreports.engine.fill.JREvaluator.evaluate(JREvaluator.java:195)
at net.sf.jasperreports.engine.fill.JRCalculator.evaluate(JRCalculator.java:589)
at net.sf.jasperreports.engine.fill.JRCalculator.evaluate(JRCalculator.java:557)
at net.sf.jasperreports.engine.fill.JRFillSubreport.getParameterValues(JRFillSubreport.java:552)
at net.sf.jasperreports.engine.fill.JRFillSubreport.getParameterValues(JRFillSubreport.java:460)
at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluateParameterValues(JRFillSubreport.java:359)
at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluateSubreport(JRFillSubreport.java:339)
at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluate(JRFillSubreport.java:263)
at net.sf.jasperreports.engine.fill.JRFillElementContainer.evaluate(JRFillElementContainer.java:258)
at net.sf.jasperreports.engine.fill.JRFillBand.evaluate(JRFillBand.java:499)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillColumnBand(JRVerticalFiller.java:2026)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillDetail(JRVerticalFiller.java:757)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportStart(JRVerticalFiller.java:269)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:127)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:942)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:860)
at net.sf.jasperreports.engine.fill.AsynchronousFillHandle$ReportFiller.run(AsynchronousFillHandle.java:148)
at java.lang.Thread.run(Thread.java:662)
Caused by: com.lucent.vital.vq.exceptions.vqSQLException: BEGIN CREATE TABLE VART_TMP_1354677346364(Available FLOAT,Color VARCHAR(20),DateTime DATETIME,FullNamebyFirstName VARCHAR(100),FullNamebyLastName VARCHAR(100),ItemCode VARCHAR(60),ItemDescription VARCHAR(1024),Make VARCHAR(80),Model VARCHAR(80),PaymentType VARCHAR(20), VARCHAR(10),Total FLOAT,UnitCost FLOAT,UnitPrice FLOAT);INSERT INTO VART_TMP_1354677346364 SELECT Avail AS Available,Color AS Color,null AS DateTime,null AS FullNamebyFirstName,null AS FullNamebyLastName,Code AS ItemCode,Description AS ItemDescription,Make AS Make,Model AS Model,null AS PaymentType, AS ,Total AS Total,Cost AS UnitCost,Price AS UnitPrice FROM INV_TABLE;INSERT INTO VART_TMP_1354677346364 SELECT null AS Available,Color AS Color,Dttm AS DateTime,FNAME + ' ' + MI + '. ' + LNAME AS FullNamebyFirstName,LNAME + ', ' + FNAME + ' ' + MI + '.' AS FullNamebyLastName,Code AS ItemCode,Description AS ItemDescription,Make AS Make,Model AS Model,BillingType AS PaymentType, AS ,null AS Total,Cost AS UnitCost,Price AS UnitPrice FROM TRANS_BILLING_VIEW WHERE TransType IN (0) AND FNAME + ' ' + MI + '. ' + LNAME LIKE 'Tuan' AND LNAME + ', ' + FNAME + ' ' + MI + '.' LIKE 'Vuong' AND (Dttm>={ts '2012-12-03 00:00:00'} AND Dttm<{ts '2012-12-06 00:00:00'}) AND (CONVERT(DATETIME, CONVERT(VARCHAR, Dttm, 1))={ts '2012-11-26 00:00:00'}) AND FiscalQuarter=3; END: CREATE TABLE permission denied in database 'DUED'.
at com.lucent.vital.vq.report.AggrDataType.getSQLStatement(AggrDataType.java:624)
at com.lucent.vital.vq.report.exec.SectionExec.getMSSQLSelect(SectionExec.java:1854)
at com.lucent.vital.vq.report.exec.SectionExec.getSQLSelect(SectionExec.java:1826)
at com.lucent.vital.vq.report.exec.SectionExec.getSQLSelect(SectionExec.java:1795)
at DUED32Test322_1354677347555_324703.evaluate(DUED32Test322_1354677347555_324703:449)
at net.sf.jasperreports.engine.fill.JREvaluator.evaluate(JREvaluator.java:182)
... 17 more
Caused by: java.sql.SQLException: CREATE TABLE permission denied in database 'DUED'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:723)
at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1160)
at com.lucent.vital.vq.report.AggrDataType.getSQLStatement(AggrDataType.java:620)
... 22 more
12/05/2012 10:15:48 [iNFO] Finalized trigger: Trigger 'vsreports.jobid=41':  triggerClass: 'org.quartz.SimpleTrigger isVolatile: false calendar: 'null' misfireInstruction: 1 nextFireTime: null

[szaharia]

Well, things are clarifying now: all these troubles are coming from the database itself, because of permission settings. Apparently your SQL script attempts to create and populate a table, but table creation in database 'DUED' is forbidden for that user, so an error is thrown instead.

Therefore, take a look at DB permissions first.

Hope this helps,

sanda

[tuanfgy]

thanks Sanda for your support. The problems are solve. It comes from permission of DB.