6 Replies Latest reply on Oct 17, 2007 10:22 AM by Dan Bracuk

    error using cfquery param

      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">

      04:45:06.006 - Database Exception - in /data/............/qry_detail.cfm : line 8
      Error Executing Database 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

        • 1. Re: error using cfquery param
          Level 7
          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.

          • 2. Re: error using cfquery param
            jjonney Level 1
            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.

            • 3. error using cfquery param
              paross1 Level 2
              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?

              • 4. Re: error using cfquery param
                rdk8487 Level 1
                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
                • 5. Re: error using cfquery param
                  mkane1 Level 1
                  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.

                  • 6. Re: error using cfquery param
                    Dan Bracuk Level 5
                    Nothing to do with the actual question, but the query may have a logic problem with the dates. Or it may not.