Error executing SQL statement

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.

 

tcenes's picture
16
Joined: Nov 29 2013 - 1:34am
Last seen: 5 years 5 months ago

10 Answers:

0

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

Boby B Jacob's picture
Joined: Oct 10 2012 - 4:42am
Last seen: 3 years 11 months ago
0

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
16
Joined: Nov 29 2013 - 1:34am
Last seen: 5 years 5 months ago
-1

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
Joined: Jan 21 2013 - 10:36pm
Last seen: 12 months 3 days ago

already did

tcenes - 5 years 5 months ago
0

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
Joined: Jan 21 2013 - 10:36pm
Last seen: 12 months 3 days ago
0

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
16
Joined: Nov 29 2013 - 1:34am
Last seen: 5 years 5 months ago
0

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
Joined: Dec 5 2013 - 10:29pm
Last seen: 2 years 2 months ago
-1

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

tcenes's picture
16
Joined: Nov 29 2013 - 1:34am
Last seen: 5 years 5 months ago
0

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
18
Joined: Jun 21 2016 - 10:55am
Last seen: 2 years 10 months ago
1

for input parameter, use $P!{input_name}

dean871025's picture
Joined: Nov 11 2016 - 12:44am
Last seen: 1 year 4 months ago
0

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
Joined: Jun 5 2018 - 5:59pm
Last seen: 11 months 2 weeks ago
Feedback
randomness