CODEWITHSUNDEEP
coldfusion
blippoids
blippoids

Reputation: 43

cfquery: Could not find prepared statement with handle x

We are receiving the following error intermittently, but often:

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Could not find prepared statement with handle 1.

I implemented the two solutions to this problem that I was able to find-

  1. Put a semicolon at the end of the query (supposedly to force recompile of query)
  2. Put this MS-SQL at the end of the query: OPTION (RECOMPILE)

I applied fix 1 and the errors stopped for the remainder of that day. The following day the error was back.

I applied fix 2 and the same thing happened- no more errors until the following day.

I do understand that whenever I use a <cfqueryparam> a prepared statement is used automatically, but isn't there a way to force the db to "re-prepare" every time the query is used and not rely on cached statements?

Note that I do not have admin access to this Cf server.

Cf version is 9.0.0

SQL Server 9.0.3054.

The offending query:

<cfquery datasource="#dsn#" name="q" maxrows="1">
SELECT
ID
FROM
tableOne
WHERE ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.ID#">
ORDER BY ID
</cfquery>

Thanks

Upvotes: 4

Views: 2713

Answers (3)

rip747
rip747

Reputation: 9455

this might sound stupid, but have you considered that maybe the maxrows attribute might be causing your pain. Trying using TOP and see what happens.

<cfquery datasource="#dsn#" name="q">
SELECT TOP 1
ID
FROM
tableOne
WHERE ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.ID#">
ORDER BY ID
</cfquery>

Upvotes: 0

XaxD
XaxD

Reputation: 1538

You can set the CachedWithin attribute to your cfquery statement..

<cfquery 
    name="GetParks" datasource="#dsn#" name="q" maxrows="1" 
    cachedwithin="#CreateTimeSpan(0, 0, 0, 0)#">

the above example will keep the query out of the cache at all times

Upvotes: 1

Brad Wood
Brad Wood

Reputation: 3953

in regards to your error: I've seen that error before when the value of #dsn# changed from one request to the next. If that is not the case, try applying your CF updates since you're still on 9.0.0.

In regards to your question: Set "Max Pooled Statements" to 0. This will require CF to re-prepare the statement each time. Also, unchecking "Maintain Connections" will also require re-compilation since the connection to the DB is re-negotiated on each request.

Also a technical note, SQL Server will always cache the execution plan regardless of what you do in CF. CF is just holding on to a handle for that prepared statement so it doesn't have to pass the SQL across the wire every time.

Upvotes: 1

Related Questions