LiveDocs Comments - coldfusion - 6.1 - htmldocs - using_r6.htm

coldfusion/6.1/htmldocs/using_r6.htm

2006-10-19T12:49:16

Moments after my last post I discovered a solution. I tested it and it works great! I wrote a function to replace the QueryNew function. Feel free to use and modify to your needs:

SELECT *
FROM myQuery
WHERE 1 <> 1

coldfusion/6.1/htmldocs/using_r6.htm

Murrah — 2005-11-23T18:21:00

Correction to my last post. The DateFormat should use "m/d/yyyy" not "mm/dd/yyyy".

ie:
WHERE rmID = #rmID# and ArrDate = '#DateFormat(offerArriveDate,"m/d/yyyy")#' and DepDate = '#DateFormat(offerDepartDate,"m/d/yyyy")#'

coldfusion/6.1/htmldocs/using_r6.htm

Murrah — 2005-11-22T23:08:24

Re: dates in Query of Query. Querying an MS_Access db then querying the results.

Using in the QoQ still gave "unsupported type conversion error".

The following worked:

MS-Access query:

SELECT rmID, BookingDate, Format(ArriveDate) as ArrDate, Format(DepartDate) as DepDate
FROM RoomBookings
WHERE enqID = #url.enqID# and Status = 1

QoQ:

Select BookingDate from BookedEnquiries
WHERE rmID = #rmID# and ArrDate = '#DateFormat(offerArriveDate,"mm/dd/yyyy")#' and DepDate = '#DateFormat(offerDepartDate,"mm/dd/yyyy")#'

I assume using Format() in the original sql and single quotes in the QoQ means we are comparing string to string. If you needed to check for date ranges you may need to convert the original dates to some other format (eg yyyymmdd) and compare to that. I didnt need that so I havent tried it.

coldfusion/6.1/htmldocs/using_r6.htm

cardinalweb — 2005-03-31T11:58:29

just use the MaxRows attribute of the CFQuery tag instead of doing Top.

coldfusion/6.1/htmldocs/using_r6.htm

jrunrandy — 2005-02-24T06:36:07

What version are you moving from? I talked to a developer and he said CF hasn't supported "top" since the base CFMX release, but I can't find any documentation anywhere that says CF ever supported this keyword.

In any case, I'm sorry to say that CFMX 7 does not support "top."

coldfusion/6.1/htmldocs/using_r6.htm

prayank — 2005-02-24T03:20:53

sanaullah could you please tell me which version of CF are you using?

coldfusion/6.1/htmldocs/using_r6.htm

sanaullah — 2005-02-18T02:29:10

I think there is a bug in query of query if we try this

select top 20 cms_id,cms_name from application.contents

the error comes up that [ from keyword is missing from select statment]

coldfusion/6.1/htmldocs/using_r6.htm

Kurt Eye — 2005-01-26T12:13:52

I take it back. That doesn't work either.

coldfusion/6.1/htmldocs/using_r6.htm

Kurt Eye — 2005-01-26T11:36:17

I've found that the ToStr function fixes the datatyping issue for me, thusly:

temp = querysetcell(entry, 'programnumber', tostr(your_variable)));

...where "your_variable" is a value that is generally numeric but
occassionally has an alphanumeric value. With this solution I'm no
longer getting a "Can't convert string to java type LONG" error in my
Query of Queries when an alphanumeric crops up.

coldfusion/6.1/htmldocs/using_r6.htm

jrunrandy — 2005-01-19T10:14:29

I probably shouldn't have said "and possible workarounds..." in my comment. Clearly, that's the kind of stuff we want on LiveDocs pages.

The point I was trying to make was that the Forums get way more traffic than LiveDocs, so you're more apt to get a faster response by posting questions to the forums. However, we just added a feature to LiveDocs that allows you to get e-mail notification when a new comment is added to a page, so maybe LiveDocs traffic will increase.

Also, have you tried the new Knowledge Base feature on the Support Center? http://www.macromedia.com/support/coldfusion I've found it to be pretty helpful.

coldfusion/6.1/htmldocs/using_r6.htm

2005-01-19T06:12:59

Preventing the posting in LiveDocs of work-arounds to bugs that have no fix available is in my opinion inappropriate. Telling me to not only find out about an issue in LiveDocs, but also go find the work around info in "forums" is unhelpful to me and likely to waste hours and more likely days. Refactoring and condensing the work arounds as the tech writer did here is very helpful - however, whatever fix or work around Tech support supposedly would help me enact "for free" unless they decide to call the bugs "enhanced functionality of MX" should be described here in Live Docs. Thank you to MM for employing the writer to condense the comments for us. When mentioning joining Blackstone beta, a link would be helpful. Getting lost in the MM site is quite easy and unproductive for most programmers.

coldfusion/6.1/htmldocs/using_r6.htm

jrunrandy — 2004-09-09T10:53:13

bjquinn: I can understand that you're frustrated with Query of queries functionality. However, I hope that you'll understand that when you deal with LiveDocs, you're dealing with documentation; not development, not product marketing, not support. You can flame me all you want, but I'm still a Tech Writer, not a developer. Believe me when I tell you that I have lobbied development on your behalf. The Query of Queries enhancements in the next release of ColdFusion (Blackstone) have tried to address most of the comments made on this page.

I suggest signing up for the Blackstone Beta to ensure that the Query of Queries fixes/enhancements meet your needs. You can sign up from http://www.macromedia.com/support/programs/beta.html. Comments and complaints made to the Blackstone Beta forum will get a lot more attention than anything you post to this page.

I will leave your comment in the page as a show of good faith. However, if this page once again gets to the point that it's not functioning as documentation, I'll consolidate the comments again.

coldfusion/6.1/htmldocs/using_r6.htm

bjquinn — 2004-09-09T09:53:09

I am upset with the way that Macromedia is dealing with this issue. I think the very fact that someone at Macromedia had to consolidate dozens of complaints about the query of queries problem to clean up the documentation page and to ask us to please quit complaining about its non-functionality on this page (there's always the forums or you can pay Macromedia to fix it!) - that should prove to them how annoying this issue is. The fact that they refuse to fix it until the next version of Cold Fusion (no hotfix?!) is rediculous. I've been having more and more of these types of issues lately, including one that I've been dealing with Macromedia tech support with on ALL YEAR - they need to realize they can't treat major problems this way or we're all eventually going to go somewhere else. Macromedia is trying to treat us like Microsoft does, but unfortunately for Macromedia, THEY have competition. Go PHP.

coldfusion/6.1/htmldocs/using_r6.htm

2004-09-02T09:07:51

I had a similar problem where a string of the form "11000000001010?10010000" caused an attempt to convert it to double. The query was built using QuerySetCell() in the usual fashion and the CFDUMP showed the contents as advertised. However, the subsequent QofQ bombed. I wanted ALL the columns to be strings in terms of data type. My workaround was to make sure that the FIRST record of my hand built query had data that forced the column type to string i.e.

etc.

Apparently the FIRST record sets the column type. Then at the end I effectively removed my "dummy" record thus:

select * from myQ where column1 != 'xxxxxxx'

I think that QueryNew(columnlist) should be modified to accept an optional second parameter (for backwards code compatibility) thus:

QueryNew(columnlist [,columnTypeList])

which would allow passing in not only the column names but also corresponding column type specificers.

coldfusion/6.1/htmldocs/using_r6.htm

jrunrandy — 2004-07-28T04:21:12

Query of Queries feedback summary
Because of the large number of comments that this topic has elicited, we are consolidating similar descriptions into this comment, along with a response for each.

Overview: Prior to ColdFusion MX, columns in query of queries were typeless. However, ColdFusion MX uses a more JDBC-like behavior and has the concept of data types in query columns. This change in behavior may require changes to CF 5 applications, particularly for manually constructed queries. Additionally, some of the side-effects of this change have been been classified as bugs and will be fixed in the next release of ColdFusion MX.

Please continue to add documentation-related comments to this page. However, if your comment relates to undesired behavior in the ColdFusion MX query of queries functionality, please do not add comments to this page. Instead, use one of the following alternatives:
* If you want to discuss the query of queries feature and possible workarounds with other members of the ColdFusion community, use the online forums: http://webforums.macromedia.com/coldfusion
* If query of queries behavior is seriously affecting your business, you should request Technical Support by calling the Macromedia Call Center at 1-800-945-9120 and opening a paid support incident. If your issue is found to be a bug, there is no charge for the incident.

Randy Nielsen
ColdFusion Documentation Manager
Macromedia, Inc.

Date: 9/22/03 By: TheoPeters
Summary: This developer noted that there is no way to tell QueryNew what the datatype of each column is, so if your data has any string values that look like a number or a date you will get an error.
Status: Fixed in next release of ColdFusion.
Note: This comment is duplicated by the following comments:
* 1/21/04 by no screen name
* 3/18/04 by Jens
* 3/23/04 by clomvardias
* 6/25/04 by no screen name
* 7/8/04 by brent.nicholas
* 7/23/04 by Bob Sacamano
* 7/24/04 by KkkMaxxX
* 7/25/04 by igneous
******
Date: 10/2/2003 By no screen name
Summary: This developer ran into a situation where a null value was returned and CF threw a [cannot convert the value "" to a boolean] error.
Status: To fix this, modify the QofQ to include "and foo is not null" in the WHERE clause as explained on http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/using_35.htm
******
Date: 12/17/2003 By: HolgerL
Summary: This developer had a situation in which, in a UNION, ColdFusion seemed to be casting the same field types to different data types.
Status: We have been unable to duplicate this behavior.
******
Date: 1/2/2004 By: jsevlie
Summary: This developer suggested a workaround that hard-wires all fields to strings:

instead of

******
Date: 1/2/2004 By: jwcb
Summary: This developer had a question with regard to the CF versions that support QofQ.
Status: HalL responded by saying the QofQ is supported in ColdFusion 5, ColdFusion MX, and ColdFusion MX 6.1 noting that the SQL syntax support was enhanced between 5.0 and MX, but all three releases support basic features.
******
Date: 1/16/2004 By: Gary M G
Summary: This developer noted that in some cases, ColdFusion thought an Int was a BigInt.
Status: He was able to solve this by using a SQL Cast function to convert the Int to a BigInt: Cast(mydata as BigInt).
******
Date: 2/12/04 By: Brian428
Summary: This developer described a situation in which QofQ aggressively treats numeric data (in the format xxx-xx-xxxx) as a date.
Status: HalL entered this as bug 54252 and it is fixed in the next release of ColdFusion.
******
Date: 2/25/04 By: mindtrap
Summary: This developer described a situation when using QofQ where one of the columns was a date and the recordset had come from an Oracle database and CF returned an invalid type conversion error.
Status: On ColdFusion MX only, using cfqueryparam within the QoQ resolved the situation. It is still the behavior in ColdFusion 5.
******
Date: 3/16/04 By: Funke
Summary: This developer described a workaround when creating a UNION of three queries. The type mismatch only occurred when one of the statements returned 0 rows. The workaround is to check for recordcount=0 before creating the QofQ.
******
Date: 4/2/2004 By: no screen name
Summary: This developer described a workaround by creating a QueryOfQuery table in the database. The table has a Char field, a Number field, and a Date field. Then query this empty table to return an empty query with the column names and types: SELECT StringField as IndividualID, StringField as Email, DateField as CommitDate FROM QueryOfQuery WHERE 1 = 0
******
Date: 6/18/2004 By: GAlanShepard
Summary: This developer described a situation related to the case-sensitivity of QofQ. Status: Query of queries is case-sensitive, as described on http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/using_27.htm
******
Again, please continue to add documentation-related comments to this page. However, if your comment relates to undesired behavior in the ColdFusion MX query of queries functionality, please do not add comments to this page. Instead, use one of the alternatives listed at the top of this comment.