can not identify error in strored procedure code?

[zulqarnainjalil]

my stored procedure code work well in mysql terminal but it is not work in jasper report (.jrxml file). it gives error in jrxml file.

my code is

DELIMITER // DROP PROCEDURE IF EXISTS try// [/code]

CREATE PROCEDURE try() BEGIN declare s_query varchar(255); [/code]

declare done bool default false; [/code]

declare c_queries cursor for SELECT `Demo`.`Qcol` FROM `Demo`.`QC`; [/code]

declare continue handler for not found set done = true; open c_queries; [/code]

read_loop: loop fetch c_queries into s_query;[/code]

 if done then leave read_loop; end if;[/code]

 -- run the query set @sql = s_query;[/code]

 prepare stmt from @sql; execute stmt;[/code]

 deallocate prepare stmt; end loop; END // DELIMITER ; CALL try();[/code]

 

it gives error like :

net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: 123. at com.jaspersoft.studio.editor.preview.view.control.ReportControler.fillReport(ReportControler.java:548) at com.jaspersoft.studio.editor.preview.view.control.ReportControler.access$20(ReportControler.java:523) at com.jaspersoft.studio.editor.preview.view.control.ReportControler$5.run(ReportControler.java:404) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:55) Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: 123. at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:344) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1245) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:723) at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:438) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:550) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:123) at java.lang.Thread.run(Thread.java:745) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'DELIMITER // DROP PROCEDURE IF EXISTS try// CREATE PROCEDURE try() BEGIN decla' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at com.mysql.jdbc.Util.getInstance(Util.java:408) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2501) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1966) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:310) ... 6 more

[butlerc]

I suspect you need to disable SQL Security validation otherwise the server expects your query to start with the keyword SELECT.

 

1.Edit the file /WEB-INF/classes/esapi/security-config.properties

2.Locate the default settings:

security.validation.sql.on=true

3.Set these properties to true or false as desired. True means the security feature is turned ON. False means the security feature is turned OFF.