2 queries are being run on the same Connection

[btenney]

I am running a report which contains the detail query and a subdataset.  This report generally runs fine, but occassionally an error is occurring where the jdbc driver complains that more than one query is being run at one time.  The jdbc driver is for Netezza, here are the versions.

jasper version: 4.7
netezza jdbc version: 6.0.5p5

Here is a skeleton of what my jrxml looks like:

 

<?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="dropout" language="groovy" pageWidth="760" pageHeight="842" columnWidth="760" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="0b12bf8d-0ce0-4c4b-8556-6c1efebd40b0">
<property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>
<property name="net.sf.jasperreports.export.xls.white.page.background" value="false"/>
<property name="net.sf.jasperreports.print.keep.full.text" value="true"/>
<property name="ireport.zoom" value="1.0"/>
<property name="ireport.x" value="0"/>
<property name="ireport.y" value="0"/>
<subDataset name="summary" uuid="5985f1c7-f8a1-475b-9222-14e8c3c02f34">
<parameter name="param1" class="java.util.Date"/>
...
<parameter name="param2" class="java.lang.String" isForPrompting="false">...</parameter>
<queryString language="SQL">
<![CDATA[select a from b]]>
</queryString>
<field name="a" class="java.lang.Integer"/>
</subDataset>
<parameter name="param1" class="java.util.Date"/>
...
<parameter name="param2" class="java.lang.String" isForPrompting="false">...</parameter>
<queryString>
<![CDATA[select x from y]]>
</queryString>
<field name="x" class="java.lang.String"/>
<background>
<band splitType="Stretch"/>
</background>
<title>
<band height="150" splitType="Stretch">
<componentElement>
<reportElement key="summary" mode="Transparent" x="0" y="0" width="760" height="150"/>
<jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" printOrder="Vertical">
<datasetRun subDataset="summary" uuid="04eb3248-8ee0-4c94-b632-aa1751aa3710">
<datasetParameter name="param1">...</datasetParameter>
...
<datasetParameter name="param2">...</datasetParameter>
</datasetRun>
<jr:listContents height="150" width="760">
<textField>...</textField>
...
<textField>...</textField>
</jr:listContents>
</jr:list>
</componentElement>
</band>
</title>
<detail>
<band height="15" splitType="Stretch">
<textField>...</textField>
...
<textField>...</textField>
</band>
</detail>
</jasperReport>

 

As I said, many times this report runs without a problem. However, when I run with certain datasets, I get the following error:

Caused by: net.sf.jasperreports.engine.JRRuntimeException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : summary at net.sf.jasperreports.components.list.VerticalFillList.prepare(VerticalFillList.java:139) at net.sf.jasperreports.engine.fill.JRFillComponentElement.prepare(JRFillComponentElement.java:129) at net.sf.jasperreports.engine.fill.JRFillElementContainer.prepareElements(JRFillElementContainer.java:328) at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:377) at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:351) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillTitle(JRVerticalFiller.java:339) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportStart(JRVerticalFiller.java:273) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:144) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:909) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:822) at net.sf.jasperreports.engine.fill.JRFiller.fill(JRFiller.java:61) at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:446) at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:849) at mshare.report.jasper.JasperReportBuilder.fill(JasperReportBuilder.java:141) at mshare.report.jasper.JasperReportBuilder.generate(JasperReportBuilder.java:95) ... 122 more Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : summary at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:246) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1073) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:667) at net.sf.jasperreports.components.list.FillDatasetRun.start(FillDatasetRun.java:157) at net.sf.jasperreports.components.list.VerticalFillList.prepare(VerticalFillList.java:93) ... 136 more Caused by: org.netezza.error.NzSQLException: netezza.max.stmt.handles at org.netezza.internal.QueryExecutor.sendQuery(QueryExecutor.java:428) at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:72) at org.netezza.sql.NzConnection.execute(NzConnection.java:2688) at org.netezza.sql.NzStatement._execute(NzStatement.java:849) at org.netezza.sql.NzPreparedStatament.executeQuery(NzPreparedStatament.java:169) at sun.reflect.GeneratedMethodAccessor2087.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at net.sf.hajdbc.util.reflect.Methods.invoke(Methods.java:52) at net.sf.hajdbc.sql.AbstractInvocationHandler$SimpleInvoker.invoke(AbstractInvocationHandler.java:553) at net.sf.hajdbc.sql.DatabaseReadInvocationStrategy.invokeAll(DatabaseReadInvocationStrategy.java:68) at net.sf.hajdbc.sql.DatabaseReadInvocationStrategy.invoke(DatabaseReadInvocationStrategy.java:47) at net.sf.hajdbc.sql.AbstractInvocationHandler.invoke(AbstractInvocationHandler.java:98) at $Proxy248.executeQuery(Unknown Source) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:239) ... 140 more

The error "org.netezza.error.NzSQLException: netezza.max.stmt.handles" indicates that the driver is being sent a second query before the first one is finished. (It could also mean the previous statement was not closed properly, but I'm not seeing any such errors in my log.)

[Ankur Gupta]

Hi btenney,

The error probably is not in the query or the report there might be following posibilities.

1. There is some where divide by 0 exception in the variable you have defined or in the fields contains expressions which cause this error as you have clearly stated that for few set of values the error comes.

2. There might be a problem in the parameters you have set, you might have skipped the default values for instance I generally give -1 for Integer/Nuber parameter and " " for a string parameter if there is no special requirement.

3. You should take care of the sequence of parameters , for instance there are two parameters P1 and P2 and they are defined in a sequence P1 and then P2 but P1 uses the P2 value, so there would be this kind of error.

Hope that rechecking the above points might help you

Regards,

Ankur