9.0.2 cfc processing sql wrong?

[Danny11223344]

Ok, this is a new one on me.  I had 9.0.1 and everything worked just fine.  Well today 9.0.1 died so I uninstalled and installed 9.0.2.  That all went fine.  Most of my pages call a counter.cfc to keep track of hitcounts on pages.

Here is the cfc:

<CFCOMPONENT HINT="Hitcount processing">

   <!--- List users method --->

<cfset day=LSDateFormat(Now(), "mm/dd/yyyy")>

<cfset time=LSTimeFormat(Now(), "h:mm:ss tt")>

<cfset current="#day#">

   <CFFUNCTION NAME="Add"

                                 RETURNTYPE="void"

               HINT="Add to hitcount datebase">

             <CFARGUMENT NAME="Page"

                               TYPE="string"

                               REQUIRED="true"

                               HINT="Page/App name required">

      <!--- Get users --->

<CFQUERY NAME="HC_Add" DATASOURCE="hitcountSQL">

      INSERT INTO hitcount (Page, Date)

                    VALUES ('#arguments.page#', '#current#')

          </CFQUERY>

   </CFFUNCTION>

</CFCOMPONENT>

The really messed up this is now I get errors everytime this cfc is called. 

Error Executing Database Query.

[Macromedia][SQLServer JDBC Driver][SQLServer]Column name or number of supplied values does not match table definition.

The error occurred in D:/cfscripts/CFCs/counter.cfc: line 16

Called from D:/cfscripts/Test/show.cfm: line 17

Called from D:/cfscripts/CFCs/counter.cfc: line 16

Called from D:/cfscripts/Test/show.cfm: line 17

14 :       <CFQUERY NAME="HC_Add" DATASOURCE="hitcountSQL">

15:       INSERT INTO hitcount

16:                     VALUES ('#arguments.page#')

17:       </CFQUERY>

18:    </CFFUNCTION>

For some reason the cfc is ignoring the column name and second value in the query.  Any clue what in the world causes this?

[WolfShade]

The query you supplied does not match the query in the error message.  The error message query is only providing one argument, the sample you provided has two arguments.

^_^

[Danny11223344]

That is my point.  The query and what is being processed are magically different.  The cfc is not processing the entire sql command.  That is an exact copy/paste from the debug output.

This is only happening in cfcs.  Every cfm is processing correctly.

[Adam Cameron. · Accepted solution]

Looks to me like you've got trusted cache switched on, and what you're seeing is what CF compiled before you make some changes to the file?

Or something CF gets "confused" if too many files end up in the cfclasses dir (if you have "save class files" switched on), and CF sometimes doesn't recompile stuff on change... or something like that, I've never worked it out. But clearing out everything in cfclasses - and switching off "save class files" a) solves the problem; b) prevents it from re-occurring.

Could it be either of those?

--

Adam

[Danny11223344]

It was the save class files.  Once I cleared the box and cleared the cache it started working again.

Thanks.

[meensi]

Yes, I agree with Adam, if the Trusted Cache is ON or if the SAve Class files options is selected in your Coldfusion administrator page, its better to clear the cache files and run the file.

[BKBK]

Yours is a perfectly good example of a use-case for VAR. Do something like

<CFFUNCTION NAME="Add" RETURNTYPE="void" HINT="Add to hitcount datebase">

<CFARGUMENT NAME="Page" TYPE="string" REQUIRED="true" HINT="Page/App name required">

<!--- Var the query name to make it function-local. In fact, the query is local to this function, so you may change the name without causing any side effects elsewhere.  --->

<cfset var HC_Add2 = "">

<!--- Get users --->

<CFQUERY NAME="HC_Add2" DATASOURCE="hitcountSQL">

INSERT INTO hitcount (Page, Date)

VALUES ('#arguments.page#', '#current#')

</CFQUERY>

</CFFUNCTION>

Changing the query name may also solve existing caching issues. If you var a variable, it will practically cease to exist when the function ends.

[Dan_Bracuk]

In addition to the other answers, I have three things to say.

Date is a reserved word in sql server.  If you want to use it as a column name, surround it with square brackets in your sql.

Next, you are running a lot of code to do something that might work, but is wrong.  DateFormat and TimeFormat return strings.  I assume [date] is a DateTime datatype in which case you should be sending a timestamp datatype.  However, in this case, since you want the current date and time, you don't have to do anything with ColdFusion because sql server has a function that will provide that for you.

Finally, use query parameters.  Even though you are not dealing with user supplied data, query parameters have other redeeming qualities such as increased performance.

[Danny11223344]

In this case, oddly enough I need the date as a string.  I will remember the square bracket thing for using anything reserved.

Thanks for all the assistance with this.  It was driving me nuts.

[Dan_Bracuk]

Why do you need the date as a string?  You might be doing something very unwise.