Re: Speaking of hex fields, was Re: What exactly is a "Binary Character" field?

On 17-Jun-2015 13:31 -0600, James H. H. Lampert wrote:

<<SNIP>>

Does anybody know of a way to tell a hex field from an alphanumeric
field, purely through the SQL metadata? As far as the metadata is
concerned, the records in QSYS2/COLUMNS look exactly the same for
hex and alphanumeric, and so does the metadata Squirrel presents in
the "Columns" tab, and yet an SQL fetch returns 8 characters for an
8-byte alphanumeric field, but 16 hex digits for an 8-byte hex field.

The conspicuous difference is the Character Field CCSID (CCSID) column of SYSCOLUMNS catalog VIEW. The H=Hexadecimal, xxxCHAR FOR BIT DATA [and BINARY] columns will have CCSID=65535. Otherwise..

From a generic\purely SQL perspective, there is no difference between BINARY, Hex, and FOR BIT DATA; i.e. the columns are all to be treated as binary\image data for lack of any associated encoding scheme. Thus what is manifest to the SQL clients via the SQL catalogs need not differentiate those three. However the differentiation is available in the Database Cross Reference (DBXREF) file QADBIFLD in the column for Internal Type (DBIITP).