Re: MSSQL Server 2005 - select [field] AS [name] does not work?

On Tue, 15 May 2012 15:21:29 +0000
Neil Whitaker <[email protected]> wrote:

> It's a bit of a strange one as you can't very easily search for it
> ('AS' isn't the best keyword to try and find!), I tried it using
> isql, and it does work:
> 
> SQL> select TOP 10 [EnquiryID] AS [Pri_Key] FROM [db].[dbo].[table];
> +------------+
> | Pri_Key    |
> +------------+
..
> So does this support your suggestion?  It's worked with FreeTDS
which
> shows it's getting the right data, but when used with PHP it's
> munging the wrong field names?

Yes, that's what I meant.  The ODBC API provides access to both names.
If you choose the wrong one, you get the wrong one.  ;-)

http://msdn.microsoft.com/en-us/library/ms713558(v=vs.85)

Compare in the ODBC API function SQLColAttribute the FieldIdentifiers
SQL_DESC_BASE_COLUMN_NAME and SQL_DESC_LABEL.  

It might be that someone misunderstood the version numbers in the table
of FieldIdentifiers linked to above.  The table appears under the
heading "Backwards Compatibility" but actually applies to  all
versions.  SQL_DESC_LABEL was supported (as SQL_COLUMN_LABEL, it seems)
under ODBC 2.0, and SQL_DESC_BASE_COLUMN_NAME first appeared in ODBC
3.0.  SQL_DESC_LABEL is part of the 3.0 specification.  It is not
superseded and continues to be the right one to use.  In fact, the
presence of SQL_DESC_BASE_COLUMN_NAME in the standard is a mistake.  

As long as I've waded in this far, I might as well defend that
assertion.  

Finding flaws in ODBC is like shooting fish in a barrel, and this is a
good example of a bad feature engendered, no doubt, by wrongheaded
demands. (I can hear the customer demanding, "but I need to know the
*real* column name!")  

SQL_DESC_BASE_COLUMN_NAME should never have appeared in
SQLColAttribute.  It is useless when not redundant.  The caller has no
rightful need of it, because the only time it "works" is to unveil the
renaming effect of "AS" in the SQL the application only just *sent* to
the server moments before!  Columns produces by scalar functions (e.g.
count(*)) have no "base" column name; columns produced by views,
functions, and stored procedure do, but in what sense are they "base"?
Does SQL_DESC_BASE_COLUMN_NAME reveal the name of the table used by a
view to produce a column?  If only.  

This would be a *great* feature if it actually worked, if every column
in every query could be attributed to its base table.  A server that
could do that could update views without restriction.  But that's a
massive job with unsolved theoretical problems.  

Instead we have a broken feature, something that was easy to create but
serves mostly to confuse.  Very few ODBC programmers understand what
SQL_DESC_BASE_COLUMN_NAME does, and none need it.  But a great many are
confused by it, and still more suffer at their hands if they choose
wrongly.  Which certainly seems to be the case here.  

--jkl