MSSQL view query with datediff creates undefined query variable

[deejaychris]

Hi,

In coldfusion 9 i use a view as a table, defined in a MSSQL 2005 db. nothing weird so far.

code:

<cfquery name="viewQuery" datasource="foo">

     select bar from ViewTable where datediff(d,date1, date2) = 0

</cfquery>

When the result is 0 de ViewQuery variable is undefined, in previous versions it wasn't the case, because it acted like a normal table.

Is this going to stay this way or is it a bug?

Kind regards,

Chris

[Adam Cameron.]

You need to bear in mind that CF has no understanding of SQL or DB constructs (like the difference between a table and a view, for instance).  All CF does with whatever is between <cfquery> tags is to pass it to the JDBC driver, which in turn passes it to the DB.  Then the DB processes it.

If you're getting unexpected results with your code... it's the DB that's doing something unexpected with them!  Now this might be down to the settings in you datasource or something like that, or the security levels that the user the datasource is configured to use, or something like that, but it's nowt to do with CF.

There's a slim chance it might be something to do with the JDBC drivers that ship with CF9 (there have been other issues reported with them), so if all else fails, perhaps retrograde to the drivers that shipped with CF8 and see if that helps.

--

Adam

[deejaychris]

http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html#bugId=80384