Skip to main content
Participant
November 3, 2009
Question

"No more available statements" error with DB2 Universal Database driver

  • November 3, 2009
  • 1 reply
  • 1095 views

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

This topic has been closed for replies.

1 reply

The LimitAuthor
Participant
November 3, 2009

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>