Greg, what database are you using? One of the suggestions I was going to make (which may have helped you if you'd not found this) was to suggest that you use a tool to see what's being passed to the database--from the database's perspective, not CFs. Both the debugging info you refer to, and the tricks for getting CF to output the SQL its building, all would show just what CF is passing "on the surface". It wouldn't show the actual specific bytestream of SQL statements being sent.
If you were using SQL Server, then the Profiler in that tool would indeed show exactly what SQL Server is being asked to do by CF, and it may well have shown that something was odd about the date strings being used. Maybe not, but just want to mention that it's another alternative to consider. Other DBMS's have similar (though perhaps different) things. For instance, MySQL has a mechanism to log all SQL statements it processes. It's not the friendly interface of Profiler, but the data is what matters. Look into adding the "log" directive in my.ini. /charlie Member, Adobe Community Experts program www.carehart.org -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Greg Drake Sent: Tuesday, May 22, 2007 11:22 AM To: discussion@acfug.org Subject: Re: [ACFUG Discuss] Bad CFQuery Results I believe I have this one. It has to do with the <cfqueryparam cfsqltype=""> attribute for dates. I've been using "cf_sql_date" this whole time. Guess I should have posted the code after all. In the LiveDocs, cf_sql_date isn't even shown as something that should be used with MSSQL. I guess I have to use cf_sql_timestamp, even though timestamps are not the same as datetimes in T-SQL. I still don't understand what was going on there exactly, since it was giving me the correct dates back in the debugger, but I'm not going to complain with success at this point. Thanks for the suggestions everybody. On 5/22/07, Gurevich, Gerry (NIH/NIEHS) [C] <[EMAIL PROTECTED]> wrote: > Just a blind stab in the dark here but... > > Are you returning your results in the same request as the cfquery? I > have inherited code that stuffs queries into session or even > application scope and then you may have all kinds of things happening > that cause you to output something different from what you thought you were sending. > > I tend to agree that using the debug feature to get a copy of the > query is the way to go. If you always get what is expected from the > debug query when you stick run it directly against the database. And > if your results on the screen are wrong. Then there must be something > wrong with the processing of the query when you do your output. My > recommendation above is only one of the things that could be wrong. > > It's also possible that you are processing the output in some way and > not outputting every single field. > > Good luck. > > ------------------------------------ > Gerry Gurevich > Application Development > NIEHS ITSS Contractor > Lockheed Martin Information Technology > 919-361-5444 ext 311 > > > -----Original Message----- > From: Greg Drake [mailto:[EMAIL PROTECTED] > Sent: Monday, May 21, 2007 6:08 PM > To: discussion@acfug.org > Subject: Re: [ACFUG Discuss] Bad CFQuery Results > > Thanks Dusty, but I'm afraid I already tried that. It all came out how > I expected it to. I ran the results directly through the database and > it came out fine. For that matter, I ran the results back through the > <cfquery> tag and it came out fine. > > Mischa, Debugging was a good idea. I never think of using that feature. > > However, it seems to be returning exactly what I would expect as well. > Of course this time the <cfqueryparam> tags are all replaced with > question marks and defined at the bottom. I still can't figure out > what I'm missing here. > > I was really hoping that it was a common thing that I'd just never > heard of before. > > On 5/21/07, Dusty Hale <[EMAIL PROTECTED]> wrote: > > You may have tried this already or perhaps the query is rather > > complex > (it > > sounds like) but: > > > > 1. make a copy of it. > > 2. remove all the cfqueryparam tags and replace with normal > > variables > or > > values. > > 3. replace the cfquery tags with <cfouput><pre>code > > here</pre><cfabort></cfouput> 4. output the actual query text so you > > can see what is really being > queried > > and run the query out put manually to see if you get the results > you're > > looking for. > > > > Not sure if this is helpful for your situation or not but thought it > > wouldn't hurt to suggest. Best of luck with the issue. > > > > Dusty > > > > -----Original Message----- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Greg > > Drake > > Sent: Monday, May 21, 2007 4:20 PM > > To: discussion@acfug.org > > Subject: [ACFUG Discuss] Bad CFQuery Results > > > > Has anyone else ever run into a situation where <cfquery> does not > return > > the same records as a direct query on the database? > > > > I have a rather large query joining 5 tables and 2 derived tables. > > The 'where' clause is cluttered with dynamically loaded conditions. > > There > are > > some <cfqueryparam> tags that keep me from easily inserting the > > query > as a > > single string. The 'order by' clause is handled by a custom tag. > > > > The point is this: under certain circumstances the query does not > respond > > properly, but I am sure that the query should be making it to the > <cfquery> > > tag properly. If the query is run directly against the database it > works > > fine, and if it is run with certain options it works fine, but other > options > > make it return a result set that is too small. > > It doesn't through an error, though, so If not for careful testing > > it wouldn't have even been noticed. > > > > I am using ColdFusion 7 and SQL Server. > > > > Sorry for not posting any code, but I'm thinking you have either run > into > > this before or you haven't. I can't find anything online, perhaps > because > > the issue is difficult to describe. > > > > > > ------------------------------------------------------------- > > Annual Sponsor FigLeaf Software - http://www.figleaf.com > > > > To unsubscribe from this list, manage your profile @ > > http://www.acfug.org?fa=login.edituserform > > > > For more info, see http://www.acfug.org/mailinglists Archive @ > > http://www.mail-archive.com/discussion%40acfug.org/ > > List hosted by http://www.fusionlink.com > > ------------------------------------------------------------- > > > > > > > > > > > > > > ------------------------------------------------------------- > > Annual Sponsor FigLeaf Software - http://www.figleaf.com > > > > To unsubscribe from this list, manage your profile @ > > http://www.acfug.org?fa=login.edituserform > > > > For more info, see http://www.acfug.org/mailinglists Archive @ > > http://www.mail-archive.com/discussion%40acfug.org/ > > List hosted by http://www.fusionlink.com > > ------------------------------------------------------------- > > > > > > > > > > > ------------------------------------------------------------- > Annual Sponsor FigLeaf Software - http://www.figleaf.com > > To unsubscribe from this list, manage your profile @ > http://www.acfug.org?fa=login.edituserform > > For more info, see http://www.acfug.org/mailinglists Archive @ > http://www.mail-archive.com/discussion%40acfug.org/ > List hosted by http://www.fusionlink.com > ------------------------------------------------------------- > > > > > ------------------------------------------------------------- > Annual Sponsor FigLeaf Software - http://www.figleaf.com > > To unsubscribe from this list, manage your profile @ > http://www.acfug.org?falogin.edituserform > > For more info, see http://www.acfug.org/mailinglists Archive @ > http://www.mail-archive.com/discussion%40acfug.org/ > List hosted by http://www.fusionlink.com > ------------------------------------------------------------- > > > > ------------------------------------------------------------- Annual Sponsor FigLeaf Software - http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com ------------------------------------------------------------- ------------------------------------------------------------- Annual Sponsor FigLeaf Software - http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -------------------------------------------------------------
