• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

error using cfquery param

New Here ,
Sep 18, 2007 Sep 18, 2007

Copy link to clipboard

Copied

The following query (CFMX7) is working fine with my MS SQL database. However, if I go in and add a field to the table the code then generates an error. If I remove the use of cfqueryparam and just use #calID# for my variable, then I can add the field without error. I've had my DB admin try adding the field and the same thing happens.
The CF error that is generated is listed below the query

<cfquery name="getEvents" datasource="#ds#">
SELECT * FROM MyTable
where approved = 1
AND evStartDate <= #CreateODBCDate(lastofmonth)#
AND evstartDate >= #CreateODBCDate(firstofMonth)#
AND calID = <cfqueryparam value="#calID#" cfsqltype="cf_sql_integer">
</cfquery>


ERROR:
04:45:06.006 - Database Exception - in /data/............/qry_detail.cfm : line 8
Error Executing Database Query.

HERE's THE PARSED VIEW OF THE QUERY
SELECT * FROM MYTable
where approved = 1
AND evStartDate <= {d '2007-09-30'}
AND evstartDate >= {d '2007-09-01'}
AND calID = ?

Query Parameter Value(s) -
Parameter #1(cf_sql_integer) = 1

TOPICS
Advanced techniques

Views

502

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

LEGEND , Sep 18, 2007 Sep 18, 2007
This is a known issue when using SELECT *. Somewhere along the way the CF
or the JDBC driver is caching the column list it expects to get back from
the DB, and it gets confused when the DB returns a different column list,
despite the fact the SQL statement is the same.

The solution is to not use SELECT *, which is generally considered poor
practice anyhow.

Do you actually mean to get ALL the columns back for that table, or are you
using SELECT * as a short cut to not have to think about which ...

Votes

Translate

Translate
LEGEND ,
Sep 18, 2007 Sep 18, 2007

Copy link to clipboard

Copied

This is a known issue when using SELECT *. Somewhere along the way the CF
or the JDBC driver is caching the column list it expects to get back from
the DB, and it gets confused when the DB returns a different column list,
despite the fact the SQL statement is the same.

The solution is to not use SELECT *, which is generally considered poor
practice anyhow.

Do you actually mean to get ALL the columns back for that table, or are you
using SELECT * as a short cut to not have to think about which columns you
might want? It's usually the latter.

You should also put the two dates into <cfqueryparam> tags too. And
possibly use the BETWEEN operator, rather than the >= & <= ones.

--
Adam

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Sep 18, 2007 Sep 18, 2007

Copy link to clipboard

Copied

Thanks Adam. In this case, I needed 23 of the 24 fields so I was using select *. But when I went back and listed each individually that solved the problem. I'm going to fix the date variables as well. Thanks again

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Oct 17, 2007 Oct 17, 2007

Copy link to clipboard

Copied

I agree that using SELECT * is usually a poor practice, often the result of being lazy. But not always. Sometimes, I use SELECT * to get the ColumnList and work with the fields, and there are other scenarios.

Nonetheless, saying "Don't use SELECT *" is a bit like the "Doc it hurts when I do this" "Don't do that". WHat do I do if it hurts when I breathe?

What can one do to resolve or avoid the problem? I manage a number of external applications, and some have a lot of inherited code with SELECT *. I can't just go fix all of the instances of SELECT *. So what can I do when I have to upload table design changes? Stop one or more of the Cold Fusion services? Stop the database (SQL Server, in my case)?

I've learned that you can usually "fix" the problem by simply changing the query. Add a space, add a "AND 1 = 1", hit ENTER to move a cfqueryaram to a new line, stuff like that. But that is not a fix.

I would like to know if the problem is with the JDBC driver, or Cold Fusion.

Thanks.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Sep 18, 2007 Sep 18, 2007

Copy link to clipboard

Copied

Try using quotes
AND calID = '<cfqueryparam value="#calID#" cfsqltype="cf_sql_integer">'

and/or also check the condition wether #calID# is blank or not.

Which line is Number 8
04:45:06.006 - Database Exception - in /data/............/qry_detail.cfm : line 8
Error Executing Database Query.


jJ

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
Sep 18, 2007 Sep 18, 2007

Copy link to clipboard

Copied

What is the datatype of the calID field in the database? Are you sure that it is type int? Does it work any differently if you use CF_SQL_NUMERIC?

Phil

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Oct 17, 2007 Oct 17, 2007

Copy link to clipboard

Copied

LATEST
Nothing to do with the actual question, but the query may have a logic problem with the dates. Or it may not.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation