Error executing SQL statement

Posted on November 29, 2013 at 3:48am
1
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's picture
tcenes
16
Joined: Nov 29 2013 - 1:34am
Last seen: 5 years 5 months ago

10 Answers:

0
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's picture
Boby B Jacob
20
Joined: Oct 10 2012 - 4:42am
Last seen: 3 years 11 months ago
0
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's picture
tcenes
16
Joined: Nov 29 2013 - 1:34am
Last seen: 5 years 5 months ago
-1
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's picture
Ankur Gupta
1064
Joined: Jan 21 2013 - 10:36pm
Last seen: 12 months 3 days ago

already did

tcenes - 5 years 5 months ago
add comment
0
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's picture
Ankur Gupta
1064
Joined: Jan 21 2013 - 10:36pm
Last seen: 12 months 3 days ago
0
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's picture
tcenes
16
Joined: Nov 29 2013 - 1:34am
Last seen: 5 years 5 months ago
0
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's picture
Sukanya Parisa
90
Joined: Dec 5 2013 - 10:29pm
Last seen: 2 years 2 months ago
-1
Posted on December 6, 2013 at 6:16am

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

tcenes's picture
tcenes
16
Joined: Nov 29 2013 - 1:34am
Last seen: 5 years 5 months ago
0
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's picture
sentoul
18
Joined: Jun 21 2016 - 10:55am
Last seen: 2 years 10 months ago
1
Posted on March 22, 2017 at 5:27am

for input parameter, use $P!{input_name}

dean871025's picture
dean871025
3
Joined: Nov 11 2016 - 12:44am
Last seen: 1 year 4 months ago
0
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's picture
john.orr
2
Joined: Jun 5 2018 - 5:59pm
Last seen: 11 months 2 weeks ago
Feedback
randomness