9 Replies Latest reply on Dec 12, 2012 5:41 AM by Dan Bracuk

    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?

        • 1. Re: 9.0.2 cfc processing sql wrong?
          WolfShade Level 4

          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.

           

          ^_^

          • 2. Re: 9.0.2 cfc processing sql wrong?
            Danny11223344 Level 1

            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.

            • 3. Re: 9.0.2 cfc processing sql wrong?
              Adam Cameron. Level 5

              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. Re: 9.0.2 cfc processing sql wrong?
                meensi Level 2

                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.

                • 5. Re: 9.0.2 cfc processing sql wrong?
                  BKBK Adobe Community Professional & MVP

                  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.

                  • 6. Re: 9.0.2 cfc processing sql wrong?
                    Dan Bracuk Level 5

                    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.

                    • 7. Re: 9.0.2 cfc processing sql wrong?
                      Danny11223344 Level 1

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

                       

                      Thanks.

                      • 8. Re: 9.0.2 cfc processing sql wrong?
                        Danny11223344 Level 1

                        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.

                        • 9. Re: 9.0.2 cfc processing sql wrong?
                          Dan Bracuk Level 5

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