iReport JRValidationException field not found

Question

I'm trying to make my first steps with JasperReports and iReport. Recently I solved a problem with Database Connection in this post. After that I created another successful connection to a MySQL DB just to reinforce the knowledge; I made a query but now I'm getting another error related -I suppose- to the query itself or something like that.

The situation

When I try to create a Report query the fields (table columns) are retrieved and it's type is recognized correctly but the data (rows) gives me an error with the message

Error: net.sf.jasperreports.engine.JRException: Error excecuting SQL statement for: data

The error is shown in the screenshot below:

Then, when I try to preview the report with the fields of the query I get the following error:

net.sf.jasperreports.engine.design.JRValidationException: Report design not valid : 
    1. Field not found : idaddress
    2. Field not found : city
  at net.sf.jasperreports.engine.design.JRAbstractCompiler.verifyDesign(JRAbstractCompiler.java:271)
  at net.sf.jasperreports.engine.design.JRAbstractCompiler.compileReport(JRAbstractCompiler.java:153)
  at net.sf.jasperreports.engine.JasperCompileManager.compile(JasperCompileManager.java:354)
  at net.sf.jasperreports.engine.JasperCompileManager.compileToFile(JasperCompileManager.java:270)
  at net.sf.jasperreports.engine.JasperCompileManager.compileReportToFile(JasperCompileManager.java:563)
  at com.jaspersoft.ireport.designer.compiler.IReportCompiler.run(IReportCompiler.java:528)
  at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:572)
  at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:997)

In the jrxml file the fields are defined properly as I saw in this other post as follow:

<subDataset name="MyDataset" uuid="b9d7b569-d19e-451e-8a15-c558918e1698">
    <queryString language="SQL">
        <![CDATA[SELECT
 address.`idaddress` AS address_idaddress,
 address.`city` AS address_city
 FROM
 `address` address]]>
    </queryString>
    <field name="address_idaddress" class="java.lang.Integer"/>
    <field name="address_city" class="java.lang.String"/>
</subDataset>

The project

The complete jrxml file is this:

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="PrimerReporte" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="eb8d4459-c8dc-4c96-bc35-ac988007e32a">
    <property name="ireport.zoom" value="1.0"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <style name="table">
        <box>
            <pen lineWidth="1.0" lineColor="#000000"/>
        </box>
    </style>
    <style name="table_TH" mode="Opaque" backcolor="#A52A2A">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="table_CH" mode="Opaque" backcolor="#FFBFBF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="table_TD" mode="Opaque" backcolor="#FFFFFF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
        </box>
        <conditionalStyle>
            <conditionExpression><![CDATA[new Boolean($V{REPORT_COUNT}.intValue()%2==0)]]></conditionExpression>
            <style backcolor="#FFEFEF"/>
        </conditionalStyle>
    </style>
    <subDataset name="MyDataset" uuid="b9d7b569-d19e-451e-8a15-c558918e1698">
        <queryString language="SQL">
            <![CDATA[SELECT
     address.`idaddress` AS address_idaddress,
     address.`city` AS address_city
FROM
     `address` address]]>
        </queryString>
        <field name="address_idaddress" class="java.lang.Integer"/>
        <field name="address_city" class="java.lang.String"/>
    </subDataset>
    <parameter name="idaddress" class="java.lang.Integer">
        <defaultValueExpression><![CDATA[]]></defaultValueExpression>
    </parameter>
    <parameter name="city" class="java.lang.String">
        <defaultValueExpression><![CDATA[]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[SELECT
     address.`idaddress` AS address_idaddress,
     address.`city` AS address_city
FROM
     `address` address]]>
    </queryString>
    <field name="address_idaddress" class="java.lang.Integer"/>
    <field name="address_city" class="java.lang.String"/>
    <filterExpression><![CDATA[$F{idaddress}]]></filterExpression>
    <group name="city">
        <groupExpression><![CDATA[$F{city}]]></groupExpression>
    </group>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="79" splitType="Stretch">
            <textField>
                <reportElement x="121" y="0" width="313" height="49" uuid="e3c5e2b7-14a4-4e26-a90c-ae4702f4ac65"/>
                <textElement>
                    <font size="36"/>
                </textElement>
                <textFieldExpression><![CDATA[Mi primer reporte]]></textFieldExpression>
            </textField>
        </band>
    </title>
    <pageHeader>
        <band height="35" splitType="Stretch">
            <staticText>
                <reportElement x="22" y="0" width="510" height="35" uuid="7d375361-1b1d-4a7b-8788-9dd432b86c43"/>
                <textElement>
                    <font size="18"/>
                </textElement>
                <text><![CDATA[Este es un reporte de prueba para aprender a usar iReport]]></text>
            </staticText>
        </band>
    </pageHeader>
    <columnHeader>
        <band height="61" splitType="Stretch"/>
    </columnHeader>
    <detail>
        <band height="125" splitType="Stretch">
            <componentElement>
                <reportElement key="table" style="table" x="0" y="0" width="401" height="80" uuid="8c775bc1-4076-44c4-a7af-1fc405283908"/>
                <jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">
                    <datasetRun subDataset="MyDataset" uuid="12ca7d6a-d625-42ab-bff9-57937d50f096">
                        <dataSourceExpression><![CDATA[new net.sf.jasperreports.engine.JREmptyDataSource(1)]]></dataSourceExpression>
                    </datasetRun>
                    <jr:column width="200" uuid="37b69ab0-be68-4dda-8739-08860a5dd8c7">
                        <jr:tableHeader style="table_TH" height="30" rowSpan="1"/>
                        <jr:columnHeader style="table_CH" height="30" rowSpan="1">
                            <textField>
                                <reportElement x="0" y="0" width="200" height="30" uuid="91dcc02e-4f54-446e-9092-d8e8fef6bc78"/>
                                <textFieldExpression><![CDATA[$F{address_idaddress}]]></textFieldExpression>
                            </textField>
                        </jr:columnHeader>
                        <jr:detailCell style="table_TD" height="20" rowSpan="1"/>
                    </jr:column>
                    <jr:column width="200" uuid="36dc21c0-a551-4df8-a180-95cf5b9cfc0c">
                        <jr:tableHeader style="table_TH" height="30" rowSpan="1"/>
                        <jr:columnHeader style="table_CH" height="30" rowSpan="1">
                            <textField>
                                <reportElement x="0" y="0" width="200" height="30" uuid="294719c1-d37b-4b53-ab0e-2045397c0d60"/>
                                <textFieldExpression><![CDATA[$F{address_city}]]></textFieldExpression>
                            </textField>
                        </jr:columnHeader>
                        <jr:detailCell style="table_TD" height="20" rowSpan="1"/>
                    </jr:column>
                </jr:table>
            </componentElement>
        </band>
    </detail>
    <columnFooter>
        <band height="45" splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band height="54" splitType="Stretch"/>
    </pageFooter>
    <summary>
        <band height="42" splitType="Stretch"/>
    </summary>
</jasperReport>

And the connection's config is this:

Note: Yes, the server has no password, it's just a testing environment...

Some attempts

I tried the other answers in the same post but none of them work for me. Last I tried changing the connection URL adding port and user but the error remains the same.

Don't know why below the <subDataset>element I have the following XML:

<queryString>
    <![CDATA[SELECT
 *
 FROM
 `address` address
 ORDER BY
 city ASC]]>
</queryString>
<field name="idaddress" class="java.lang.Integer">
    <fieldDescription><![CDATA[Address table ID]]></fieldDescription>
</field>
<field name="city" class="java.lang.String">
    <fieldDescription><![CDATA[The city]]></fieldDescription>
</field>
<group name="city">
    <groupExpression><![CDATA[$F{city}]]></groupExpression>
</group>

Looks the same as the <subDataset> but it's there. I didn't wanted to remove as this is generated by iReport, so I assume it's correct.

So the question is: what am I missing here? What can I try to solve this? where can I look for some clue?

Thanks in advance for your answers.

Error... for *data*, data is probably the report name, you should try to get the whole stacktrace down to the exeception of the query and/or connection. — Petter Friberg, May 08 '17 at 07:47
Well, I get an `net.sf.jasperreports.engine.design.JRValidationException` with a message about the fields of the query after the `net.sf.jasperreports.engine.JRException`, doesn't that means something? how can I get the whole stacktrace for the `JRException`? In the iReport console I only have the `JRValidationException`'s stacktrace, which is updated in the post — Alvaro Pedraza, May 08 '17 at 11:05
You have 2 different problems, my first comment was related to first problem. **Problem 1** " Error executing SQL statement", This is iReport that can't execute the query, it may be related to driver, it's trying to do resultSet.getInt("idaddress") and resultSet.getString("city"), for some reason it can not execute this, you need to check the iReport log. — Petter Friberg, May 08 '17 at 12:36
**Problem 2**, you have a subdataset, (probably table component or similar), when using this subdataset you are trying to reference the main datasource fields (idaddress,city) but in your subdataset they are called address_idaddress,address_city), the jrxml you see under the subdataset probably is the main dataset. — Petter Friberg, May 08 '17 at 12:38
In general this is difficult to answer since we can't see your whole jrxml (how you reference thingzz) and we do not have complete stack trace related to your error while trying to execute the query. Above are by best guess, I can't do better then that without an [mcve], maybe you would need to split question in 2 as well. Note I don't think they are related, just 2 different problems. — Petter Friberg, May 08 '17 at 12:41
@PetterFriberg, I added detailed information to give a complete example. For now I'll keep the post as it is but I'll consider splitting it as you suggest. Maybe solving the first problem the second will be also. — Alvaro Pedraza, May 08 '17 at 13:55

score 1 · Accepted Answer · edited Sep 27 '17 at 14:23

You have some confusion about how Jasper Report works, the difference between main datasource and a subDataset and when using them you need to reference the fields related to the datasource that you are using.

To clear some confusion:

The main datasource will execute and jasper report will iterate (repeat) the detail band for each record.
The subDatset is another datasource that you can pass to a component.

The solution to your report is to either use only the main query (delete the jr:table component and its subdataset) or if you like to use the jr:table, have a main datasource that returns only 1 record and then pass the subdatasource and the connection to the jr:table component

Example using textField's in the detail band

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="PrimerReporte" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="eb8d4459-c8dc-4c96-bc35-ac988007e32a">
    <queryString>
        <![CDATA[SELECT
     address.`idaddress` AS address_idaddress,
     address.`city` AS address_city
FROM
     `address` address]]>
    </queryString>
    <field name="address_idaddress" class="java.lang.Integer"/>
    <field name="address_city" class="java.lang.String"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="79" splitType="Stretch">
            <textField>
                <reportElement x="121" y="0" width="313" height="49" uuid="e3c5e2b7-14a4-4e26-a90c-ae4702f4ac65"/>
                <textElement>
                    <font size="36"/>
                </textElement>
                <textFieldExpression><![CDATA[Mi primer reporte]]></textFieldExpression>
            </textField>
        </band>
    </title>
    <pageHeader>
        <band height="35" splitType="Stretch">
            <staticText>
                <reportElement x="22" y="0" width="510" height="35" uuid="7d375361-1b1d-4a7b-8788-9dd432b86c43"/>
                <textElement>
                    <font size="18"/>
                </textElement>
                <text><![CDATA[Este es un reporte de prueba para aprender a usar iReport]]></text>
            </staticText>
        </band>
    </pageHeader>
    <columnHeader>
        <band height="20" splitType="Stretch">
            <staticText>
                <reportElement x="0" y="0" width="100" height="20" uuid="2604ec98-ab97-4192-9a22-c6c4f95b2416"/>
                <text><![CDATA[address_idaddress]]></text>
            </staticText>
            <staticText>
                <reportElement x="100" y="0" width="100" height="20" uuid="afb38cfb-3eeb-4af9-a520-93338b12eed9"/>
                <text><![CDATA[address_city]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="20" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="100" height="20" uuid="aabd4a33-ecb9-4c60-b366-f770a01c32f8"/>
                <textFieldExpression><![CDATA[$F{address_idaddress}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="100" y="0" width="100" height="20" uuid="58913924-4ffe-4e78-a783-b2364f66c27a"/>
                <textFieldExpression><![CDATA[$F{address_city}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>

Note: No more table component and subdatasource, the fields of the main datasource are referenced directly in the textField's. The detail band will be repeated as many time as there are records in your query.

Example using jr:table component

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="PrimerReporte" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="eb8d4459-c8dc-4c96-bc35-ac988007e32a">
    <style name="table">
        <box>
            <pen lineWidth="1.0" lineColor="#000000"/>
        </box>
    </style>
    <style name="table_TH" mode="Opaque" backcolor="#A52A2A">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="table_CH" mode="Opaque" backcolor="#FFBFBF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="table_TD" mode="Opaque" backcolor="#FFFFFF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
        </box>
        <conditionalStyle>
            <conditionExpression><![CDATA[new Boolean($V{REPORT_COUNT}.intValue()%2==0)]]></conditionExpression>
            <style backcolor="#FFEFEF"/>
        </conditionalStyle>
    </style>
    <subDataset name="MyDataset" uuid="b9d7b569-d19e-451e-8a15-c558918e1698">
        <queryString language="SQL">
            <![CDATA[SELECT
     address.`idaddress` AS address_idaddress,
     address.`city` AS address_city
FROM
     `address` address]]>
        </queryString>
        <field name="address_idaddress" class="java.lang.Integer"/>
        <field name="address_city" class="java.lang.String"/>
    </subDataset>
    <queryString>
        <![CDATA[SELECT 1]]>
    </queryString>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="79" splitType="Stretch">
            <textField>
                <reportElement x="121" y="0" width="313" height="49" uuid="e3c5e2b7-14a4-4e26-a90c-ae4702f4ac65"/>
                <textElement>
                    <font size="36"/>
                </textElement>
                <textFieldExpression><![CDATA[Mi primer reporte]]></textFieldExpression>
            </textField>
        </band>
    </title>
    <pageHeader>
        <band height="35" splitType="Stretch">
            <staticText>
                <reportElement x="22" y="0" width="510" height="35" uuid="7d375361-1b1d-4a7b-8788-9dd432b86c43"/>
                <textElement>
                    <font size="18"/>
                </textElement>
                <text><![CDATA[Este es un reporte de prueba para aprender a usar iReport]]></text>
            </staticText>
        </band>
    </pageHeader>
    <columnHeader>
        <band height="61" splitType="Stretch"/>
    </columnHeader>
    <detail>
        <band height="125" splitType="Stretch">
            <componentElement>
                <reportElement key="table" style="table" x="0" y="0" width="401" height="80" uuid="8c775bc1-4076-44c4-a7af-1fc405283908"/>
                <jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">
                    <datasetRun subDataset="MyDataset" uuid="ec302d39-8b71-4c05-86c0-74bb5a2b71d4">
                        <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                    </datasetRun>
                    <jr:column width="200" uuid="37b69ab0-be68-4dda-8739-08860a5dd8c7">
                        <jr:tableHeader style="table_TH" height="30" rowSpan="1"/>
                        <jr:columnHeader style="table_CH" height="30" rowSpan="1">
                            <textField>
                                <reportElement x="0" y="0" width="200" height="30" uuid="91dcc02e-4f54-446e-9092-d8e8fef6bc78"/>
                                <textFieldExpression><![CDATA[$F{address_idaddress}]]></textFieldExpression>
                            </textField>
                        </jr:columnHeader>
                        <jr:detailCell style="table_TD" height="20" rowSpan="1"/>
                    </jr:column>
                    <jr:column width="200" uuid="36dc21c0-a551-4df8-a180-95cf5b9cfc0c">
                        <jr:tableHeader style="table_TH" height="30" rowSpan="1"/>
                        <jr:columnHeader style="table_CH" height="30" rowSpan="1">
                            <textField>
                                <reportElement x="0" y="0" width="200" height="30" uuid="294719c1-d37b-4b53-ab0e-2045397c0d60"/>
                                <textFieldExpression><![CDATA[$F{address_city}]]></textFieldExpression>
                            </textField>
                        </jr:columnHeader>
                        <jr:detailCell style="table_TD" height="20" rowSpan="1"/>
                    </jr:column>
                </jr:table>
            </componentElement>
        </band>
    </detail>
</jasperReport>

Note: How the main query is only SELECT 1, mysql statement that will return 1 (in 1 record) and how the $P{REPORT_CONNECTION} is passed so that the subDataset can execute the query.