Error executing SQL statement

Posted on November 29, 2013 at 3:48am

Hallo,

always if i try to fill a report i get the "Error executing SQL statement" Message and i cant understand why.

net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : Reportname

at com.jaspersoft.studio.editor.preview.view.control.ReportControler.fillReport(ReportControler.java:482)

at com.jaspersoft.studio.editor.preview.view.control.ReportControler.access$18(ReportControler.java:457)

at com.jaspersoft.studio.editor.preview.view.control.ReportControler$4.run(ReportControler.java:347)

at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)

Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : faktura

at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:240)

at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1112)

at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:689)

at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1281)

at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:900)

at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:120)

at java.lang.Thread.run(Unknown Source)

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 '[Zahlungsart],

einzelaufstellung.Anzahl as [Anzahl der Aufträge],

einzelaufstel' at line 8

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)

at java.lang.reflect.Constructor.newInstance(Unknown Source)

at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)

at com.mysql.jdbc.Util.getInstance(Util.java:386)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2825)

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2156)

at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2323)

at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:233)

... 6 more

Thanks for your Help.

iReport Designer

Jaspersoft® Studio

tcenes

234

Joined: Nov 29 2013 - 1:34am

Last seen: 8 years 8 months ago

11 Answers:

Posted on November 29, 2013 at 4:43am

Going through the error description, it seems there is a syntactical error in your SQL code. Please check

Boby B Jacob

114

Joined: Oct 10 2012 - 4:42am

Last seen: 7 years 2 months ago

Posted on November 29, 2013 at 4:57am

Hmm i'm not sure what can be wrong.

SELECT CASE 
            WHEN einzelaufstellung.Zahlungsart = 'banktransfer' THEN 'Lastschrift'     
            WHEN einzelaufstellung.Zahlungsart = 'eustandardtransfer' THEN 'Vorkasse'     
            WHEN einzelaufstellung.Zahlungsart = 'invoice' THEN 'Rechnung'     
            WHEN einzelaufstellung.Zahlungsart = 'paypal' THEN 'Paypal'     
            WHEN einzelaufstellung.Zahlungsart = 'paypalexpress' THEN 'Paypalexpress'
        END AS [Zahlungsart],
einzelaufstellung.Anzahl AS [Anzahl der Aufträge],
einzelaufstellung.Anzahl/gesamtzahl.Anzahl*100 AS [Anteil der Aufträge IN %],
einzelaufstellung.Umsatz AS [Umsatz IN EUR],
einzelaufstellung.Umsatz/gesamtzahl.Umsatz*100 AS [Anteil des Umsatzes IN %]
FROM
( SELECT '1' AS [joinid],
         o.payment_method AS [Zahlungsart],
         COUNT(o.orders_id) AS [Anzahl],
         SUM(ot.value) AS [Umsatz]
  FROM orders AS o
  LEFT OUTER JOIN orders_total AS ot
  ON o.orders_id = ot.orders_id AND class = "ot_total"
  LEFT OUTER JOIN customers AS c 
  ON o.customers_id=c.customers_id
  GROUP BY o.payment_method
) AS einzelaufstellung
LEFT OUTER JOIN
( SELECT '1' AS [joinid],
         COUNT(o.orders_id) AS [Anzahl],
         SUM(ot.value) AS [Umsatz]
  FROM orders AS o  
  LEFT OUTER JOIN orders_total AS ot
  ON o.orders_id = ot.orders_id AND class = "ot_total"
) AS gesamtzahl
  ON einzelaufstellung.joinid = gesamtzahl.joinid
ORDER BY [Zahlungsart] ASC

tcenes

234

Joined: Nov 29 2013 - 1:34am

Last seen: 8 years 8 months ago

Posted on November 29, 2013 at 5:43am

Hi,

The error stack trace clearly demarcates that there is problem in SQL --

"Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; "

Please send the query so that could have a look and resolve you issue.

Thanks,

Ankur Gupta

Ankur Gupta

5620

Joined: Jan 21 2013 - 10:36pm

Last seen: 2 weeks 4 days ago

already did

tcenes - 8 years 8 months ago

add comment

Posted on November 29, 2013 at 12:51pm

Hi, I think the problem is in the CASE-WHEN: The default syntax is -

CASE case-expression
WHEN when-expression-1 THEN value-1
[ WHEN when-expression-n THEN value-n ... ]
[ ELSE else-value ]
END

In your query you have missed the else value, which I think is giving error try changing the last When to else that might help like

CASE WHEN einzelaufstellung.Zahlungsart = 'banktransfer' THEN 'Lastschrift'
WHEN einzelaufstellung.Zahlungsart = 'eustandardtransfer' THEN 'Vorkasse'
WHEN einzelaufstellung.Zahlungsart = 'invoice' THEN 'Rechnung'
WHEN einzelaufstellung.Zahlungsart = 'paypal' THEN 'Paypal'
ELSE 'Paypalexpress'
END AS [Zahlungsart]

Please let me know if it helps.

Thanks,
Ankur Gupta

Ankur Gupta

5620

Joined: Jan 21 2013 - 10:36pm

Last seen: 2 weeks 4 days ago

Posted on December 2, 2013 at 12:03am

Hi,

i put in the Else-Value but theres always the error message again.

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 '[Zahlungsart],

einzelaufstellung.Anzahl as [Anzahl der Aufträge],

einzelaufst' at line 7

tcenes

234

Joined: Nov 29 2013 - 1:34am

Last seen: 8 years 8 months ago

Posted on December 6, 2013 at 12:45am

Hi,I think the problem is with allias names of the columns, Give the allias names with in double quotes and check if it not works then check the right syntax to give allias names to the columns.

Sukanya Parisa

398

Joined: Dec 5 2013 - 10:29pm

Last seen: 5 years 5 months ago

Posted on December 6, 2013 at 6:16am

Got it, the "names" dont need any kind of '', "" or []. Just the Strings need 'STRING'.

tcenes

234

Joined: Nov 29 2013 - 1:34am

Last seen: 8 years 8 months ago

Posted on June 28, 2016 at 10:17am

I met with this problem and the SQL actually ran OK when run in directly on MySQL.

In my case, the problem solved completely by selecing "All" instead of the default option "First 100 records" in the Preview Data section (bottom section in the Report query window).

sentoul

132

Joined: Jun 21 2016 - 10:55am

Last seen: 6 years 1 month ago

Posted on March 22, 2017 at 5:27am

for input parameter, use $P!{input_name}

dean871025

Joined: Nov 11 2016 - 12:44am

Last seen: 5 months 3 weeks ago

Posted on June 5, 2018 at 6:07pm

Thanks sentoul - I was pretty confused but my case was the same as yours, my query was fine - and selecting "All" saved the day.

john.orr

120

Joined: Jun 5 2018 - 5:59pm

Last seen: 4 years 2 months ago

Posted on October 4, 2020 at 4:47am

Wonderfull, it's so easy et simple... just "all" instead of "100 records", but i will never find this solution by myself.

Because in Netbeans (with plugin) it works perfectly with "100 records" but not in Jaspersoft ireport 5.6.0 !

Tanks a lot !

ag33

Joined: Aug 21 2020 - 8:53am

Last seen: 1 year 10 months ago

Main menu

Error executing SQL statement

11 Answers:

Tags

Main menu

You are here

Error executing SQL statement

11 Answers:

Tags