On 30-Nov-2015 17:38 -0600, Buck Calabro wrote:
SQLCOLUMNS does not appear to understand 'Column Heading'.
Look at <ed: SYSCOLUMNS> instead to see the results of LABEL ON.
Looking at the ODBC catalogue page <ed: SQLCOLUMNS VIEW>
[http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzcatsqlcolumns.htm]
I see that REMARKS is intended to be set via COMMENT, but
COLUMN_HEADING makes no mention of how that is supposed to be set.
I see the text "A character string supplied with the COMMENT
statement." for REMARKS. However...
I was unable to find any reference to either COLUMN_HEADING [or
LABEL] in the SQLCOLUMNS docs on that [above] link. I presumed
possibly, the reference to COLUMN_HEADING applied to what was found in
the following link instead of what was found in the preceding link. But
then, when reviewing the docs on that [below] link, there I found the
text "A character string supplied with the LABEL statement (column
headings)" for COLUMN_HEADING. Call me confused.?.?
Hmm, thinking on this further, reading back to the OP, I infer more
likely, is that COLUMN_HEADING [in above quoted text] was meant to be
COLUMN_TEXT. And while SQLCOLUMNS docs fails to include the [somewhat
expected for consistency] text "A character string supplied with the
LABEL statement (column text)", given that column is merely indirection
from COLUMN_TEXT of SYSCOLUMNS, I went looking at that doc [next URL in
the quoted text] to find that indeed the [omitted short name LABELTEXT
FOR] COLUMN_TEXT includes exactly that above\expected text "A character
string supplied with the LABEL statement (column text)".
FWiW, that definition of that SQLCOLUMNS catalog VIEW may need an
enhancement, *if* the level of ODBC the OS is intended to be support for
beyond ODBC 1.0, per my comment:
[
http://archive.midrange.com/midrange-l/201510/msg00683.html]
"Since ODBC 1.0 <ed: there> was added the SQL_DESC_LABEL to the
sqlColAttribute function. AIUI, that data correlates directly to the
effect of the LABEL ON COLUMN ... IS statement's string-constant data;
i.e. the "LABEL" [aka COLUMN_HEADING] from SYSCOLUMNS."
And from that same archived reply, I suggested that possibly "IIRC,
the sqlColumns() API results from IBM i drivers originally [and likely
still do] effect for the REMARKS, the expression of COALESCE(remarks,
labeltext, "LABEL") against SYSCOLUMNS catalog data. Therefore the
TEXT() and COLHDG() *may* have value to requests made from elsewhere,
if\when there was no COMMENT ON COLUMN done for that particular
COLUMN_NAME." I wonder if the effect I recall, instead might have been,
that the effect for the COLUMN_TEXT was reflected as what would be
effectively COALESCE(labeltext, "LABEL") against SYSCOLUMNS data.?
Perhaps someone can persuade IBM to update SQLCOLUMNS to make such a
change [e.g. TRIM(COALESCE(COLUMN_TEXT,"LABEL")) AS COLUMN_TEXT], and\or
update that VIEW to reflect the more recent ODBC specification for which
"LABEL" [perhaps named DESC_LABEL] is included. FWiW, either the docs
for SQLCOLUMNS are incorrect already anyhow per a failure to reflect all
of the VARGRAPHIC updates to the based-on VIEW SYSCOLUMNS, or the
SQLCOLUMNS is incorrect because the VIEW does not reflect the docs.
Someone could submit a comment on the docs to get them to /correct/ one
and\or the other for proper reflection of what should be expected on the
system, and even include a /suggestion/ about the SQL_DESC_LABEL which
if not provided explicitly could be provided in a COALESCE of the
COLUMN_TEXT data.
Looking at the DB2 for i catalogue page <ed: SYSCOLUMNS VIEW>
[http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzcatsyscol.htm]
I see that LONG_COMMENT is intended to be set via COMMENT and
COLUMN_HEADING is intended to be set via LABEL.
Notable: the short field name [not provided in those docs] for
LONG_COMMENT is REMARKS, for COLUMN_TEXT is LABELTEXT, and for
COLUMN_HEADING is LABEL.
For completeness, the ISO catalogue <ed: COLUMNS VIEW>
[http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzcatcolumns.htm]
does not appear to have either of those columns.
I am at 7.2.
midrange.com
