Skip to main content
BreakawayPaul
BreakawayPaul
Inspiring
November 3, 2011
Answered

Oh functions, how I loathe thee...

  • November 3, 2011
  • 3 replies
  • 3388 views

I have a form that submits to another page, and on the target page I'm trying to add a function to log the activity.

So I did this:

Target page:

<cfinvoke component="actionlog" method="additem">

    <cfinvokeargument name="details" value="Edited user: #FORM.email#">

</cfinvoke>

And the cfc:

<cfcomponent>

<cffunction name="additem" access="public">

<cfargument name="details" type="string" required="yes">

<cfquery name="addlog" datasource="hepoffice">

INSERT INTO actions

        ( timestamp

        , user

        , action )

VALUES

        ( '#DateFormat(Now(), "yyyy-mm-dd|hh:mm:ss")#'

        , '#GetAuthUser()#'

        , <cfqueryparam value="#arguments.details#" cfsqltype="cf_sql_varchar"> )

</cfquery>

</cffunction>

</cfcomponent>

I've done this sort of thing in the past, and it's worked, but not I'm getting an error:

Error Executing Database Query.

Syntax error in INSERT INTO statement.

The error occurred in D:\Inetpub\staffnet_test\hep\admin\actionlog.cfc: line 12

Called from D:\Inetpub\staffnet_test\hep\admin\hepadmin.cfm: line 87

Called from D:\Inetpub\staffnet_test\hep\admin\hepadmin.cfm: line 75

Called from D:\Inetpub\staffnet_test\hep\admin\hepadmin.cfm: line 64

Called from D:\Inetpub\staffnet_test\hep\admin\hepadmin.cfm: line 1

Called from D:\Inetpub\staffnet_test\hep\admin\hepadmin.cfm: line 1

Called from D:\Inetpub\staffnet_test\hep\admin\actionlog.cfc: line 12

Called from D:\Inetpub\staffnet_test\hep\admin\hepadmin.cfm: line 87

Called from D:\Inetpub\staffnet_test\hep\admin\hepadmin.cfm: line 75

Called from D:\Inetpub\staffnet_test\hep\admin\hepadmin.cfm: line 64

Called from D:\Inetpub\staffnet_test\hep\admin\hepadmin.cfm: line 1

Called from D:\Inetpub\staffnet_test\hep\admin\hepadmin.cfm: line 1

10 :         ( '#DateFormat(Now(), "yyyy-mm-dd|hh:mm:ss")#'

11 :         , '#GetAuthUser()#'

12 :         , <cfqueryparam value="#arguments.details#" cfsqltype="cf_sql_varchar"> )

13 : </cfquery>

14 : </cffunction>

What am I doing wrong?

    This topic has been closed for replies.
    Correct answer -__cfSearching__-

    Nope.  It seems to be hung up on the last line, where it plugs in the details.  It's like the details aren't making it to the function.

    I tried pulling the cfinvoke and pasting the query there, but got the same error.

    EDIT: Actually, the error message has changed:

    Data type mismatch in criteria expression.

    Odd since the column type is text and I'm using varchar.

    Unfortunately, I'm stuck using Access for the db.


    <cfqueryparam cfsqltype="cf_sql_datetime" value="#now()#">

    BTW: The correct cfsqltype is cf_sql_timestamp. There is no cf_sql_datetime. (We can probably thank the jdbc specs for that ..) Though it would certainly be more intuitive ...

    3 replies

    -
    -__cfSearching__-
    Inspiring
    November 3, 2011

    INSERT INTO actions ( timestamp, user, action ) 

    I would bet the problem is one or more of your column names is a reserved word in your database. TIMESTAMP and USER are likely suspects. In which case your options are to rename the columns or escape them (syntax is database specific). (BTW: It is always a good thing to mention your database type when queries are involved)

    >  '#DateFormat(Now(), "yyyy-mm-dd|hh:mm:ss")#'

    >   Does date format support a time mask?

    s Micheal mentioned, DateFormat does not fully support time masks. Not surprising given the function name. But be aware "m" only represents month number, not minutes. That said, if "timestamp" is a datetime column it is best to use datetime objects, not strings. Lose the quotes and dateFormat() and just insert #now()#

    -Leigh

    BreakawayPaul
    BreakawayPaulAuthor
    Inspiring
    November 3, 2011

    The reserved words was something I should have thought of, because I had that exact problem a few weeks ago.

    I changed the column names and the query to match.  No dice.

    I changed the timestamp to just use #now()#, the wrapped each query item in cfqueryparam.  Still no dice.

    I'm stumped!

    I
    insuractive
    Inspiring
    November 3, 2011

    Does this work:

    <cfcomponent>

    <cffunction name="additem" access="public">

    <cfargument name="details" type="string" required="yes">

    <cfquery name="addlog" datasource="hepoffice">

    INSERT INTO [actions]

            ( [timestamp]

            , [user]

            , [action] )

    VALUES

            ( <cfqueryparam cfsqltype="cf_sql_datetime" value="#now()#">
             , <cfqueryparam value="#GetAuthUser()#" cfsqltype="cf_sql_varchar">
             , <cfqueryparam value="#arguments.details#" cfsqltype="cf_sql_varchar"> )

    </cfquery>

    </cffunction>

    </cfcomponent>

    D
    Dan_Bracuk
    Inspiring
    November 3, 2011

    If GetAuthUser returns a string, you need to quote it in your query.  Better yet, use cfqueryparam to escape apostrophes in that string.

    Also, there are better ways to do the timestamp.  I believe the best is to use the database function that gives you the current timestamp.  If you insist on using ColdFusion for that, don't format now(), just send it.  Now() returns a timestamp.  Use cfqueryparam for that as well.

    I
    insuractive
    Inspiring
    November 3, 2011

    turning on robust debugging (SQL) when encountering these sort of errors will give you a better idea of what went wrong - especially the part where it generates the SQL statement as text so you can double check your syntax.  Does date format support a time mask?  I thought you needed to use TimeFormat for that, but it might be different in CF9

    Hope that helps,

    -Michael

    BreakawayPaul
    BreakawayPaulAuthor
    Inspiring
    November 3, 2011

    Hey that's a good idea!  I just took the time portion out and got the same error.  Then I changed the db from date/time to text.  Same error.

    I turned robust debugging on, but it doesn't seem to show data regarding this function.  Shows just about everything else though.

    D
    Dan_Bracuk
    Inspiring
    November 3, 2011

    Regarding:

    Then I changed the db from date/time to text

    Change it back before you forget.  Storing dates and times as text is a bad idea.

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