Hi list!

A quick question (and I hope a quick answer!).

I have a Windows Server 2003 R2 SP2 box where MS SQL 2000 SP4 is
running.
I have a debian "sarge" box running apache 2.0.54-5sarge2 and
libapache2-mod-php4 4.3.10-22.
I have another debian "lenny" box running apache 2.2.8-1 and
libapache2-mod-php 5 5.2.5-2.

On the two servers I have the same php script that queries a database
on the SQL server. One of the queried colums holds a datetime, and I
have that the output format is different between the two servers:
lenny server: Jun 7 2007 12:00:00:000AM
sarge server: Thu Jun 7 00:00:00 2007 <- this is what I want

I did not use any interpolation function (like strtotime()) but the
outcome is different.
How can I have the same output on the lenny server? Is this a server
configuration issue or what else? I would like not to use any php
function because I want to migrate my scripts from the old server to
the new one with the minimum effort.

TIA
--Alessandro

On 18 Feb, 17:36, Alessandro <fag...@gmail.com> wrote:
> Hi list!
>
> [...]
>

Any hint please!
I made some test on the two boxes. The output of the date command is
the same:
Tue Feb 19 08:41:57 CET 2008
so it should be a matter of php/apache configuration. Where should I
look?
I tried to compare the main configuration scripts but with no
success...

Alessandro wrote:

> I have a Windows Server 2003 R2 SP2 box where MS SQL 2000 SP4 is
> running.

Using MSSQL, you are really best off specifying your desired date format
as part of the query itself. e.g.

SELECT CONVERT(varchar(20),my_date,20) AS my_date
FROM my_table

will consistently return dates as varchar(20) columns formatted in "YYYY-
MM-DD HH:mm:ss" format. If you don't include the format conversion in the
query itself, then the format you get back can be a bit unpredictable. It
will depend on, amongst other factors: version of PHP, version of FreeDTS,
locale settings and the user name used to log into SQL server.

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.17.14-mm-desktop-9mdvsmp, up 20 days, 16:08.]

Bottled Water
http://tobyinkster.co.uk/blog/2008/02/18/bottled-water/

On 19 Feb, 10:55, Toby A Inkster <usenet200...@tobyinkster.co.uk>
wrote:
> Alessandro wrote:
> > I have a Windows Server 2003 R2 SP2 box where MS SQL 2000 SP4 is
> > running.
>
> Using MSSQL, you are really best off specifying your desired date format
> as part of the query itself. e.g.
>
> SELECT CONVERT(varchar(20),my_date,20) AS my_date
> FROM my_table

I agree with you approach. Unfortunately I have many queries of this
kind:

SELECT * FROM my_table

and I display datetime columns with instructions like the following:
echo "DATE1: ".date("d/m/Y",strtotime($row[DateTimeColumn1]));

I have to parse all my scripts (too many) and change both the query
and the echo argument...

Alessandro wrote:
> On 19 Feb, 10:55, Toby A Inkster <usenet200...@tobyinkster.co.uk>
> wrote:
>> Alessandro wrote:
>>> I have a Windows Server 2003 R2 SP2 box where MS SQL 2000 SP4 is
>>> running.
>> Using MSSQL, you are really best off specifying your desired date format
>> as part of the query itself. e.g.
>>
>> SELECT CONVERT(varchar(20),my_date,20) AS my_date
>> FROM my_table
>
> I agree with you approach. Unfortunately I have many queries of this
> kind:
>
> SELECT * FROM my_table
>
> and I display datetime columns with instructions like the following:
> echo "DATE1: ".date("d/m/Y",strtotime($row[DateTimeColumn1]));
>
> I have to parse all my scripts (too many) and change both the query
> and the echo argument...
>

SELECT * is not a good thing to use. You are much better off always
specifying the columns.

For one thing, it's very seldom you need all of the columns. But more
importantly, what happens if someone later adds a new column to the
table (i.e. a 5mb BLOB)?

Also, if someone later deletes or renames a current column, the query
will fail, making the problem very obvious. Otherwise you may not even
get an error message, depending on your error settings - just incorrect
output, which may or may not be noticed for a while.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

> SELECT * is not a good thing to use. You are much better off always
> specifying the columns.
>
> For one thing, it's very seldom you need all of the columns.

Why put columns in a table if you are not interested in them? It is
really rare and a sign of bad datase stucture if a table contains things
I do not want to select.

> But more
> importantly, what happens if someone later adds a new column to the
> table (i.e. a 5mb BLOB)?

Well, first of all, that would be me. If I would add such a column, I
would have a need for it. If that field would only be vaguely connected
to the rows in that table, it would be in an only vaguely connected table.

So what happens? I would update the mapping table and a template and I
would have an extra field on my web form. Without the need to modify the
code. That is why ORM layers exist, is it not?

> Also, if someone later deletes or renames a current column, the query
> will fail, making the problem very obvious.

On the contrary, "SELECT *" will NOT fail. Just update your ORM settings
and all of your code knows it. Your php code will be less hard-wired to
the database structure, which is a good thing.

> Otherwise you may not even
> get an error message, depending on your error settings - just incorrect
> output, which may or may not be noticed for a while.

I am getting more errors than I am happy with in such a case, I can
assure you.

Regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

Willem Bogaerts wrote:
>> SELECT * is not a good thing to use. You are much better off always
>> specifying the columns.
>>
>> For one thing, it's very seldom you need all of the columns.
>
> Why put columns in a table if you are not interested in them? It is
> really rare and a sign of bad datase stucture if a table contains things
> I do not want to select.
>

In over 20 years of SQL programming, I've found it to be very rare to
need all columns all the time. If you do need them all the time, you've
probably overnormalized. THAT is a bad design.

>> But more
>> importantly, what happens if someone later adds a new column to the
>> table (i.e. a 5mb BLOB)?
>
> Well, first of all, that would be me. If I would add such a column, I
> would have a need for it. If that field would only be vaguely connected
> to the rows in that table, it would be in an only vaguely connected table.
>

Maybe NOW that would be you. But what about later?

> So what happens? I would update the mapping table and a template and I
> would have an extra field on my web form. Without the need to modify the
> code. That is why ORM layers exist, is it not?
>

Well, the first time you select 3 rows, you run out of memory for your
script.

>> Also, if someone later deletes or renames a current column, the query
>> will fail, making the problem very obvious.
>
> On the contrary, "SELECT *" will NOT fail. Just update your ORM settings
> and all of your code knows it. Your php code will be less hard-wired to
> the database structure, which is a good thing.
>

I didn't say SELECT * will fail. But other code later in the script
will fail.

At some point your code will be tied to the database. That's where it
will fail.

>> Otherwise you may not even
>> get an error message, depending on your error settings - just incorrect
>> output, which may or may not be noticed for a while.
>
> I am getting more errors than I am happy with in such a case, I can
> assure you.
>
> Regards,

You really should understand what SQL experts have been saying for over
20 years. And why they've been saying it.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Greetings, Alessandro.
In reply to Your message dated Monday, February 18, 2008, 19:36:39,

> I have a Windows Server 2003 R2 SP2 box where MS SQL 2000 SP4 is
> running.
> I have a debian "sarge" box running apache 2.0.54-5sarge2 and
> libapache2-mod-php4 4.3.10-22.
> I have another debian "lenny" box running apache 2.2.8-1 and
> libapache2-mod-php 5 5.2.5-2.

> On the two servers I have the same php script that queries a database
> on the SQL server. One of the queried colums holds a datetime, and I
> have that the output format is different between the two servers:
> lenny server: Jun 7 2007 12:00:00:000AM
> sarge server: Thu Jun 7 00:00:00 2007 <- this is what I want

> I did not use any interpolation function (like strtotime()) but the
> outcome is different.
> How can I have the same output on the lenny server? Is this a server
> configuration issue or what else? I would like not to use any php
> function because I want to migrate my scripts from the old server to
> the new one with the minimum effort.

It is different in case of different intermediate layers between PHP and MS
SQL server.
I've had similar issue when dealing with dates from MSSQL.
After some reading and consultations, I'm end up with solution similar to one
posted above:

SELECT CONVERT(VARCHAR, u.[create_date], 126) [create_date]
FROM [tablename] u;

It will give You best possible datetime representation I ever saw from the MSSQL.
It is XML-style "YYYY-MM-DD'T'HH:MM" format, like "2008-02-14T16:00"

Then You may just strtotime() that column and deal with "normal" timestamp and
format it as You wish using regular PHP *date() functions.


--
Sincerely Yours, AnrDaemon <anrdaemon@freemail.ru>

Sample:

public function execute($request, $DB = NULL)
{
$result = false;

if(class_exists('DB_common') && !$DB instanceof DB_common)
{
$this->setLastError(500, sprintf(LANG_DEAD_DB_EXPLAINED, 500, 'Database handler unknown'),
__FILE__, __LINE__-3, __FUNCTION__);
}
elseif(DB::isError($rc = $DB->getAll(MSSQL_LIST_CHARS, array($request))))
{
$this->setLastError(500, sprintf(LANG_DEAD_DB_EXPLAINED, $rc->getCode(), $rc->getUserInfo()),
__FILE__, __LINE__-3, __FUNCTION__);
}
else
{
$result = array();
foreach($rc as $char)
{
$char['create_date'] = strtotime($char['create_date']);
$char['login'] = strtotime($char['login']);
$char['logout'] = strtotime($char['logout']);
$result[] = $char;
}
}

return $result;
}

Please don't delete attributions; they're important to following the
conversation. I've readded them.

On Tue, 19 Feb 2008 13:03:43 +0100, Willem Bogaerts
<w.bogaerts@kratz.maardanzonderditstuk.nl> wrote in
<47bac59f$0$14355$e4fe514c@news.xs4all.nl>:

>On Tue, 19 Feb 2008 06:40:01 -0500, Jerry
>Stuckle <jstucklex@attglobal.net> wrote in
><25idnRKzfsPjXSfanZ2dnUVZ_t3inZ2d@comcast.com>:
>
>> SELECT * is not a good thing to use. You are much better off always
>> specifying the columns.
>>
>> For one thing, it's very seldom you need all of the columns.
>
>Why put columns in a table if you are not interested in them? It is
>really rare and a sign of bad datase stucture if a table contains things
>I do not want to select.

This is a strawman. Jerry suggested no such thing. He said that you
don't usually need all of the columns for a particular query. IME
that's quite true. The only time I want all fields from a particular
table is usually when allowing record additions and editing via a user
interface. Even in those cases I generally use views because I'm
joining against related tables. Most of time when I'm querying,
however, is for reporting, which usually requires only a subset of the
available columns.

>>But more importantly, what happens if someone later adds a new
>>column to the table (i.e. a 5mb BLOB)?
>
>Well, first of all, that would be me.

Are you the only one who ever works on the databases you create? If
so, do you think that your experience is representative?

>If I would add such a column, I would have a need for it.

In every single case that you wanted to retrieve data from that table?
You wouldn't want to ever want to, say, run a query returning all
unique authors associated with the documents that are in the BLOB
field?

>If that field would only be vaguely connected to the rows in that
>table, it would be in an only vaguely connected table.

Sorry, but this doesn't make any sense to me.

>So what happens? I would update the mapping table and a template and I
>would have an extra field on my web form. Without the need to modify the
>code. That is why ORM layers exist, is it not?
>
>> Also, if someone later deletes or renames a current column, the query
>> will fail, making the problem very obvious.
>
>On the contrary, "SELECT *" will NOT fail.

He meant that if you write your query as "SELECT FirstName, LastName
FROM employees" and someone renamed "FirstName" to "GivenName", then
the query will fail immediately. SELECT * will not. Whether using
SELECT * will cause problems depends on whether your code references
"FirstName" by name.

[snip rest]
--
Charles Calvert | Software Design/Development
Celtic Wolf, Inc. | Project Management
http://www.celticwolf.com/ | Technical Writing
(703) 580-0210 | Research

> Please don't delete attributions; they're important to following the
> conversation. I've readded them.

Then please read them and don't turn things the other way.

>>> SELECT * is not a good thing to use. You are much better off always
>>> specifying the columns.
>>>
>>> For one thing, it's very seldom you need all of the columns.
>> Why put columns in a table if you are not interested in them? It is
>> really rare and a sign of bad datase stucture if a table contains things
>> I do not want to select.
>
> This is a strawman. Jerry suggested no such thing. He said that you
> don't usually need all of the columns for a particular query. IME
> that's quite true.

As I indicated, this is not true for my way of programming. It may be
very well true for yours. But that does not make "SELECT *" bad in all
case. THAT is what Jerry suggested. By the way, this thread was about
dates in queries of that type. Just saying it is bad does not .
Especially when "SELECT *" queries just do have their uses.

> The only time I want all fields from a particular
> table is usually when allowing record additions and editing via a user
> interface. Even in those cases I generally use views because I'm
> joining against related tables. Most of time when I'm querying,
> however, is for reporting, which usually requires only a subset of the
> available columns.

And that is excatly the opposite for most of my programs. My programs
are object oriented, and an object usually "maps" to a row in a
database. If my application evolves, so do both the table and the
object. This is where ORM and "SELECT *" queries are of great value.

So instead of telling that the original poster is wrong, people may
actually consider ing him.

>>> But more importantly, what happens if someone later adds a new
>>> column to the table (i.e. a 5mb BLOB)?
>> Well, first of all, that would be me.
>
> Are you the only one who ever works on the databases you create? If
> so, do you think that your experience is representative?

Yes. As I told above, that is the mere structure of the application. My
programs "depersist objects" far more often than they "report data".

>
>> If I would add such a column, I would have a need for it.
>
> In every single case that you wanted to retrieve data from that table?
> You wouldn't want to ever want to, say, run a query returning all
> unique authors associated with the documents that are in the BLOB
> field?

No, off course not. I did not say that I NEVER used field names, did I?
For searching and reporting, I still use complex JOINs, a subset of
field names, etc. It just so happens that those queries are less than 5%
of my application. That may very well be the opposite for yours, but I
will not tell that to be bad and stop responding to a perfectly normal
question.

>> If that field would only be vaguely connected to the rows in that
>> table, it would be in an only vaguely connected table.
> Sorry, but this doesn't make any sense to me.

If the field would not really be part of the entity that that row (and
that object) represents, it would just not be in that table.

An example:
Suppose the table is a table with images. If I want to retrieve the
image object, I would need the image ("SELECT *"). If I wanted to search
which objects I would want to read, I would use different queries or
already have IDs in other objects. If I wanted to do both, I could
combine them.

>> So what happens? I would update the mapping table and a template and I
>> would have an extra field on my web form. Without the need to modify the
>> code. That is why ORM layers exist, is it not?
>>
>>> Also, if someone later deletes or renames a current column, the query
>>> will fail, making the problem very obvious.
>> On the contrary, "SELECT *" will NOT fail.
>
> He meant that if you write your query as "SELECT FirstName, LastName
> FROM employees" and someone renamed "FirstName" to "GivenName", then
> the query will fail immediately. SELECT * will not. Whether using
> SELECT * will cause problems depends on whether your code references
> "FirstName" by name.

So? The query itself may not fail, but the rest of my code (most
probably the ORM section first) will. Just as immediate. What's the
difference?

Regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

Willem Bogaerts wrote:
>> Please don't delete attributions; they're important to following the
>> conversation. I've readded them.
>
> Then please read them and don't turn things the other way.
>
>>>> SELECT * is not a good thing to use. You are much better off always
>>>> specifying the columns.
>>>>
>>>> For one thing, it's very seldom you need all of the columns.
>>> Why put columns in a table if you are not interested in them? It is
>>> really rare and a sign of bad datase stucture if a table contains things
>>> I do not want to select.
>> This is a strawman. Jerry suggested no such thing. He said that you
>> don't usually need all of the columns for a particular query. IME
>> that's quite true.
>
> As I indicated, this is not true for my way of programming. It may be
> very well true for yours. But that does not make "SELECT *" bad in all
> case. THAT is what Jerry suggested. By the way, this thread was about
> dates in queries of that type. Just saying it is bad does not .
> Especially when "SELECT *" queries just do have their uses.
>

Yes, it is what I suggested. Just because it's "your way of
programming" does not make it good.

>> The only time I want all fields from a particular
>> table is usually when allowing record additions and editing via a user
>> interface. Even in those cases I generally use views because I'm
>> joining against related tables. Most of time when I'm querying,
>> however, is for reporting, which usually requires only a subset of the
>> available columns.
>
> And that is excatly the opposite for most of my programs. My programs
> are object oriented, and an object usually "maps" to a row in a
> database. If my application evolves, so do both the table and the
> object. This is where ORM and "SELECT *" queries are of great value.
>

Not in my experience. It is exactly the way most programmers I've seen
do it. Not necessarily views, but obtaining only a subset of the
available columns.

And the fact your programs and databases evolve makes it even more
important to specify column names.

I do a lot of OO programming, also. And I never use SELECT *, and very
seldom am selecting all columns in a row.

The fact you need to do this regularly is an indication that you may be
overnormalizing your database.

> So instead of telling that the original poster is wrong, people may
> actually consider ing him.
>

We are trying to him. Because it is a poor programming practice.

>>>> But more importantly, what happens if someone later adds a new
>>>> column to the table (i.e. a 5mb BLOB)?
>>> Well, first of all, that would be me.
>> Are you the only one who ever works on the databases you create? If
>> so, do you think that your experience is representative?
>
> Yes. As I told above, that is the mere structure of the application. My
> programs "depersist objects" far more often than they "report data".
>

So you are the only one who works on those databases? And your
experience is representative of the industry?

>>> If I would add such a column, I would have a need for it.
>> In every single case that you wanted to retrieve data from that table?
>> You wouldn't want to ever want to, say, run a query returning all
>> unique authors associated with the documents that are in the BLOB
>> field?
>
> No, off course not. I did not say that I NEVER used field names, did I?
> For searching and reporting, I still use complex JOINs, a subset of
> field names, etc. It just so happens that those queries are less than 5%
> of my application. That may very well be the opposite for yours, but I
> will not tell that to be bad and stop responding to a perfectly normal
> question.
>

You may think it's not bad, but many other, more experienced programmers
will tell you it is. In fact, in my programs, SELECT * is in zero
percent of my queries. That's what virtually all good SQL programmers
recommend - and have been for years.

>>> If that field would only be vaguely connected to the rows in that
>>> table, it would be in an only vaguely connected table.
>> Sorry, but this doesn't make any sense to me.
>
> If the field would not really be part of the entity that that row (and
> that object) represents, it would just not be in that table.
>
> An example:
> Suppose the table is a table with images. If I want to retrieve the
> image object, I would need the image ("SELECT *"). If I wanted to search
> which objects I would want to read, I would use different queries or
> already have IDs in other objects. If I wanted to do both, I could
> combine them.
>

I agree with Charles - this makes no sense to me, either.

>>> So what happens? I would update the mapping table and a template and I
>>> would have an extra field on my web form. Without the need to modify the
>>> code. That is why ORM layers exist, is it not?
>>>
>>>> Also, if someone later deletes or renames a current column, the query
>>>> will fail, making the problem very obvious.
>>> On the contrary, "SELECT *" will NOT fail.
>> He meant that if you write your query as "SELECT FirstName, LastName
>> FROM employees" and someone renamed "FirstName" to "GivenName", then
>> the query will fail immediately. SELECT * will not. Whether using
>> SELECT * will cause problems depends on whether your code references
>> "FirstName" by name.
>
> So? The query itself may not fail, but the rest of my code (most
> probably the ORM section first) will. Just as immediate. What's the
> difference?
>
> Regards,

The difference is the query is now the point of failure. That's the
closest to the actual error. The farther you get from the error, the
harder it is to troubleshoot, and the more time it takes to correct.

I really suggest you ask about SELECT * in comp.databases.mysql. The
experts there will also tell you why it's bad to use.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[attributions readded... again]

On Wed, 20 Feb 2008 10:19:23 +0100, Willem Bogaerts
<w.bogaerts@kratz.maardanzonderditstuk.nl> wrote in
<47bbf09e$0$14343$e4fe514c@news.xs4all.nl>:

>On Tue, 19 Feb 2008 17:54:38 -0500, Charles Calvert <cbciv@yahoo.com>
>wrote in <3nmmr3hvf2qutk9rggrr6u99gcir1kpias@4ax.com>:
>>On Tue, 19 Feb 2008 13:03:43 +0100, Willem Bogaerts
>><w.bogaerts@kratz.maardanzonderditstuk.nl> wrote in
>><47bac59f$0$14355$e4fe514c@news.xs4all.nl>:
>>
>>>On Tue, 19 Feb 2008 06:40:01 -0500, Jerry
>>>Stuckle <jstucklex@attglobal.net> wrote in
>>><25idnRKzfsPjXSfanZ2dnUVZ_t3inZ2d@comcast.com >:
>>
>> Please don't delete attributions; they're important to following the
>> conversation. I've readded them.
>
>Then please read them and don't turn things the other way.

I don't understand this sentence. Removing attributions is rude.
Please don't do it. For your reference:

<http://en.wikipedia.org/wiki/Top-posting#Attribution_lines>
<http://www.i-hate-computers.demon.co.uk/quote.html>
<http://www.cs.indiana.edu/docproject/zen/zen-1.0_6.html>
<http://coherentdog.org/netquote.htm>
<http://linux.sgms-centre.com/misc/netiquette.php#quoting>
<http://www.newsreaders.com/guide/netiquette.html>

>>>> SELECT * is not a good thing to use. You are much better off always
>>>> specifying the columns.
>>>>
>>>> For one thing, it's very seldom you need all of the columns.
>>> Why put columns in a table if you are not interested in them? It is
>>> really rare and a sign of bad datase stucture if a table contains things
>>> I do not want to select.
>>
>> This is a strawman. Jerry suggested no such thing. He said that you
>> don't usually need all of the columns for a particular query. IME
>> that's quite true.
>
>As I indicated, this is not true for my way of programming. It may be
>very well true for yours. But that does not make "SELECT *" bad in all
>case. THAT is what Jerry suggested.

Jerry suggested that SELECT * is generally bad practice, and he
suggested it to someone who, based on the question they were asking,
seemed to be less experienced with databases than Jerry or a lot of
the other regular posters in this group. Was he making an assumption?
Yes, but it was a fairly good one, given the available evidence.

>By the way, this thread was about dates in queries of that type. Just
>saying it is bad does not . Especially when "SELECT *" queries
>just do have their uses.

Jerry was pointing out what he perceived to be a second issue that
arose as a result of the discussion of the first. This happens all
the time.

>> The only time I want all fields from a particular
>> table is usually when allowing record additions and editing via a user
>> interface. Even in those cases I generally use views because I'm
>> joining against related tables. Most of time when I'm querying,
>> however, is for reporting, which usually requires only a subset of the
>> available columns.
>
>And that is excatly the opposite for most of my programs. My programs
>are object oriented,

So are mine.

>and an object usually "maps" to a row in a database.

Ah. So you're using the database as a persistence layer for your
objects. Fine. There's nothing inherently wrong with that and I can
see why you'd want to map all available columns to an object.

>If my application evolves, so do both the table and the
>object. This is where ORM and "SELECT *" queries are of great value.

Okay.

>So instead of telling that the original poster is wrong, people may
>actually consider ing him.

If you go back and reread the responses, you'll notice that he was
ed. AnrDaemon posted the reason behind the difference the O.P.
was seeing and several people suggested solutions. The O.P. raised an
objection to one of the solutions because he was using SELECT * and
didn't want to change that. At this point, Jerry chimed in. It was a
perfectly reasonable thing to do at that point in the conversation.

>>>> But more importantly, what happens if someone later adds a new
>>>> column to the table (i.e. a 5mb BLOB)?
>>> Well, first of all, that would be me.
>>
>> Are you the only one who ever works on the databases you create? If
>> so, do you think that your experience is representative?
>
>Yes. As I told above, that is the mere structure of the application. My
>programs "depersist objects" far more often than they "report data".

I take it that your "yes" was to my first question, not my second. I
can tell you that your model is not the most common one by far. Your
applications are an exception to the rule. Let's assume for the
moment that if I examined your applications that I would agree that
SELECT * is the best way to handle it. Even so, I would still apply
the rule of thumb that SELECT * is not a good way to select data from
a database, generally speaking. There are always corner cases. That
they exist doesn't invalidate good rules of thumb, especially when
those rules of thumb can stop the inexperienced from causing
themselves problems.

>>> If I would add such a column, I would have a need for it.
>>
>> In every single case that you wanted to retrieve data from that table?
>> You wouldn't want to ever want to, say, run a query returning all
>> unique authors associated with the documents that are in the BLOB
>> field?
>
>No, off course not. I did not say that I NEVER used field names, did I?

No, but I wasn't sure that I grasped what you were saying, so I asked
questions to refine my understanding.

>For searching and reporting, I still use complex JOINs, a subset of
>field names, etc. It just so happens that those queries are less than 5%
>of my application. That may very well be the opposite for yours,

Part of my point is that it is the opposite of most database
applications.

>but I will not tell that to be bad and stop responding to a perfectly
>normal question.

I didn't suggest that you stop responding to anything. I'm merely
arguing with you. It's not a personal attack. This is Usenet.

>>> If that field would only be vaguely connected to the rows in that
>>> table, it would be in an only vaguely connected table.
>> Sorry, but this doesn't make any sense to me.
>
>If the field would not really be part of the entity that that row (and
>that object) represents, it would just not be in that table.
>
>An example:
>Suppose the table is a table with images. If I want to retrieve the
>image object, I would need the image ("SELECT *"). If I wanted to search
>which objects I would want to read, I would use different queries or
>already have IDs in other objects. If I wanted to do both, I could
>combine them.

I understand your example, but not your point. Your original
paragraph still doesn't make sense to me. It's quite common to have
columns in a table that are infrequently used, but still correctly
part of a normalized table.

>>> So what happens? I would update the mapping table and a template and I
>>> would have an extra field on my web form. Without the need to modify the
>>> code. That is why ORM layers exist, is it not?
>>>
>>>> Also, if someone later deletes or renames a current column, the query
>>>> will fail, making the problem very obvious.
>>> On the contrary, "SELECT *" will NOT fail.
>>
>> He meant that if you write your query as "SELECT FirstName, LastName
>> FROM employees" and someone renamed "FirstName" to "GivenName", then
>> the query will fail immediately. SELECT * will not. Whether using
>> SELECT * will cause problems depends on whether your code references
>> "FirstName" by name.
>
>So? The query itself may not fail, but the rest of my code (most
>probably the ORM section first) will. Just as immediate. What's the
>difference?

As Jerry pointed out, it's easier to troubleshoot when the error is
manifested closer to the source.

There's another issue that has been implied, but not stated
explicitly. SELECT * can be a performance nightmare when the table
has many records, when the connection is slow, when the server has to
handle many requests or any combination of the above.

Like any programming construct, SELECT * has its uses. Unfortunately,
it is often used incorrectly by the inexperienced, hence the general
rule to not use it in production code. That's what Jerry was getting
at. If I were to codify the rule, it would be something like this.

Rule 1: Don't use SELECT *
Rule 2: (For the experienced only) Don't use SELECT * in production
code
Rule 3: (For experts only) Don't use SELECT * unless nothing else will
do

The third rule may apply in your situation. It's unlikely that it
does in the O.P.'s.
--
Charles Calvert | Software Design/Development
Celtic Wolf, Inc. | Project Management
http://www.celticwolf.com/ | Technical Writing
(703) 580-0210 | Research