CFQUERYPARAM Error

[wbartnick10]

Hello,

I am using MX7 and am having problems with cfqueryparam. I am new to a
project and the old code does not use them and everything I have built recently
uses them.

Now I intermittently receive application errors on only those queries that use
cfqueryparam. All other queries work fine. The error is always something like:

[Macromedia][SQLServer JDBC Driver][SQLServer]Could not find prepared
statement with handle 5

The number at the end changes, but the error is always the same. I haven't
been able to get a clear indication of what is causing this. It occurs
randomly throughout the day. But when it appears, all queries using
cfqueryparam are broken for awhile. Then suddenly it will start working again,
then fail again, etc.

The code itself works fine, it seems like a server issue or a db connection
issue or something. However, since other queries (not using query params)
work, it almost seems specific to how CF deals with cfqueryparams.

Has anyone experienced this? Any ideas on things to try?

Thanks!
Wendell

[wbartnick10]

Hi all,

Anyone run into this issue?

[PaulH]

googling for that error shows it all over the map, XML, DTS, sql 2005, ODBC,
etc.

are you using the jdbc or odbc driver? what version of sql server? can you
post the simplest query that is breaking?

[wbartnick10]

Thanks for the response. I googled as well and didn't find much helpful.

We are using SQL Server 2000 and the JDBC driver that comes with CFMX 7
installation. We have not installed any updates/hot fixes yet.

Here is one example of a query that breaks. Remember, once one breaks, it
seems that every query that uses cfqueryparam also breaks, though I don't know
that 100%. All the queries that fail including this one are called from within
a CFC.

SELECT form_field_type_id
FROM field_customized
WHERE custom_field_id = <cfqueryparam cfsqltype="CF_SQL_INTEGER"
value="#arguments.pCustomFieldID#">

Hope this helps, let me know if you need anything else.

[wbartnick10]

One more note.

When the page errors, I can refresh any number of times and the page will still fail. Then if I change the query to not use the cfqueryparam, the page works.

[wbartnick10]

This might be a better example, because I just see it erroring. When trying to
debug this further, I seem to see problems in queries with if statements in the
query. This may be a stupid question, but could this have something to do with
the binding that occurs to help peformance? I don't know how it works, but
perhaps ColdFusion stores one version of the query (say where the IF statement
is true), then if the query runs when the IF statement is false, it errors. I
honestly don't know.

SELECT customer_learner_id
FROM learners
WHERE customer_learner_id IS NOT NULL
AND customer_learner_id <> ''
<cfif arguments.pStudentID IS NOT "">
AND learnerid <> <cfqueryparam cfsqltype="CF_SQL_INTEGER"
value="#arguments.pStudentID#">
</cfif>

[OldCFer]

Try using:

value="#Val(arguments.pStudentID)#">

If arguments.pStudentID is not a number , it will change it to zero.

[PaulH]

when you drop the cfqueryparam, does your data change as well? as larry points
out it might simply be bad data. though if it was bad data, i think you would
normally see an error thrown (are you swallowing the errors by any chance?).
when this craps out, what shows up in the logs?

frankly i'm not sure about using cfqueryparam within a cfif block, don't think
i've ever used it that way. if it is the problem, you should be able to
reproduce it easily. loop over the query making sure the logic uses the
cfqueryparam, then toss in a few cases where it doesn't.

[wbartnick10]

I 'found' the solution. Basically, I just changed the jdbc driver and the
error no longer occurs. I was using the jdbc driver that comes with the Cold
Fusion MX 7 install. I changed the driver to the latest Microsoft's JDBC
driver.

Thanks for your help.