Skip to main content
D
Danny11223344
Participant
December 11, 2012
Answered

9.0.2 cfc processing sql wrong?

  • December 11, 2012
  • 4 replies
  • 1783 views

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?

    This topic has been closed for replies.
    Correct answer Adam Cameron.

    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

    4 replies

    D
    Dan_Bracuk
    Inspiring
    December 12, 2012

    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.

    D
    Danny11223344Author
    Participant
    December 12, 2012

    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.

    D
    Dan_Bracuk
    Inspiring
    December 12, 2012

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

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    December 12, 2012

    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.

    M
    meensi
    Inspiring
    December 12, 2012

    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.

    WolfShade
    WolfShade
    Legend
    December 11, 2012

    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.

    ^_^

    D
    Danny11223344Author
    Participant
    December 11, 2012

    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.

    A
    Adam Cameron.Correct answer
    Inspiring
    December 11, 2012

    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

    Firefly Community Gallery
    Adobe Firefly

    Remix with Firefly Community Gallery

    Thousands of free creations to fall in love with and remix in Firefly.

    Explore now
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices