"Ann W. Harrison" wrote:

> Joe Martinez wrote:
>
> >> Select sales.*, customers.custfname, customers.custlname
> >> from sales, customers
> >> where ...
>
>> wow. I really didn't know you could do it! Thanks for the syntax.
>
> You can, but you really shouldn't. If some one adds a field to sales,
> or drops one, or changed the field order in the table, your query is
> going to mess up. One of the sadder parts of the history of relational
> databases is that a loosey-goosey interactive query language was chosen
> as the standard for program access. The "*" syntax should never be used
> outside casual, hand-typed, one time queries.

When application reads selected data, there is a secret (:-) structure
called xsqlda, that among pointers to data itself contains description of types
of data and fieldnames! It is used by most (all?) data access components
(ibx, fib+, etc), so applications access fields by their names.
If somebody specifies
fields to be read from "select *" query by position number
instead of field name, is it really good reason to blame
the feature and not that developer ?

> If some one adds a field to sales,
> or drops one, or changed the field order in the table, your query is
> going to mess up.

If somebody drops the field, than it is dropped, and you do not retrieve
its data regardless your using "select deletedfield, ..." or "select *" query.
In fact, if the application use "select deletedfield, ..." than it is in troubles,
while correctly written application using "select *" can in many cases
continue to work (e.g. if the dropped field was just comment shown in grid).

Here is another exampe how using "*" can avoid troubles with inconsitent
database - I have several views that are used to restrict access
to just subsets of rows, but that copy the structure of main table, i.e.
Create View V1 as Select * From Tab Where condition1;
Create View V2 as Select * From Tab Where condition2; etc
When I add new field to the table, then I just recreate those views.
Without "*" I would have to add that field to the table AND to ALL
that views, so there would be much higher danger that I forget
to change the definition somewhere.

> The "*" syntax should never be used
> outside casual, hand-typed, one time queries.

I do not agree with this crusade :-) against "*".
The fact that some feature can be misused is not enugh
to say that it should "never be used ...", if that feature
can be both safe and useful when used correctly.
Otherwise, I would recommend to never use pointers
(you can forget to initialize them, or free the memory, ...),
never use national characters (text file usually do not contain
information which charset is used, so there is always
danger of misinterpretation), and several millions other things.

Strangely, nobody mentioned "Insert Into Table Values (...)",
i.e. without list of fieldnames, which, unlike "select *", do not pass
names of fields in xsqlda (why?), so there I would fully agree with
"should never be used outside casual, hand-typed, one time inserts".

Ivan
http://www.volny.cz/iprenosil/interbase/