Dunno, I could filter out every event for a month by just doing LIKE 2006-11-% and that's every event in November?
Just how I'm used to it.I'm going to give between a shot now.just seems to make my code a bit overly complex now, because I have to determine the last day of a month based on the month I'm in. - D _____ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Chris Phillips Sent: Wednesday, November 15, 2006 12:54 PM To: [email protected] Subject: Re: [Reactor for CF] Problem with isLike and DateTime fields (mysql5) Derek, This may be a crazy question. But, why would you use LIKE for date fields? That seems really odd to me. Why aren't you using the reactor equivalent of "WHERE (dt >= '2006-01-01' AND dt < '2007-01-01' )" I'm just curious, as I have never used LIKE on a datefield and never even considered it. Regards, Chris Phillips www.dealerpeak.com Senior Application Developer On 11/15/06, Patrick McElhaney <[EMAIL PROTECTED]> wrote: I think the maxlength should just be removed on LIKE queries. IMHO, it doesn't make sense to limit the length of the value in a LIKE clause. And it keeps causing problems in Reactor resulting in ugly workarounds. ( http://trac.reactorframework.org/reactor/search?q=maxlength <http://trac.reactorframework.org/reactor/search?q=maxlength&wiki=on&changes et=on&ticket=on> &wiki=on&changeset=on&ticket=on) Let's pretend dt is a character field and it really is only two characters wide. What would be the harm in trying this? > SELECT dt FROM table > > WHERE dt LIKE "2006-%" > The query will return no results. What's wrong with that? No results is what I expect if I search for a substring that's longer than the string itself could be. For a more realistic example, imagine you have a search form that allows people to search username or email address. The query might look like this. SELECT * FROM people WHERE username like '%arguments.searchstring%' or email like '%arguments.searchstring%' Now let's say username has a maxlength of 16 characters and email has maxlength of 255 characters. If this query was built with Reactor, I wouldn't be able to search for ' [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> '. Patrick On 11/15/06, Derek P. <[EMAIL PROTECTED]> wrote: > > > > > Hey, > > > > I need to be able to filter datetime fields with a SQL statement like this: > > > > SELECT dt FROM table > > WHERE dt LIKE "2006-%" > > > > Or something of that nature. I used the where.isLike() statement and loaded > the value 2006-11 (because is like will wrap %'s around them) and I get this > error: > > > > Message: The cause of this output exception was that: > coldfusion.tagext.sql.QueryParamTag$InvalidDataException: > Invalid data %2006-11% value exceeds MAXLENGTH setting 2.. > > > > If you check the code in the query it creates it sets the queryparam to > maxlength 2. is this a bug? Or what am I doing wrong? > > > > Thanks! > > - Derek > > > > > -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- > -- -- > Reactor for ColdFusion Mailing List > [email protected] > Archives at: > http://www.mail-archive.com/reactor%40doughughes.net/ > -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- > -- -- -- Patrick McElhaney 704.560.9117 -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Reactor for ColdFusion Mailing List [email protected] Archives at: http://www.mail-archive.com/reactor%40doughughes.net/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Reactor for ColdFusion Mailing List [email protected] Archives at: http://www.mail-archive.com/reactor%40doughughes.net/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Reactor for ColdFusion Mailing List [email protected] Archives at: http://www.mail-archive.com/reactor%40doughughes.net/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
