Skip navigation
RedmonRob
Currently Being Moderated

CF9 Problems with Select * query, resulting in Value can not be converted to requested type.

Dec 17, 2012 9:42 AM

Tags: #error #cfquery #cf9

So, I work on a legacy CF web site and there are numerous SELECT * FROM USERS_TABLE queries all over the site.

 

Well, we changed the structure of said table in the database on our Testing and Staging sites, with no issues.

 

When we pushed up to our production environment and changed the structure of the production DB table, the server kept kicking back "Value can not be converted to requested type."

 

After doing some searching out there, it looks like CF caches the structure of the table, and you either have to restart CF to clear it, or rename and then name-back the DSN to fix the issue.

http://www.bennadel.com/blog/194-ColdFusion-Query-Error-Value-Can-Not- Be-Converted-To-Requested-Type.htm

 

That said, this doesn't happen in our testing and staging environments - so what would be the difference?

 

Is there some setting I need to change in the CF Admin to keep this from happening again?

 
Replies
  • Currently Being Moderated
    Dec 17, 2012 1:07 PM   in reply to RedmonRob

    This is a common issue with using SELECT * statements.  SELECT * is often frowned upon by experienced developers for a number of reasons:

    1. It brings back every column from the table(s) being queried, when often only a subset is required.  With large tables, this can result in transferring much more data than is needed, slowing performance.
    2. As you noticed, the first time ColdFusion runs the query, it caches the table columns and doesn't pick up subsequent changes to the table structure.
    3. Queries should be written to retrieve the data required for a given purpose, and only that data.
    4. If a column is removed from the table, anywhere the deleted column is used will throw an error.  This will be easier to catch if you specify the column explicitly in your CFQuery statement - the query itself will likely be the first error thrown and will help to immediately identify where the problem is.
    5. There are other reasons that I'm sure I'm forgetting.

     

    I'd recommend rewriting the queries in question to explicitly state columns names in the SELECT statement, even if you are actually retrieving all of the columns.

    -Carl V.

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 17, 2012 1:34 PM   in reply to Carl Von Stetten

    Carl Von Stetten wrote:

     

    There are other reasons that I'm sure I'm forgetting.

     

    The database first has to reference the system table that contains the column names of the target table to actually get the column names that it needs to retrieve data from.

     

    Also, if you can use a Stored Procedure to retrieve the data, do so.  Standard queries gets all the information, anyway, chokes bandwidth passing it all to the CF server, and forces the CF server to filter, sort, and format the data.  SPs tell the db server to get ONLY the data requested, and forces the db server to filter and sort the data, leaving only formatting to the CF server.


    ^_^

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 17, 2012 2:10 PM   in reply to RedmonRob

    Are there differences in the cache settings between dev and prod servers?  Maybe dev does not cache queries but prod does.

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 17, 2012 2:55 PM   in reply to WolfShade

    Regarding, "Standard queries gets all the information, anyway, chokes bandwidth passing it all to the CF server, and forces the CF server to filter, sort, and format the data.  SPs tell the db server to get ONLY the data requested, and forces the db server to filter and sort the data, leaving only formatting to the CF server."

     

    Codswallop.  It doesn't matter if your sql is written as a cfquery or sproc.  What matters is the sql you actually write.

     

    There are advantages to stored procedures, but this isn't one of them.

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 18, 2012 2:02 AM   in reply to RedmonRob

    RedmonRob wrote:

     

     

    When we pushed up to our production environment and changed the structure of the production DB table,

    You say you changed the structure, however could you tell us in concrete terms what you actually did.

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 18, 2012 3:20 AM   in reply to WolfShade

    Also, if you can use a Stored Procedure to retrieve the data, do so.  Standard queries gets all the information, anyway, chokes bandwidth passing it all to the CF server, and forces the CF server to filter, sort, and format the data.  SPs tell the db server to get ONLY the data requested, and forces the db server to filter and sort the data, leaving only formatting to the CF server.

     

    That's not true. The only time CF messes with data returned from the DB is if there's a maxrows attribute, and the record set returnded from the DB has more than that number of records... which causes CF to truncate the recordset to the correct size before returning it. The DB might or might not stop sending rows down to CF after CF says "yeah, I've got all I want now".

     

    Other than that, for all intents and purposes all CF does with the SQL is pass it to the DB and wait for an answer. The only thing it does to the returned data is to create a CF record set ("query") with it... this does not involve any filtering and sorting.

     

    --

    Adam

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 18, 2012 3:21 AM   in reply to Dan Bracuk

    Codswallop.  It doesn't matter if your sql is written as a cfquery or sproc.  What matters is the sql you actually write.

    Sorry Dan, didn't see your reply before making my own one, along these lines.

     

    --
    Adam

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 18, 2012 8:39 AM   in reply to RedmonRob

    The SQL code 'select * from Tbl' may have its disadvantages, but it has one plus-point. That is, software reuse. You may change the structure of the table as much as you want, you can still reuse the SQL code. At least, that is the idea.

     

    I consider the issue a Coldfusion bug (as it is very likely caused by the database drivers implemented by ColdFusion). I would advise you to file a bug report.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points