data types, explicit casting and stray single quotes

[mmonclair]

I'm stumped on a query filter I'm trying to put together. Here's what I need it to do:
1. Run a query that populates a table with order information to be gathered for regular invoices.
2. A user can filter date ranges, location, client and payment status. The options in the state and client field are derived from the main query.
3. Multiple entries can be selected from the client and state fields.
4. The form handler assembles a WHERE statement from the form values passed over and inserts it, as a local variable, into a QoQ.
5. The output from the QoQ repopulates the table.

There are two areas where I'm running into trouble, in assembling the WHERE statement:
First: the date range. I have a From date, that defaults at 9/1/2011, around the time orders started being placed, and a To date, which defaults to Now(). I declared the variables containing them like so:

<cfparam name="fromdate" default="" type="date">

I then set it to the appropriate form value like so:
<cfset fromdate = DateFormat(#form.from_date#, 'yyyy-mm-dd')>

Same with the To date form value. Remember, the default value is Now().

I run a QoQ with a WHERE statement of
paid_date between #fromdate# AND #todate#

And I get this error:

Invalid parameter type.

The value cannot be converted to a date because it is not a simple value.Simple values are booleans, numbers, strings, and date-time values.

When I did this without setting a format or data cast, it reads the From date as a double and the To date as a date. I even went into my form and formatted the default values for each using CreateDate(). I thinkered with this any number of ways, but I still get a datatype mismatch. Even when I use values that are not the default values, I get the same thing. As for the form sending these over, the <cfinput> attributes for each are identical, except for the field name and default values.

The other issue I am having is in assembling a longer WHERE statement using an IN operator. The local variables containing the form values passed over can contain a list, as I've allowed multiple selections. For each field there is a variable where_n. If a list is passed over, it is set to where_n = ' AND field IN ('#form.field#')'. If all values are being searched for that field, it is set to ''. A series of variables containing the list, or nothing if the default (all values) is passed over. The final expression putting it all together looks like this:
<cfset search_string = 'paid_date between #fromdate# AND #todate#'&#where_1#&#where_2#&#where_3#>

Here's the problem: the WHERE statement generated drops an extra single quote where each appears in the expression. So, instead of:

(paid_date between '2011-09-01' AND '2012-04-01') AND client IN ('client1,client2,client3')

I get:

(paid_date between ''2011-09-01'' AND ''2012-04-01'') AND client IN (''client,client1,client2,client3'')

Which, of course, doesn't work. I tried tinkering with the placement of quotes in the tags setting the variables, but it's either two single quotes, or no quotes, which also doesn't work (I'm using PostgreSQL).

When I run this in my PostgreSQL Admin app, it works OK with the single quotes I expect, though it doesn't want to seem to find the field values from the list.

I apologize for such a lengthy post, which touches on multiple issues. The data mismatch is the biggest problem right now, I can always come back to the multiple selection and search parameters at a later time.

[arrgh]

And I get this error:

Invalid parameter type.

It's hard to follow all of that without seeing the values. Can you put together a small concrete example of the QoQ that's failing (with the values)? Btw, do you really have to use a QoQ? Couldn't you use INSERT ... SELECT.

the WHERE statement generated drops an extra single quote

Probably because you're using dynamic sql? CF always escapes single quotes. It guards against sql injection. Only way to avoid it is disable the protection altogether by using PreserveSingleQuotes.

field IN ('#form.field#')'

You need to quote each value inside the variable. ie

  `field IN ( 'x', 'y', 'z' )`

... by putting quotes around the whole thing you're searching for a single value instead ie

  `field =  'x,y,z' 

[arrgh]

Also what's the data type of the paid_date column?

[mmonclair]

paid_date is timestamp. I found that by using a direct query, rather than a QoQ, it is less sensitive to what it thinks the datatype is.

The form now works, as long as I only select one value for the fields where I had specified multiple values. What you say about the IN operator makes sense, but from what I've read elsewhere on this CF passes the multiple values over as a list. Do I need to use a ListToArray(), and then create a string from looping the resulting array? Examples I've seen of how to handle this in CF show simply using the form value as a list.

I'll give the ListToArray() idea a try and report back.

[arrgh]

I found that by using a direct query, rather than a QoQ, it is less sensitive to what it thinks the datatype is.

Good. A direct query offers the most control anyway.

What you say about the IN operator makes sense, but from what I've read elsewhere on this CF passes the multiple values over as a list.

If you're using cfqueryparam list="true" it does. ie

        WHERE field IN ( <cfqueryparam value="x,y,z" cfsqltype="(your type)" list="true"> )

[mmonclair]

Using cfqueryparam list='yes' did it. Thanks again!

So, to summarize what I should take away from this:
Issue #1: QoQ was not a good option for this type of search. It was better to just reuse the original query and add the specific search parameters defined in the form.

Issue #2: Explicitly setting the form variable as a list within the query is necessary to have it treated as a list.