Copy link to clipboard
Copied
I came across an issue with the default DB2 driver and (although I've found a workaround) I was wondering if anyone has also come across this and figured out how to fix the underlining issue.
When I use default "DB2 Universal Database" driver and cfqueryparam(s); only a certain number of query variations are allowed. After the limit is reached, I get the following error:
[Macromedia][DB2 JDBC Driver]No more available statements. Please recreate your package with a larger dynamicSections value.
Here's an example of code that would cause the error (assuming that the dynamicSections value is less then 300) :
<cfoutput>
<cfloop from="1" to="300" index="x">
<strong>TRY #x# : </strong>
<cftry>
<cfquery datasource="MY_DB2_SOURCE" name="qryMyQuery">
SELECT ID, DESCRIPTION
FROM MTCP.INTERVENTIONS
WHERE ID = <cfqueryparam value="1" cfsqltype="cf_sql_integer">
AND #x#=#x#
</cfquery>
OK - #qryMyQuery.DESCRIPTION#<br>
<cfcatch type="any">
<cfdump var="#cfcatch#">
<cfbreak>
</cfcatch>
</cftry>
</cfloop>
</cfoutput>
Increasing the dynamicSections value does allow for more query statements, but only until the limit is reached. We were able to work around this issue by using a different JDBC driver for DB2 ... is that the only workaround for this? is the driver that ships with Coldfusion (Version 8.0.1) broken? or is there a setting that I'm missing?
Thanks,
Eric
Copy link to clipboard
Copied
Sorry, for some reason the sample code was not included:
<cfoutput>
<cfloop from="1" to="300" index="x">
<strong>TRY #x# : </strong>
<cftry>
<cfquery datasource="MY_DB2_DS" name="qryTest">
SELECT ID, DESCRIPTION
FROM MY_DBSCHEMA.MY_TABLE
WHERE ID = <cfqueryparam value="1" cfsqltype="cf_sql_integer">
AND #x#=#x#
</cfquery>
OK - #qryTest.DESCRIPTION#<br><cfcatch type="any">
<cfdump var="#cfcatch#">
<cfbreak>
</cfcatch>
</cftry>
</cfloop>
</cfoutput>