query recordsets arent structs?

I think im losing it...arent query recordsets structures? or are the similar to structures in the way we can get into their data and return values? ...tony Tony Weeg Senior Web Developer UnCertified Advanced ColdFusion Developer Information System Design Navtrak, Inc. Mobile workforce monitoring, mapping & reporting www.navtrak.net 410.548.2337

I sure hope they are like arrays, since the order of the records can be just as important as the record data itself I think im losing it...arent query recordsets structures? or are the similar to structures in the way we can get into their data and return values? ...tony Tony Weeg Senior Web Developer UnCertified Advanced ColdFusion Developer Information System Design Navtrak, Inc. Mobile workforce monitoring, mapping & reporting www.navtrak.net 410.548.2337

Johannes, Although I know some programmers rely on the order that records are returned in, Dr. Codd, the creator of relational databases (who just recently died), specifically prohibits the order of records returned as being significant. To the degree that major DBs truly implement the relational calculus, they may/may not return the rows in a specific order. Hal Helms "Java for CF Programmers" class in Washington, DC June 23-27 www.halhelms.com I sure hope they are like arrays, since the order of the records can be just as important as the record data itself I think im losing it...arent query recordsets structures? or are the similar to structures in the way we can get into their data and return values? ...tony Tony Weeg Senior Web Developer UnCertified Advanced ColdFusion Developer Information System Design Navtrak, Inc. Mobile workforce monitoring, mapping & reporting www.navtrak.net 410.548.2337

to my knowledge, if there is an identity field, or some other key, they are returned in relation to the rules of collation, based on the key or identity field, unless there is an order by clause right? so that if I had a key in a table called id, and no order by clause in my query, they would follow the collation rules of order on the column that is my key? ...tony Tony Weeg Senior Web Developer UnCertified Advanced ColdFusion Developer Information System Design Navtrak, Inc. Mobile workforce monitoring, mapping & reporting www.navtrak.net 410.548.2337 Johannes, Although I know some programmers rely on the order that records are returned in, Dr. Codd, the creator of relational databases (who just recently died), specifically prohibits the order of records returned as being significant. To the degree that major DBs truly implement the relational calculus, they may/may not return the rows in a specific order. Hal Helms "Java for CF Programmers" class in Washington, DC June 23-27 www.halhelms.com I sure hope they are like arrays, since the order of the records can be just as important as the record data itself I think im losing it...arent query recordsets structures? or are the similar to structures in the way we can get into their data and return values? ...tony Tony Weeg Senior Web Developer UnCertified Advanced ColdFusion Developer Information System Design Navtrak, Inc. Mobile workforce monitoring, mapping & reporting www.navtrak.net 410.548.2337

Tony Weeg wrote: > to my knowledge, if there is an identity field, > or some other key, they are returned in relation to the > rules of collation, based on the key or identity field, unless > there is an order by clause right? Incorrect. > so that if I had a key in a table called id, and no order > by clause in my query, they would follow the collation rules > of order on the column that is my key? No. Jochem

not even in sql server 2000 I know its true. I run queries all the time that have no order by, and just have an autonumber field, identity column blah blah blah, and I would say...now this is without any empirical data, that they always come back ordered by that column.  at least in query analyzer. no? ...tony Tony Weeg Senior Web Developer UnCertified Advanced ColdFusion Developer Information System Design Navtrak, Inc. Mobile workforce monitoring, mapping & reporting www.navtrak.net 410.548.2337 Tony Weeg wrote: > to my knowledge, if there is an identity field, > or some other key, they are returned in relation to the > rules of collation, based on the key or identity field, unless > there is an order by clause right? Incorrect. > so that if I had a key in a table called id, and no order > by clause in my query, they would follow the collation rules > of order on the column that is my key? No. Jochem

What the database actually does is different from what it is programmed to do (strange as that may sound).  There is nothing preventing it from returning stuff in a standard order when no ORDER BY clause is provided. However, there is nothing requiring it to either.  Thus, if the order of the result set is important, you MUST provided an ORDER BY clause. If for a given query the DB only has to read through the table from top to bottom, then it is very likely going to return the records in order, unless it is told to reorder them.  However, if it does any kind of non-linear search for matching rows, then chances are very good (but not 100%) that it won't read them in IDENTITY order.  Both cases you have identical behaviour (rows returned in parse order).  The first situation just happens to also be IDENTITY order, but that's a coincidence. take this table (MySQL syntax):    create mytable (       myID unsigned int not null auto_increment,       age int not null default 3,       primary key (myID),       key k_age (age)    ) If you query like this:    SELECT *    FROM mytable then you are likely to get the results in IDENTITY order, as the DB has to return the whole table, and the easiest way is to start at the top and go straigh to the bottom.  But if you query like this:    SELECT *    FROM mytable    WHERE age > 4 then you're likely to get them in a very different order, as the k_age index should be used to pick the rows to return.  That index isn't going to be parsed with a linear search (if it is, what's the point?), so the returned rowIDs won't be in IDENTITY order. barneyb --- Barney Boisvert, Senior Development Engineer AudienceCentral (formerly PIER System, Inc.) bboisvert@audiencecentral.com voice : 360.756.8080 x12 fax   : 360.647.5351 www.audiencecentral.com ----- Excess quoted text cut - see Original Post for more -----

Tony Weeg wrote: ----- Excess quoted text cut - see Original Post for more ----- No. For instance, any order that may or may not be present in physical storage is messed up as soon as you start doing stuff like joining to other tables. Just the fact that there are different ways to perform the actual join (hashjoin, mergejoin, nested loop etc.) should be enough of a warning against trusting natural orders. Add to that you might be using different indices to filter the data, query execution plans changing because of different table statistics and even the difference between little endian and big endian machines and you are entering a nightmare. Maybe "SELECT * FROM table" always returns the same order, but it is the exception, not the rule. Jochem

makes sense. and believe me, I wouldn't recommend blind faith when it comes to the db ordering things...just something I had noticed, with some very simple queries with no real tricky stuff involved. ...tony Tony Weeg Senior Web Developer UnCertified Advanced ColdFusion Developer Information System Design Navtrak, Inc. Mobile workforce monitoring, mapping & reporting www.navtrak.net 410.548.2337 Tony Weeg wrote: > not even in sql server 2000 > > I know its true. > > I run queries all the time that have no order by, and just have an > autonumber field, identity column blah blah blah, and I would say...now > this > is without any empirical data, that they always come back ordered > by that column.  at least in query analyzer. > > no? No. For instance, any order that may or may not be present in physical storage is messed up as soon as you start doing stuff like joining to other tables. Just the fact that there are different ways to perform the actual join (hashjoin, mergejoin, nested loop etc.) should be enough of a warning against trusting natural orders. Add to that you might be using different indices to filter the data, query execution plans changing because of different table statistics and even the difference between little endian and big endian machines and you are entering a nightmare. Maybe "SELECT * FROM table" always returns the same order, but it is the exception, not the rule. Jochem

Tony, Yes - but this can be altered through the use of clustered indexes on something OTHER than the identity field. Also, if you join this data to another table or use union or other extrapolation techniques things get more complicated. -mk not even in sql server 2000 I know its true. I run queries all the time that have no order by, and just have an autonumber field, identity column blah blah blah, and I would say...now this is without any empirical data, that they always come back ordered by that column.  at least in query analyzer. no? ...tony Tony Weeg Senior Web Developer UnCertified Advanced ColdFusion Developer Information System Design Navtrak, Inc. Mobile workforce monitoring, mapping & reporting www.navtrak.net 410.548.2337 Tony Weeg wrote: > to my knowledge, if there is an identity field, > or some other key, they are returned in relation to the > rules of collation, based on the key or identity field, unless > there is an order by clause right? Incorrect. > so that if I had a key in a table called id, and no order > by clause in my query, they would follow the collation rules > of order on the column that is my key? No. Jochem

yeah that's what im reading and seeing here...that all makes sense, I guess what I was trying to say, not that I was advocating blind faith in the ordering, was just that sometimes, you will see it follow collation rules, on a simple select. that's all.   thanks for yours and everyone elses help earlier! ...tony Tony Weeg Senior Web Developer UnCertified Advanced ColdFusion Developer Information System Design Navtrak, Inc. Mobile workforce monitoring, mapping & reporting www.navtrak.net 410.548.2337 Tony, Yes - but this can be altered through the use of clustered indexes on something OTHER than the identity field. Also, if you join this data to another table or use union or other extrapolation techniques things get more complicated. -mk not even in sql server 2000 I know its true. I run queries all the time that have no order by, and just have an autonumber field, identity column blah blah blah, and I would say...now this is without any empirical data, that they always come back ordered by that column.  at least in query analyzer. no? ...tony Tony Weeg Senior Web Developer UnCertified Advanced ColdFusion Developer Information System Design Navtrak, Inc. Mobile workforce monitoring, mapping & reporting www.navtrak.net 410.548.2337 Tony Weeg wrote: > to my knowledge, if there is an identity field, > or some other key, they are returned in relation to the > rules of collation, based on the key or identity field, unless > there is an order by clause right? Incorrect. > so that if I had a key in a table called id, and no order > by clause in my query, they would follow the collation rules > of order on the column that is my key? No. Jochem

Is this what you're looking for?  queryObj["column"]["row"] example: staff["last_name"][54] ~Todd At 10:40 AM 4/25/2003 -0400, you wrote: >I think im losing it...arent query recordsets structures? > >or are the similar to structures in the way we can get into >their data and return values? > >...tony

A query is not a structure, it just smells like one. ;) What are you trying to do? You ask about getting the data - but I assume you already know about <cfoutput query=""> and <cfloop query="">. If you are looking for something a bit more dynamic, here is an example of code that will output a query w/o knowing the columns: <cfoutput query="q">   <cfloop index="col" list="#q.columnList#">     #q[col][currentRow]#   </cfloop>   <p> </cfoutput> ======================================================================== === Raymond Camden, ColdFusion Jedi Master for Mindseye, Inc (www.mindseye.com) Member of Team Macromedia (http://www.macromedia.com/go/teammacromedia) Email    : jedimaster@mindseye.com Blog     : www.camdenfamily.com/morpheus/blog Yahoo IM : morpheus "My ally is the Force, and a powerful ally it is." - Yoda ----- Excess quoted text cut - see Original Post for more ----- www.navtrak.net 410.548.2337

im just toying with getting a lump of data into a cfsavecontent, so that I can write to a csv this lump of data, but I was kinda hoping to have all the cflooping and stuff done before it gets there, so im just toying with getting it all into an array of structures, and was hoping I could just snag the data from the query, in its baseline structure format, and make some changes to the data, add some lines to the structure....etc.... make sense? ...tony Tony Weeg Senior Web Developer UnCertified Advanced ColdFusion Developer Information System Design Navtrak, Inc. Mobile workforce monitoring, mapping & reporting www.navtrak.net 410.548.2337 A query is not a structure, it just smells like one. ;) What are you trying to do? You ask about getting the data - but I assume you already know about <cfoutput query=""> and <cfloop query="">. If you are looking for something a bit more dynamic, here is an example of code that will output a query w/o knowing the columns: <cfoutput query="q">   <cfloop index="col" list="#q.columnList#">     #q[col][currentRow]#   </cfloop>   <p> </cfoutput> ======================================================================== === Raymond Camden, ColdFusion Jedi Master for Mindseye, Inc (www.mindseye.com) Member of Team Macromedia (http://www.macromedia.com/go/teammacromedia) Email    : jedimaster@mindseye.com Blog     : www.camdenfamily.com/morpheus/blog Yahoo IM : morpheus "My ally is the Force, and a powerful ally it is." - Yoda ----- Excess quoted text cut - see Original Post for more ----- www.navtrak.net 410.548.2337

Errr - sure. You've seen the other posts on how to examine the contents. If you want to add data, you can use queryAddRow, querySetCell. ======================================================================== === Raymond Camden, ColdFusion Jedi Master for Mindseye, Inc (www.mindseye.com) Member of Team Macromedia (http://www.macromedia.com/go/teammacromedia) Email    : jedimaster@mindseye.com Blog     : www.camdenfamily.com/morpheus/blog Yahoo IM : morpheus "My ally is the Force, and a powerful ally it is." - Yoda ----- Excess quoted text cut - see Original Post for more -----

that's what I don't want to F with, the results as a query record set, I want to take it from recordset status, do what I gotta do to automatically get into an array of structures, and then from there do stuff to it.... ...tony Tony Weeg Senior Web Developer UnCertified Advanced ColdFusion Developer Information System Design Navtrak, Inc. Mobile workforce monitoring, mapping & reporting www.navtrak.net 410.548.2337 Errr - sure. You've seen the other posts on how to examine the contents. If you want to add data, you can use queryAddRow, querySetCell. ======================================================================== === Raymond Camden, ColdFusion Jedi Master for Mindseye, Inc (www.mindseye.com) Member of Team Macromedia (http://www.macromedia.com/go/teammacromedia) Email    : jedimaster@mindseye.com Blog     : www.camdenfamily.com/morpheus/blog Yahoo IM : morpheus "My ally is the Force, and a powerful ally it is." - Yoda ----- Excess quoted text cut - see Original Post for more -----

yup...then use that UDF QueryToArrayOfStructures().....that'll do it for ya..nice and easy man ;-) Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. t. 250.920.8830 e. bryan@electricedgesystems.com --------------------------------------------------------- Macromedia Associate Partner www.macromedia.com --------------------------------------------------------- Vancouver Island ColdFusion Users Group Founder & Director www.cfug-vancouverisland.com ----- Excess quoted text cut - see Original Post for more -----

Seems like a lot of extra work?  Nothing wrong with making your own query object (queryNew() is awesome) or fussing with an existing one? ~Todd At 11:18 AM 4/25/2003 -0400, you wrote: ----- Excess quoted text cut - see Original Post for more ----- ---------- Todd Rafferty (todd@web-rat.com) - http://www.web-rat.com/ Team Macromedia Volunteer for ColdFusion http://www.macromedia.com/support/forums/team_macromedia/ http://www.devmx.com/ ----------

unless ur lazy like me and havent spent time learning that set of cool functions ;) ...tony Tony Weeg Senior Web Developer UnCertified Advanced ColdFusion Developer Information System Design Navtrak, Inc. Mobile workforce monitoring, mapping & reporting www.navtrak.net 410.548.2337 Seems like a lot of extra work?  Nothing wrong with making your own query object (queryNew() is awesome) or fussing with an existing one? ~Todd At 11:18 AM 4/25/2003 -0400, you wrote: ----- Excess quoted text cut - see Original Post for more ----- ---------- Todd Rafferty (todd@web-rat.com) - http://www.web-rat.com/ Team Macromedia Volunteer for ColdFusion http://www.macromedia.com/support/forums/team_macromedia/ http://www.devmx.com/ ----------

You can data to a returned recordset? Coooooool! ----- Excess quoted text cut - see Original Post for more -----

They are arrays of structures (or structures of arrays) - but they are more alike a classs in that they also have some extra properties - like columnlist and recordset. But basically:   myqueryname['columname'][1]  - gives you the first row of the query.... notice that it's a 1 and not a zero (most arrays begin at 0). Because of the special properties, you can't do things like "structkeylist(myqueryname)" - which is exactly why things like the columnlist property was added (were added?). -mk I think im losing it...arent query recordsets structures? or are the similar to structures in the way we can get into their data and return values? ...tony Tony Weeg Senior Web Developer UnCertified Advanced ColdFusion Developer Information System Design Navtrak, Inc. Mobile workforce monitoring, mapping & reporting www.navtrak.net 410.548.2337

> I think im losing it...arent query recordsets structures? No. > or are the similar to structures in the way we can get into > their data and return values? A recordset is more complicated than a structure. It might be useful to think of a query object as a structure that contains arrays, though. The structure would represent the columns, while an individual column would be represented as an array. However, that's not exactly what it is, in the sense that if you test a query object with IsStruct, it'll return false. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444

ok, well I understand all of this... does what im trying to do make sense? im trying to get the data from a query, play with it, when im done drop the data into a cfsavecontent variable, and then on to a csv without too many file system hits. but before I get it into that cfsavecontent, I need to play with it...meaning I may have to add a column from a sub query etc... thanks. ...tony Tony Weeg Senior Web Developer UnCertified Advanced ColdFusion Developer Information System Design Navtrak, Inc. Mobile workforce monitoring, mapping & reporting www.navtrak.net 410.548.2337 > I think im losing it...arent query recordsets structures? No. > or are the similar to structures in the way we can get into > their data and return values? A recordset is more complicated than a structure. It might be useful to think of a query object as a structure that contains arrays, though. The structure would represent the columns, while an individual column would be represented as an array. However, that's not exactly what it is, in the sense that if you test a query object with IsStruct, it'll return false. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444

Hey Tony, It sounds like some UDFs might help you out.  I've used one from www.cflib.org called QueryToArrayOfStructures() (or something close to that).  Once the queries converted you can add columns/resort/etc. and then convert it back with another UDF (can't remember the name but you'll see it).  The UDFs are really great when you just can't get what you want out of SQL.  You can use them to totally reformat the results and then do a QofQ on them...god stuff.  Then again you may just get by with CF functions like QueryAddRow() and QuerySetCell(). HTH...came in a tad late in the thread ;-) Cheers Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. t. 250.920.8830 e. bryan@electricedgesystems.com --------------------------------------------------------- Macromedia Associate Partner www.macromedia.com --------------------------------------------------------- Vancouver Island ColdFusion Users Group Founder & Director www.cfug-vancouverisland.com ----- Excess quoted text cut - see Original Post for more -----

dig it....thats what im looking for!! thanks. when in doubt, cflib.org, some other cfcodeguru has already done what ur thinkin ;) ...tony Tony Weeg Senior Web Developer UnCertified Advanced ColdFusion Developer Information System Design Navtrak, Inc. Mobile workforce monitoring, mapping & reporting www.navtrak.net 410.548.2337 Hey Tony, It sounds like some UDFs might help you out.  I've used one from www.cflib.org called QueryToArrayOfStructures() (or something close to that).  Once the queries converted you can add columns/resort/etc. and then convert it back with another UDF (can't remember the name but you'll see it).  The UDFs are really great when you just can't get what you want out of SQL.  You can use them to totally reformat the results and then do a QofQ on them...god stuff.  Then again you may just get by with CF functions like QueryAddRow() and QuerySetCell(). HTH...came in a tad late in the thread ;-) Cheers Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. t. 250.920.8830 e. bryan@electricedgesystems.com --------------------------------------------------------- Macromedia Associate Partner www.macromedia.com --------------------------------------------------------- Vancouver Island ColdFusion Users Group Founder & Director www.cfug-vancouverisland.com ----- Excess quoted text cut - see Original Post for more ----- to > think of a query object as a structure that contains arrays, though. The > structure would represent the columns, while an individual column would > be > represented as an array. However, that's not exactly what it is, in the > sense that if you test a query object with IsStruct, it'll return false. ----- Excess quoted text cut - see Original Post for more -----

yup but what about clustered indexes? - they physically sort the rows in a certain way and hence if no order is specified the rows are returned in this way - surely that is significant. Johannes, Although I know some programmers rely on the order that records are returned in, Dr. Codd, the creator of relational databases (who just recently died), specifically prohibits the order of records returned as being significant. To the degree that major DBs truly implement the relational calculus, they may/may not return the rows in a specific order. Hal Helms "Java for CF Programmers" class in Washington, DC June 23-27 www.halhelms.com I sure hope they are like arrays, since the order of the records can be just as important as the record data itself I think im losing it...arent query recordsets structures? or are the similar to structures in the way we can get into their data and return values? ...tony Tony Weeg Senior Web Developer UnCertified Advanced ColdFusion Developer Information System Design Navtrak, Inc. Mobile workforce monitoring, mapping & reporting www.navtrak.net 410.548.2337

Still not a good enough argument, because the SQL engine may be using other indexes to do additional selection (WHERE clause or JOINs), and that may affect the search order, especially if the index is in a tree structure (which is the norm, as far as I know). As Hal said, there is NO implicit ordering scheme if an ORDER BY clause is not specified.  Most of the time (in my experience) you can predict pretty accurately what the result will be if the query is simple and you're familiar with the database, especially if there is no use of indexes, but that behaviour isn't guarenteed. This exact situation bit me in the rear last year with some code that I inherited.  Upgraded our DB, and the way indexes were managed changed, and so the results started coming out all garbled, because the previous guy hadn't bothered to put in a ORDER BY, instead relying on the natural order the DB returned the results in. barneyb --- Barney Boisvert, Senior Development Engineer AudienceCentral (formerly PIER System, Inc.) bboisvert@audiencecentral.com voice : 360.756.8080 x12 fax   : 360.647.5351 www.audiencecentral.com ----- Excess quoted text cut - see Original Post for more -----

Is this the case for all databases though? to my knowledge, if there is an identity field, or some other key, they are returned in relation to the rules of collation, based on the key or identity field, unless there is an order by clause right? so that if I had a key in a table called id, and no order by clause in my query, they would follow the collation rules of order on the column that is my key? ...tony Tony Weeg Senior Web Developer UnCertified Advanced ColdFusion Developer Information System Design Navtrak, Inc. Mobile workforce monitoring, mapping & reporting www.navtrak.net 410.548.2337 Johannes, Although I know some programmers rely on the order that records are returned in, Dr. Codd, the creator of relational databases (who just recently died), specifically prohibits the order of records returned as being significant. To the degree that major DBs truly implement the relational calculus, they may/may not return the rows in a specific order. Hal Helms "Java for CF Programmers" class in Washington, DC June 23-27 www.halhelms.com I sure hope they are like arrays, since the order of the records can be just as important as the record data itself I think im losing it...arent query recordsets structures? or are the similar to structures in the way we can get into their data and return values? ...tony Tony Weeg Senior Web Developer UnCertified Advanced ColdFusion Developer Information System Design Navtrak, Inc. Mobile workforce monitoring, mapping & reporting www.navtrak.net 410.548.2337

Adrian Lynch wrote: > Is this the case for all databases though? No. Jochem

Generally any that use indexing - I remember back in the good old days of DBase :) - also used indexing. Hal is right that when using the rows returned it isn't significant how they are ordered - but it is in terms of how the data is stored in the database using indexing (clustered in the case of SQL server) Is this the case for all databases though? to my knowledge, if there is an identity field, or some other key, they are returned in relation to the rules of collation, based on the key or identity field, unless there is an order by clause right? so that if I had a key in a table called id, and no order by clause in my query, they would follow the collation rules of order on the column that is my key? ...tony Tony Weeg Senior Web Developer UnCertified Advanced ColdFusion Developer Information System Design Navtrak, Inc. Mobile workforce monitoring, mapping & reporting www.navtrak.net 410.548.2337 Johannes, Although I know some programmers rely on the order that records are returned in, Dr. Codd, the creator of relational databases (who just recently died), specifically prohibits the order of records returned as being significant. To the degree that major DBs truly implement the relational calculus, they may/may not return the rows in a specific order. Hal Helms "Java for CF Programmers" class in Washington, DC June 23-27 www.halhelms.com I sure hope they are like arrays, since the order of the records can be just as important as the record data itself I think im losing it...arent query recordsets structures? or are the similar to structures in the way we can get into their data and return values? ...tony Tony Weeg Senior Web Developer UnCertified Advanced ColdFusion Developer Information System Design Navtrak, Inc. Mobile workforce monitoring, mapping & reporting www.navtrak.net 410.548.2337

Watch carefully, and you will see that it is normally ordered by the first column if you haven't specified an ORDER BY.  Doesn't really matter what the column is.  (my own observation) Shawn not even in sql server 2000 I know its true. I run queries all the time that have no order by, and just have an autonumber field, identity column blah blah blah, and I would say...now this is without any empirical data, that they always come back ordered by that column.  at least in query analyzer. no? ...tony Tony Weeg Senior Web Developer UnCertified Advanced ColdFusion Developer Information System Design Navtrak, Inc. Mobile workforce monitoring, mapping & reporting www.navtrak.net 410.548.2337 Tony Weeg wrote: > to my knowledge, if there is an identity field, > or some other key, they are returned in relation to the > rules of collation, based on the key or identity field, unless > there is an order by clause right? Incorrect. > so that if I had a key in a table called id, and no order > by clause in my query, they would follow the collation rules > of order on the column that is my key? No. Jochem