Error when result set becomes bigger

[dirk.wouters]

I have a main report with some subreports. If the result set is not so big it works fine. When the result set increases I get the next message:

net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query: select percentage,resnr,resnaam,beheerder from planning.in_erkende_perimeter(?)

The query mentioned in this error message is used in one of the subreports.

Is there somewhere a limitation involved?

[Friendly User]

Hello Dirk,

JRS doesn't really have a limit for report result set, only for ad hoc views/reports. Do you get the same error if you run the report in the studio? How do you increase the result set? Do you pass different filter values? The subreport query looks to be having problems running which means most likely it is not constructed properly. Do you use $P! syntax?

[dirk.wouters]

<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 6.1.1.final using JasperReports Library version 6.1.1  --><!-- 2016-11-04T09:09:05 --><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="sub percelen in erkende perimeters" printOrder="Horizontal" pageWidth="200" pageHeight="100" columnWidth="200" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" isIgnorePagination="true" uuid="7b45ae02-0d94-4d6d-81f9-6534db6f51f6">    <property name="com.jaspersoft.studio.unit." value="pixel"/>    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="Reservatendatabank"/>    <property name="com.jaspersoft.studio.unit.pageHeight" value="pixel"/>    <property name="com.jaspersoft.studio.unit.pageWidth" value="pixel"/>    <property name="com.jaspersoft.studio.unit.topMargin" value="pixel"/>    <property name="com.jaspersoft.studio.unit.bottomMargin" value="pixel"/>    <property name="com.jaspersoft.studio.unit.leftMargin" value="pixel"/>    <property name="com.jaspersoft.studio.unit.rightMargin" value="pixel"/>    <property name="com.jaspersoft.studio.unit.columnWidth" value="pixel"/>    <property name="com.jaspersoft.studio.unit.columnSpacing" value="pixel"/>    <parameter name="polygoon" class="java.lang.Object"/>    <queryString language="SQL">        <![CDATA[select percentage,resnr,resnaam,beheerder from planning.in_erkende_perimeter($P{polygoon})]]>    </queryString>    <field name="percentage" class="java.lang.Double"/>    <field name="resnr" class="java.lang.String"/>    <field name="resnaam" class="java.lang.String"/>    <field name="beheerder" class="java.lang.String"/>    <variable name="samen" class="java.lang.String">        <variableExpression><![CDATA[LEN($V{samen}) == 0 ? new java.text.DecimalFormat("##0").format($F{percentage}) + "% " + $F{resnr} + " " + $F{resnaam} + " " + $F{beheerder} + "n" : $V{samen} + new java.text.DecimalFormat("##0").format($F{percentage}) + "% " + $F{resnr} + " " + $F{resnaam} + " " + $F{beheerder}]]></variableExpression>        <initialValueExpression><![CDATA[""]]></initialValueExpression>    </variable>    <detail>        <band height="15" splitType="Stretch">            <property name="local_mesure_unitheight" value="pixel"/>            <printWhenExpression><![CDATA[false]]></printWhenExpression>            <textField>                <reportElement x="30" y="0" width="30" height="15" uuid="a9023381-4f87-405a-b9ed-69e779e5423a"/>                <textFieldExpression><![CDATA[$F{resnr}]]></textFieldExpression>            </textField>            <textField isStretchWithOverflow="true">                <reportElement stretchType="RelativeToBandHeight" x="60" y="0" width="100" height="15" uuid="f3028648-9a69-49c3-894f-18e563471a6d"/>                <textFieldExpression><![CDATA[$F{resnaam}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="160" y="0" width="40" height="15" uuid="9068c1bc-6e0a-4a62-b118-fa1b8c35a60c"/>                <textFieldExpression><![CDATA[$F{beheerder}]]></textFieldExpression>            </textField>            <textField pattern="#,##0.00#">                <reportElement x="0" y="0" width="30" height="15" uuid="908e7cf6-197f-4b96-8c36-1f0368b3c67a"/>                <textFieldExpression><![CDATA[$F{percentage}]]></textFieldExpression>            </textField>        </band>    </detail>    <summary>        <band height="14">            <textField isStretchWithOverflow="true">                <reportElement stretchType="RelativeToBandHeight" x="0" y="0" width="200" height="14" uuid="2042104c-6864-419d-a8ab-5a57608c8dea"/>                <textFieldExpression><![CDATA[$V{samen}]]></textFieldExpression>            </textField>        </band>    </summary></jasperReport>[/code]

This is the code from the subreport which causes the error. Using the $P!{} syntax doesn't work at all.

I have maybe a special parameter because it is a Postgis geometry format which I have defined as a java.lang.object class.