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

[RVonDrunkpants]

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

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?

[Carl Von Stetten]

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.

[WolfShade]

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.

^_^

[Dan_Bracuk]

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.

[Adam Cameron.]

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

[Adam Cameron.]

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

[RVonDrunkpants]

Carl Von Stetten wrote:

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.

Right... I get that, and I don't typically use SELECT * in the queries I write... it's more about all of the places in the legacy code that use it, which is going to be semi-painful to fix.

I'm ultimately curious why it fails out on one server, and not on another.

[Miguel-F]

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

[BKBK]

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.

[RVonDrunkpants]

BKBK wrote:

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

Sure.

I took away one column that wasn't being used... at all... like 2000+ users with (null) values. I also added three others.

[BKBK]

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.

[RVonDrunkpants]

BKBK wrote:

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.

Thanks, I put in a ticket for our server team to check the ODBC driver versions in the Production, Testing, and Staging environments. It would be nice if that was the issue.

I have a feeling that the Production server may have been an upgrade of an upgrade, whereas the other two were likely clean installs.