On 01-Dec-2015 10:55 -0600, Vernon Hamberg wrote:
On 12/1/2015 8:43 AM, CRPence wrote:
On 30-Nov-2015 16:56 -0600, Rob wrote:
<<SNIP>>
[ COMMENT ON COLUMN schema.table.column IS "Field Comment" ] Runs
in System i Navigator without an error.
[ LABEL ON COLUMN schema.table.column IS "Field Label" ] also Runs
in System i Navigator without an error.
But neither change COLUMN_TEXT.
<<SNIP>>
The former statement should have changed the REMARKS. The latter
statement makes changes that are not visible\manifest in the data
available from the SQLCOLUMNS VIEW [unless the VIEW is customized,
or has been updated by IBM to reflect a newer ODBC [than 1.0]
specification.
Try instead:
LABEL ON COLUMN schema.table.column TEXT IS 'Field Text'
Or [better IMO], enabling one statement to modify multiple
columns, use the following syntax instead:
LABEL ON COLUMN schema.table
( column1 TEXT IS 'Field Text'
, [...]
, columnN TEXT IS 'Column Text'
)
<<SNIP>>
You may have seen my post where I recommend NOT using SQLCOLUMNS,
rather, to use SYSCOLUMNS.
Yes. Both you, and Buck. But that could be somewhat like saying
"Use C" to an RPG programmer; I do not recall clarification by the OP as
to why one is being used vs another, or if any followup ruled-out or
confirmed that was an option for them.
I suggested another approach [though I would not recommend
vociferously], to customize that VIEW. or to ask for a clarification or
possible /correction/ regarding what the SQLCOLUMNS VIEW offers, because
the ODBC specs were updated to include something more in SQLColumns()
API than is apparently being included in the VIEW SQLCOLUMNS. That is,
if there is a SQL_DESC_LABEL of ODBC that should correlate to
COLUMN_HEADING but the SQLCOLUMNS VIEW provides only COLUMN_TEXT, then
that VIEW could be enhanced to provide the first 50 characters of
"LABEL" as COLUMN_TEXT when the latter is NULL, or even just provide the
effective COLUMN_HEADING *while keeping* the remaining columns
unchanged; i.e. the OP may have a reason to use SQLCOLUMNS, for what the
SYSCOLUMNS does not provide, at least not manifest in the same manner.
Briefly,
LABEL ON COLUMN [column-name] IS 'Field Label'
update COLUMN_HEADING in SYSCOLUMNS - nothing in SQLCOLUMNS, which
knows nothing about column headings (the 3-part, each 20 long stuff).
Thus why an inquiry as to why SQLCOLUMNS does not provide what
SQLColumns() provides in a newer specification, could potentially prove
fruitful; barring or pending that, the VIEW_DEFINITION [aka TEXT] of the
SQLCOLUMNS VIEW could be retrieved and then modified to include the
equivalent of COLUMN_HEADING, from which a CREATE VIEW SQLCOLUMNS could
be performed to replace the existing copy [and placed under system
change management, to ensure the same action\customization is repeated
whenever the object has changed since the last time that custom update
was effected].
There is little reason I can see why SQLCOLUMNS could not be updated
to return a separate COLUMN_HEADING [aka LABEL], thus reflecting the
newer specification, or [the first 50 characters of] the LABEL if
COLUMN_TEXT is NULL so as to satisfy the concept of the OP to get the
information [possibly] with the least updates; i.e. just the LABEL ON
COLUMN ... IS, vs both that and LABEL ON COLUMN ... TEXT IS:
varchar( TRIM(COALESCE(COLUMN_TEXT,"LABEL")), 50)
I did try a COMMENT ON TABLE, and that comment *is* stored in the
actual PF object - even a regular PF, not a TABLE.
Yes, stored in the *FILE space object.
And in the catalogs; DBXREM of QADBXREF, and manifest in some SQL
catalog VIEW(s) such as SYSTABLES as REMARKS [aka LONG_COMMENT].
I also did a COMMENT ON COLUMN, and it is also stored in the PF in
the list of columns you see in a DMPOBJ output.
Stored in the separate *FMT space object; as a composite piece of the
*FILE, rather than being stored in the *FILE space object.
And in the catalogs; DBIREM of QADBIFLD, and manifest in some SQL
catalog VIEW(s) such as SYSCOLUMNS as REMARKS [aka LONG_COMMENT].
And in the Retrieve File Description (QDBRTVFD) API which maps
[possibly somewhat directly] what was seen in the Dump Object (DMPOBJ)
output, in Qdbfdict as the "Data dictionary file definition comment
text", in Qddfdfct as the "Field definition comment text", and for what
is irrelevant\unsupported for the SQL, in Qddfditx as the "Format
definition long comment".
These "remarks" are not visible, however in DSPFD for the table or
in DSPFFD for the columns.
Correct. They were not deemed worthwhile to include there; too much
text, although they should be available via the same interface and
QPDSPFD and QPDSPFFD with 6=Print from the Work With Data Definitions
(WRKDTADFN) of the Interactive Data Definition Utility (IDDU). The Long
Comments [REMARKS] were available from the Data Dictionary (*DTADCT)
files [physical file QIDCTP02, field Q02CTX; stored in multiples of 256
characters and possibly restricted to a /practical/ limit of ~1360
characters for viewing vs the actual limit of 2000 of the SQL REMARKS]
when the COLLECTION was implemented WITH DATA DICTIONARY [or for
definitions of an IDDU Data Dictionary], but since v3r1 the WITH DATA
DICTIONARY is deprecated, and the preferred method to obtain the
information is from the catalogs.
And as noted in past discussion, the F23=Long Comment of the
Query/400 [IBM i Query] supports all three types [fil, rcdfmt, fld] of
comments\remarks, and that [oddly] the SQL 5250 interfaces seem never to
have bothered, despite the SQL being the only non-deprecated interface
to the feature.
midrange.com
