Jasper Report Studio - SQL Query Error

Classic Servoy
1

Servoy Version: 6.0.10 - build 1240
Jaspersoft Studio 5.6.2.final
Jasper Plug-In 5.0.0 a2
JRE build 1.7.0_71-b14
Sybase SQL 11

Here’s another Jasper Report oddity. I run the same SQL query in Sybase’s Interactive SQL and Servoy’s SQL Explorer with no problem (see first two graphics below). But, when I run the same SQL query in Jasper Studio, an error message is emitted stating that the PK column (order_id) is unknown even though it’s not part of the query (see the 3rd graphic below). If you look closely at Studio’s Data Set and Query dialog graphic (the 3rd one), you will see that order_id field is known (indicated by red arrow).

Is anyone experiencing problems with Jasper Studio? Am I doing something wrong? Is this a bug? Help!

[attachment=2]2014-12-20_1.png[/attachment]

[attachment=1]2014-12-20_sql_explorer.png[/attachment]

[attachment=0]2014-12-20_studio_query.png[/attachment]

The error message:

Start Report Execution
Compiling report…DONE
Setting Data Adapter…DONE
Setting Virtualizer…DONE
Filling Report…ERROR
net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Unknown column name : order_id
at com.jaspersoft.studio.editor.preview.view.control.ReportControler.fillReport(ReportControler.java:467)
at com.jaspersoft.studio.editor.preview.view.control.ReportControler.access$18(ReportControler.java:442)
at com.jaspersoft.studio.editor.preview.view.control.ReportControler$4.run(ReportControler.java:334)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)
Caused by: net.sf.jasperreports.engine.JRException: Unknown column name : order_id
at net.sf.jasperreports.engine.JRResultSetDataSource.getColumnIndex(JRResultSetDataSource.java:409)
at net.sf.jasperreports.engine.JRResultSetDataSource.getFieldValue(JRResultSetDataSource.java:139)
at net.sf.jasperreports.engine.fill.JRFillDataset.setOldValues(JRFillDataset.java:1358)
at net.sf.jasperreports.engine.fill.JRFillDataset.next(JRFillDataset.java:1259)
at net.sf.jasperreports.engine.fill.JRFillDataset.next(JRFillDataset.java:1235)
at net.sf.jasperreports.engine.fill.JRBaseFiller.next(JRBaseFiller.java:1614)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:150)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:963)
at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:120)
at java.lang.Thread.run(Unknown Source)

Report Execution Finished.

2014-12-20_studio_query.png
2014-12-20_studio_query.png1188×768 64.5 KB

2014-12-20_sql_explorer.png
2014-12-20_sql_explorer.png1168×688 27.9 KB

2014-12-20_1.png
2014-12-20_1.png958×609 34.9 KB

2

It’s obvious that Jasper doesn’t know the column, since you don’t ask for it. The question is: where is it used. I guess the best approach is to dive into the report’s XML and look for any reference to that column.

3

Hi, Patrick…and, thank you for your reply!

Before creating this post, I did search for order_id in the XML, and it appears only once, just after the SQL query where all the table fields are listed (note that it is also the first ‘unknown’ field):

	<queryString language="SQL">
		<![CDATA[SELECT order_animal_species, order_animal_pain_level, sum(order_animal_quantity_received), count(*) FROM orders
GROUP BY order_animal_species, order_animal_pain_level
ORDER BY order_animal_species ASC, order_animal_pain_level ASC]]>
	</queryString>
	<field name="order_id" class="java.lang.Integer"/>
	<field name="order_number" class="java.lang.String"/>
	<field name="order_segment" class="java.lang.Integer"/>
	<field name="order_date" class="java.sql.Timestamp"/>
        ...

A simple query such as:

SELECT * FROM orders

works fine. But, as soon as I add the aggregate sum() and GROUP BY clause, the error message is emitted stating that the next field (in the list of XML fields) is unknown:

SELECT order_animal_species, sum(order_animal_quantity_received) FROM orders
GROUP BY order_animal_species
ORDER BY order_animal_species ASC

If I add the unknown field to the GROUP BY clause, then the next field in the XML list is flagged as unknown.

4

And what happens if you simply remove the fields that you don’t want and you don’t query for from the XML (or the field list in the designer)? It sounds a bit as if these fields are added when you test the “select *” and not removed when you change the query later.

5

The same thing happens with only two fields defined in the report’s:

	<queryString language="SQL">
		<![CDATA[SELECT order_animal_species, sum(order_animal_quantity_received)
FROM orders
GROUP BY order_animal_species
ORDER BY order_animal_species ASC]]>
	</queryString>
	<field name="order_animal_species" class="java.lang.String"/>
	<field name="order_animal_quantity_received" class="java.lang.Integer"/>

causes the following error message to be omitted:

net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Unknown column name : order_animal_quantity_received