CF_SQL_DATE Issues with MS SQL Server 2005
At work, we are using MS SQL Server 2000. Works great. At home though, I have more of a developer's environment. I am running CF Developer's edition and MS SQL Server 2005 single user edition (or something, can't quite remember what it is).
I don't get a lot of time to work on stuff at home, but one night, I had some time to kill, so I brought a personal site from work to home. I copied the files over, created the datasource, started her up and she opened file... until I started to click around. Once I went to any DB driven page I got a SQL error that "optional components had not been installed." Not being a SQL master, I had no idea what this meant, nor did I have any idea how to debug it. So, I just started taking out parts of the query until things started running again.
Apparently, what was causing the error was my use of the CF_SQL_DATE sql type in my query params. Not having internet at home (crazy I know), all I could do was look up the CF help files. Well, in the CF query param help files, apparently CF_SQL_DATE doesn't actually translate very well to MS SQL server, if I understood the documentation correctly. It seems that CF_SQL_TIMESTAMP is the one type that translates over to DATETIME in MS SQL server.
So I went back into the query, replaced the DATE with the TIMESTAMP, ran the page again and presto-chango, works like a charm. I then did this for all the pages on the site and everything is running great.
The only caveat with TIMESTAMP is that is uses the TIME part of the date, where as CF_SQL_DATE, I believe, did not. So now, I just have to be sure to use date's that only have date portions before using them in the database.
Reader Comments
For the love of god... thanks so much for posting this. I was having one of those "this is way too easy for it not to be working" moments up until I realized that the SQL Server version was the variable in my equation. Take care!
Hi Ben,
I had a problem with cf_sql_date on MySQL for a simple query that should run on MSSQL2000 and 2005 as well. It seemed that I didn't get the timestamp into the db-field when using this cfsqltype, even when requiring the db-field to be filled with createodbcdatetime(). Taking the hint from your article I switched to cfsqltimestamp and voila, on all db-server environments I got the required timestamp ;-)
Merci!
Thank you posting this and for your blog.
I finally found this blog entry using Google and switching the cfsqltype from cf_sql_date to cf_sql_timestamp solved the problem I was having querying a MS SQL Server 2000 table.
@Bruce,
Glad to help :)
Love it, saved my ass ;)
I am using the POI utility to upload data into a excel spread sheet. I had seen your code helps. The number part works but the date part is a problem in my case.
I tried to use the sqldatetime in my querynew declaration for the columns.
I tried to put the datatime as string in the row because javacast does not accept any other format. I tried float and double. int was out of option in anycase. But non work.
Can you help me?
But the javacast throws an exception.
--------------------------
An error occurred while trying to modify the query named class coldfusion.sql.QueryTable.
Query objects cannot be modified, they can only be displayed.
The error occurred in D:\WebSites\WebServices\Aruna\Student_NoShow1\admin\test1.cfm: line 28
26 : <cfset qMovie[ "Semester" ][ #rownum# ] = JavaCast( "string", "#Strm_Desc#" ) />
27 : <!--- <cfset qMovie[ "Date_1" ][ #rownum# ] = JavaCast( "string", "#Strm_Desc#" ) />--->
28 : <cfset qMovie[ " Date_1" ][ #GetAllSemester.currentrow# ] = JavaCast( "double", "#gdate#") />
29 : <cfset qMovie[ "Reason" ][ #rownum# ] = JavaCast( "string","#Reason#" ) />
30 : </cfloop>
----------------------------
sorry I used CF_SQL_TIMESTAMP in the querynew statement.
My code:
<!--- Create query to ouptut to excel. --->
<cfset qMovie = QueryNew(
"StudentFName,StudentLName,StudentID,InstructorName,CoursePrefix,CourseNo,Section,Semester,Date_1,Reason",
"CF_SQL_VARCHAR,CF_SQL_VARCHAR,CF_SQL_VARCHAR,CF_SQL_VARCHAR,CF_SQL_VARCHAR,CF_SQL_VARCHAR,
CF_SQL_VARCHAR, CF_SQL_VARCHAR,CF_SQL_TIMESTAMP,CF_SQL_VARCHAR") />
<!--- Add rows to query. --->
<cfset QueryAddRow( qMovie, #GetAllSemester.recordcount# ) />
<!--- Set row data. --->
<cfloop query="GetAllSemester">
<Cfset rownum=#GetAllSemester.currentrow#>
rownum: #rownum#, #GetAllSemester.currentrow# ,
qMovie[ "StudentFName" ][ #rownum# ] = JavaCast( "string", "#Stud_FN#" )<br />
<cfset qMovie[ "StudentFName" ][ #rownum# ] = JavaCast( "string", "#Stud_FN#" ) />
<cfset qMovie[ "StudentLName" ][ #rownum# ] = JavaCast( "string", "#Stud_LN#" ) />
<cfset qMovie[ "StudentID" ][#rownum# ] = JavaCast( "string", "#STud_no#" ) />
<cfset qMovie[ "InstructorName" ][ #rownum# ] = JavaCast( "string", "#Fac_name#" ) />
<cfset qMovie[ "CoursePrefix" ][ #rownum# ] = JavaCast( "string", "#Area#" ) />
<cfset qMovie[ "CourseNo" ][#rownum# ] = JavaCast( "string", "#NBR#" ) />
<cfset qMovie[ "Section" ][ #rownum# ] = JavaCast( "string", "#Sec#" ) />
<cfset qMovie[ "Semester" ][ #rownum# ] = JavaCast( "string", "#Strm_Desc#" ) />
<!--- <cfset qMovie[ "Date_1" ][ #rownum# ] = JavaCast( "string", "#Strm_Desc#" ) />--->
<cfset qMovie[ " Date_1" ][ #GetAllSemester.currentrow# ] = JavaCast( "double", "#gdate#") />
<cfset qMovie[ "Reason" ][ #rownum# ] = JavaCast( "string","#Reason#" ) />
</cfloop>
Error is :
An error occurred while trying to modify the query named class coldfusion.sql.QueryTable.
Query objects cannot be modified, they can only be displayed.
The error occurred in D:\WebSites\WebServices\Aruna\Student_NoShow1\admin\test1.cfm: line 28
26 : <cfset qMovie[ "Semester" ][ #rownum# ] = JavaCast( "string", "#Strm_Desc#" ) />
27 : <!--- <cfset qMovie[ "Date_1" ][ #rownum# ] = JavaCast( "string", "#Strm_Desc#" ) />--->
28 : <cfset qMovie[ " Date_1" ][ #GetAllSemester.currentrow# ] = JavaCast( "double", "#gdate#") />
29 : <cfset qMovie[ "Reason" ][ #rownum# ] = JavaCast( "string","#Reason#" ) />
30 : </cfloop>
@Aruna,
Usually that error about modifying queries comes from trying to reference a query column that doesn't exist. You shouldn't get an error for storing data in a query, even if it's the wrong data type (the query doesn't really care until you try to actually use the data).
Usually, this is due to a spelling mistake or leaving a space somewhere.