A query contained within a query

[MikerRoo]

Probably not a good idea from a coding design / maintainability standpoint.

If you really need to package queries, probably best to use an explicit structure.

[MikerRoo]

Well, hey.

If it works for you, use it.

Just document it well -- so that you won't be scratching your head too much in
a year or two. :P

Also, since "coldfusion.sql" is not an Ansi SQL column type. Macromedia
might bust this approach in future releases.

MTC,
-- MikeR

[_jt]

Performing a basic test, I've found that you can store a query within a
query

<cfset QuerySetCell(query2, "ColumnName", query1, rowNumber)>

<cfdump var="#query2#">

Okay, so it's possible.... but is it a good idea? Any thoughts?

[_jt]

Thanks for the feedback. Yes, I was thinking about that side of it...

It's just that queries have some niceties (like CurrentRow) that structures
and arrays don't. I was hoping for a solution that wouldn't require explict
loops and counters.

"MikerRoo" <webforumsuser@macromedia.com> wrote in message
news:dbeslh$ncg$1@forums.macromedia.com...

> Probably not a good idea from a coding design / maintainability

standpoint.

>
> If you really need to package queries, probably best to use an explicit

structure.

>

[MikerRoo]

How did you determine that a query was a subclass of java.util.Map?

Query variables report that they are derived from java.sql in their meta data.
This is also what one would expect.

Anyway, I know of no ANSI SQL standard that supports complex structures as a
column type (except as binary data or a string full of XML). But, I'm not up
with the latest. I'll study it when MS or Oracle implements it.

[_jt]

> Also, since "coldfusion.sql" is not an Ansi SQL column type. Macromedia

> might bust this approach in future releases.

Hmm.. good question. So the general issue is .... will CF continue to
support the usage of "complex" objects in a query. I would think "yes" and
here is why.... MX is based on java and the query object is essentially a
subclass of a java.util.Map which does support the storage of complex
objects (i.e. another Map or query object). The JDBC SQL3 standards also
support the generic setObject()/getObject() methods, which would cover a
cfquery "Object". Maybe I'm wrong here, but I'm thinking future support is a
good possibility ;-)

I don't know about the most recent ANSI SQL standards though. Do you know
anything about what complex objects are part of the latest standards?

[_jt]

> How did you determine that a query was a subclass of java.util.Map?

> Query variables report that they are derived from java.sql in their meta

data.

> This is also what one would expect.

I said that it was "essentially" a sub-class of this java.util.Map. Bad
choice of wording ;-) I didn't mean subclass in the strict sense. I'm
referring to the fact that it CF can automatically cast a query object to a
java.util.Map (from livedocs). I haven't checked the actual class structure
on MX 7 to determine if it does or doesn't implement Map. I believe
structures definitely are a subclass of java.util.Map and arrays a subclass
of java.util.List.

> Anyway, I know of no ANSI SQL standard that supports complex structures

as a

> column type (except as binary data or a string full of XML). But, I'm

not up

> with the latest. I'll study it when MS or Oracle implements it.

Neither am I, but as long as it implements some complex (non string/binary)
object (array, etc), which I thought SQL3 does. I'll have to check it.

[_jt]

> Neither am I, but as long as it implements some complex (non
string/binary)

> object (array, etc), which I thought SQL3 does. I'll have to check it.

From what I've read, SQL3 and SQL2003 contain non-scalar data types (arrays,
collections, etc). I don't know how widely they are used, but they seem to
be part of the specs. So I'm still thinking that it's likely that query
objects will continue to support storing "complex" objects (CF term) in the
future.

Anyone else have any thoughts? Is my logic horribly flawed?

"_jt" <fake_email@nospam.not> wrote in message
news:dbf8mi$83t$1@forums.macromedia.com...

> > How did you determine that a query was a subclass of java.util.Map?

>

> > Query variables report that they are derived from java.sql in their

meta

> data.

> > This is also what one would expect.

>
> I said that it was "essentially" a sub-class of this java.util.Map. Bad
> choice of wording ;-) I didn't mean subclass in the strict sense. I'm
> referring to the fact that it CF can automatically cast a query object to

a

> java.util.Map (from livedocs). I haven't checked the actual class

structure

> on MX 7 to determine if it does or doesn't implement Map. I believe
> structures definitely are a subclass of java.util.Map and arrays a

subclass

> of java.util.List.
>

> > Anyway, I know of no ANSI SQL standard that supports complex structures

> as a

> > column type (except as binary data or a string full of XML). But, I'm

> not up

> > with the latest. I'll study it when MS or Oracle implements it.

>
>
>