cfinvokeargument and trying to pass dates to a cfc/query

[brianthedrummer]

Hi

I have a situation where passing a date along to a cfc method (which runs a basic SQL select query) fails. if i hardcode the datevalue into the cfc file or hardcode the select statement into the page with the , it works. so something in the passing of the argument is wrong but i cannot tell what. i have tried the preservesinglequotes() function to no avail. Attached is a sample.

error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2008-02-29 21:23:17''

it seems as though if I set a variable (a whereclause) variable with a date in it, the sql doesnt run right. I cannot say "select * where #whereclause#" if the whereclause has a date comparison value in it (where 'ending event date greater than now' for example). what is the right way for me to pass this datetime comparison command/argument in my cfm page to the cfc method? My best guess not being a software engineer is that the MYSQL database doesnt recognize the date ts{} if it's encapsulated in a larger string variable. argh.

Thanks for the help smart people!

Brina

[-__cfSearching__-]

That seems like an overcomplication. Why not just pass in an optional date argument and construct the where clause inside the function query?

<cfquery ...>
SELECT ColumnNames FROM Table
<cfif structKeyExists(arguments, "theDate")>
WHERE Eeventdate >= <cfqueryparam value="#arguments.theDate#" cfsqltype="cf_sql_date">
</cfif>
</cfquery>

To answer your question, now() returns a date and time in odbc format. This format contains single quotes. As a security measure, CF doubles single quotes inside variables. So when you use this

WHERE #whereclause#

the odbc format is distorted, causing a syntax error. However, using the preserveSingleQuotes function should fix that. Though again, it would be cleaner to construct the where clause inside the function.

WHERE #preserveSingleQuotes(whereclause)#

[brianthedrummer]

thanks for the reply.

putting preservesinglequotes() inside the cffunction on the whereclause did not work.

the reason i dont want to do it your way is because the method may not always have a date comparison as a whereclause. it may have something like 'where id=100' and no dateclause. so i need to be able to have a pretty dynamic whereclause. ideas?

thanks,
Brian

[-__cfSearching__-]

I tested it and it works fine. Though I noticed your query is cached. Perhaps that is causing a problem. Try removing that temporarily.

You can still construct the where clause dynamically with a variable number of arguments. Just use WHERE 0 = 0 and append extra conditions based on which arguments are defined.

SELECT Columns
FROM Table
WHERE 0 = 0
<cfif structKeyExists(arguments, "theDate")>
AND Eeventdate >= <cfqueryparam value="#arguments.theDate#" cfsqltype="cf_sql_date">
</cfif>
<cfif structKeyExists(arguments, "id")>
AND ID = <cfqueryparam value="#arguments.id#" cfsqltype="cf_sql_integer">
</cfif>
....

I would also suggest reading up on cfcomponent and cffunction. It is important to scope function variables properly using "var". This includes query names too. Proper scoping prevents threading problems for cfc's that are stored in a shared scope. Also, it is good to get into the habit of including function return types, argument types, etcetera. It improves readability and also provides type checking.

[Dan_Bracuk]

quote:

---

Originally posted by: brianthedrummer
thanks for the reply.

putting preservesinglequotes() inside the cffunction on the whereclause did not work.

the reason i dont want to do it your way is because the method may not always have a date comparison as a whereclause. it may have something like 'where id=100' and no dateclause. so i need to be able to have a pretty dynamic whereclause. ideas?

thanks,
Brian

---

Does your query run if you pass that argument? The one thing I noticed in your code is that your cfargument does not have a type attribute. That may or may not matter.

[brianthedrummer]

hi

thanks for the note. it worked when i tried it this way. not quite what i wanted but i found a way to make it work.i was already doing the 0=0 and appending extra statements. again, from what i can tell, the issue is when you send a date var through over to the method from the cfm page, it seems to not work correctly. your version doesnt pass the date to the method and so it works because the date setting happens in the CFC file.

thanks,
Brian

[-__cfSearching__-]

Not exactly. When you use optional arguments a date is passed to the function. However it is evaluated differently. The single quotes are not doubled, so it does not cause the syntax error that occured in your original example.

In any case, constructing the where clause inside the function produces cleaner, more managable code IMO. Constructing the sql outside the function sort of defeats one of the main purposes of using functions and cfc's: encapsulation.