Copy link to clipboard
Copied
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.
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?
Copy link to clipboard
Copied
This is a common issue with using SELECT * statements. SELECT * is often frowned upon by experienced developers for a number of reasons:
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.
Copy link to clipboard
Copied
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.
^_^
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
Are there differences in the cache settings between dev and prod servers? Maybe dev does not cache queries but prod does.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.