names returned in the order they are defined.
When you are writing a general-purpose table-independent db client or
maintenance program.
In almost every case, there is some logic to the order in which table
columns are defined.
For example:
--The primary key is usually defined first
--related fields are usually contiguous (e.g. LastName, FirstName)
certainly there are exceptions, but in most tables the column order is
quite logical
Is this a big deal?
It can be!
Consider:
--You have a new client who wants you to create (or upgrade) his web
site from one or more Access dbs.
--The designers of these dbs are long-gone
--You must analyze and reverse engineer the db to determine the best
RDBMS and db design to implement
-- You must create/convert/manipulate the existing data into the new
RDBMS & schema
-- You must provide programs to maintain the new db
You can do some of the above with a general-purpose SQL GUI client (if
one is available)
But I have found that CF's power/ease of manipulating databases makes
it greate for writing little, ad hoc, scripts to analyze and manipulate
the data.
Really, CF is the best db scripting language that I am aware of.
Anyway, using CF in this way (as a data analysis & design tool) can
significantly reduce the effort to redisign the db.
And, if you save the little ad hoc design/analysis scripts, you have
the building blocks of the programs necessary to convert/create the new
db.
And, (at least in the initial stages) maintaining the new db can be
done with a single general-purpose CF program -- kind of a custom SQL
GUI.
Finally, save all conversion programs for the next upgrade to the
clent's db
Whew!
Long-winded answer!
But I do this all the time, and it is a real irritant to get column
names returned in random (alphabetic) order,
or have to name the columns in select statements for one-time scripts.
GTG
Dick
On Sep 3, 2004, at 2:54 PM, Barney Boisvert wrote:
> Nope sure isn't, and believe me, you don't want to be able to. Doing
> so creates a dependancy on the order of the columns in your database
> and/or SQL statement (depending on if you use SELECT * or not), which
> is almost guarenteed to cause problems down the road when you
> add/update/remove a column from either place.
>
> If you need to use the columns in a certain order, define a variable
> with the column names, use that variable as your SELECT list, and then
> use it again in place of queryname.columnlist. That way you won't
> have any cross dependancies. If that's impossible, just do the second
> part. It'll work almost as well, at fail explicitly (throw an
> exception) if things change to break a dependancy, rather than the
> non-exception failure if you just rely on the order the SQL server
> throws back at you.
>
> cheers,
> barneyb
>
> On Fri, 03 Sep 2004 17:48:30 -0400, Mark Knox <[EMAIL PROTECTED]> wrote:
> > I know I've seen this on a list before but couldn't remember
> where. When displaying or using the cfquery.columnList variable, the
> columns are automatically sorted in alphanumeric order. Is there any
> way to retrieve the columnlist in the order it was returned by
> SQL-Server -- which is the order as it is defined in the table to
> begin with? In otherwords, is there a way to tell <cfquery> to NOT
> sort the columnNames for the columnList variable?
> >
> > thanks in advance,
> >
> > mark
>
> --
> Barney Boisvert
> [EMAIL PROTECTED]
> 360.319.6145
> http://www.barneyb.com
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
