Adobe Community: Oh functions, how I loathe thee...

Currently Being Moderated

1. insuractive,

Nov 3, 2011 11:23 AM in reply to BreakawayPaul

Report

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

|

Mark as:

Currently Being Moderated

2. BreakawayPaul,

Nov 3, 2011 11:42 AM in reply to insuractive

Report

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.

|

Mark as:

Currently Being Moderated

3. Dan Bracuk,

Nov 3, 2011 11:43 AM in reply to BreakawayPaul

Report

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.

|

Mark as:

Currently Being Moderated

4. Dan Bracuk,

Nov 3, 2011 11:45 AM in reply to BreakawayPaul

Report

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.

|

Mark as:

Currently Being Moderated

5. -==cfSearching==-,

Nov 3, 2011 11:48 AM in reply to BreakawayPaul

Report

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

|

Mark as:

Currently Being Moderated

6. -==cfSearching==-,

Nov 3, 2011 11:51 AM in reply to BreakawayPaul

Report

Then I changed the db from date/time to text

If the column stores dates - leave it as a date/time.

|

Mark as:

Currently Being Moderated

7. BreakawayPaul,

Nov 3, 2011 12:16 PM in reply to -==cfSearching==-

Report

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!

|

Mark as:

Currently Being Moderated

8. insuractive,

Nov 3, 2011 12:19 PM in reply to BreakawayPaul

Report

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>

|

Mark as:

Currently Being Moderated

9. -==cfSearching==-,

Nov 3, 2011 12:24 PM in reply to BreakawayPaul

Report

>I changed the column names and the query to match.

I am still betting on reserved word issues. Which column(s) did you change and what is your current query? (And you still did not tell us which database are you using. Hint, hint...)

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

It would not. Using a date string would not that error. It is just a bad practice.

Leigh

|

Mark as:

Currently Being Moderated

10. BreakawayPaul,

Nov 3, 2011 12:28 PM in reply to insuractive

Report

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.

|

Mark as:

Currently Being Moderated

11. -==cfSearching==-,

Nov 3, 2011 12:33 PM in reply to BreakawayPaul

Report

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

Okay, then Timestamp and User are almost certainly reserved words. So Michael's suggestion of using brackets Re: Oh functions, how I loathe thee... should do the trick. (Though renaming is preferable.) If that does not work, can you post your current cfquery?

|

Mark as:

Currently Being Moderated

12. -==cfSearching==-,

Nov 3, 2011 12:37 PM in reply to BreakawayPaul

Report

<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 ...

|

Mark as:

Currently Being Moderated

13. insuractive,

Nov 3, 2011 12:38 PM in reply to -==cfSearching==-

Report

Ah, good catch. Thanks for the correction Leigh

|

Mark as:

Currently Being Moderated

14. -==cfSearching==-,

Nov 3, 2011 12:42 PM in reply to BreakawayPaul

Report

EDIT: Actually, the error message has changed:
Data type mismatch in criteria expression.

Did you remember to change the data type of "timestamp" back to datetime?

|

Mark as:

Currently Being Moderated

15. BreakawayPaul,

Nov 3, 2011 12:47 PM in reply to -==cfSearching==-

Report

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 ...

That did the trick! It works now! Woohoo!

Oh and FYI, the current column names are action_time, user_name, and action_done.

Thanks everyone!

|

Mark as:

Currently Being Moderated

16. -==cfSearching==-,

Nov 3, 2011 12:51 PM in reply to BreakawayPaul

Report

Great news!

|

Mark as:

Currently Being Moderated

17. insuractive,

ColdFusion

Oh functions, how I loathe thee...

Nov 3, 2011 11:11 AM

More Like This

Bookmarked By (0)

Answers + Points = Status