选择*不返回所有列 - Coldfusion / SQL Server 2008 [英] Select * not returning all columns - Coldfusion / SQL Server 2008

问题描述

我得到一些奇怪的行为，涉及数据库查询，我从来没有见过，我希望你可以照亮这个问题。

I am getting some strange behavior involving database queries that I have never seen before and I'm hoping you can illuminate the issue.

我有一个名为myTable的数据表，有一些列;到目前为止一切涉及它已经很好。现在，我刚刚添加了一个名为subTitle的列;和II注意到，提取给定记录的数据的 SELECT * 查询不知道该列（它表示返回的查询没有subTitle列），但如果我明确命名列（选择subTitle）它是。我想也许，ColdFusion服务器可能缓存查询，所以我试图解决 cachedwithin =＃CreateTimeSpan（0，0，0，0）＃骰子。

I have a table of data called myTable with some columns; thus far everything involving it has been fine. Now, I've just added a column called subTitle; and II notice that the SELECT * Query that pulls in the data for a given record is not aware of that column (it says the returned query does not have a subTitle column), but if I explicitly name the column (select subTitle) it is. I thought perhaps that the Coldfusion server might be caching the query so I tried to work around with cachedwithin="#CreateTimeSpan(0, 0, 0, 0)#" but no dice.

请考虑以下代码：

<cfquery name="getSub" datasource="#Application.datasourceName#">
     SELECT   subTitle 
     FROM     myTable
     WHERE    RecordID = '674'
 </cfquery> 

 <cfoutput>#getSub.subTitle#</cfoutput>

 <cfquery name="getInfo" datasource="#Application.datasourceName#">
     SELECT   * 
     FROM     myTable
     WHERE    RecordID = '674'
 </cfquery>  

 <cfoutput>#getInfo.subTitle#</cfoutput>

---

请记住，记录674有字符串测试in the subTitle column about about about the above is

---

Keeping in mind that record 674 has the string "test" in it's subTitle column the about of the above is

测试

[[CRASH WITH ERROR]]

[[CRASH WITH ERROR]]

这对我没有意义，除非SQL Server 2008以某种方式将SELECT *查询与表的先前的化身一起缓存，但奇怪的是，如果我运行查询从SQL管理Studio中没有问题，它显示所有列与选择*

This doesn't make sense to me unless SQL Server 2008 has somehow cached the SELECT * query with the previous incarnation of the table, but the strange thing is if I run the query right from within SQL Management Studio there are no problems and it shows all columns with the select *

坦率地说，这一个让我困惑;我知道我可以解决这个问题，明确命名所有所需的列在选择查询，而不是使用*（这是最好的做法反正），但我想知道为什么会发生。

Frankly this one has me baffled; I know I can get around this by explicitly naming all the desired columns in the select query instead of using * (which is best practice anyway), but I want to understand why this is occurring.

我已经使用SQL Server 2005多年了，从来没有像这样的事情发生，这导致我相信它可能涉及一些新的SQL Server 2008年;但是再次，这个查询在管理工作室内工作正常的事实不会与这个问题jive。

I've worked with SQL Server 2005 for many years and never had something like this happen, which leads me to believe it might involve something new in SQL Server 2008; but then again the fact that the query works fine inside of the management studio doesn't jive with that either.

=== UPDATE ===

===UPDATE===

清除CF管理员中的模板缓存将解决问题

Clearing the Template Cache in the CF admin will solve the issue

推荐答案

缓存< cfquery> SQL字符串。

Yes, ColdFusion caches the <cfquery> SQL string. If the underlying table structure changes, the result might be an exception like you see it.

解决方法：

• 建议的解决方案：
  
  
  
  
  • 如果您有开发或企业版本，在服务器moniter和清除只有查询那里。 （来自@Dpolehonski的感谢，感谢）
  
  
  • 否则，在ColdFusion管理员中的设置/缓存）。
    
    这将使服务器上的所有缓存的CFML模板失效，CF将在必要时重新编译它们。
  
  
  • Recommended solutiuons:
    
    • If you have the development or enterprise version you can view your query cache in the server moniter and clear only the queries there. (comment from @Dpolehonski, thanks)
    • Otherwise, click Clear Template Cache Now in the ColdFusion Administrator (under Server Settings/Caching).
      This will invalidate all cached CFML-Templates on the server and CF will re-compile them when necessary.

    这篇关于选择*不返回所有列 - Coldfusion / SQL Server 2008的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！