Re: SQL view *pointer to a BLOB

On 20-Nov-2014 15:47 -0600, Jack Tucky wrote:

On 19-Nov-2014 16:08 -0600, Bruce Vining wrote:

I agree with the need to show only hex values (in character form)
from a BLOB, but I'm a bit confused by STRSQL not allowing any
access to LOBs of various types. If I have a CLOB column XYZ in
table MyTable then STRSQL and

Select cast(XYZ as varchar(32000)) from MyTable

seems to work for me. I'm most likely mis-understanding your post.

<<SNIP>>
I'm using a BLOB. Maybe last time it was a CLOB? But I remember being
able to see the location of the file.

AFaIK there is no support to cast from a BLOB to any /character/ string data types. Instead, cast the BLOB to one of either VARBINARY or BINARY.

I could not find the specific documentation I was looking for [a table of supported mappings\castings for SQL data types], but the following reference is for the Query/400 [DB2 Query for i] feature that is used as the report-writer [for local database connection] queries:
<http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/rzahf/rzahfquery.htm>
_BINARY, VARBINARY, HEX, and LENGTH functions for Query_
"This topic describes the BINARY, VARBINARY, HEX, and LENGTH built-in functions that are supported by the IBM® Query for i licensed program.
..."

The same SQL scalar functions [as the named built-in function for the Query feature] are available via the SQL; the SQL supports additionally the CAST scalar. Thus if XYZ in Bruce's example was BLOB vs CLOB, then [but only if the BLOB data is character data that would produce (mostly just) visible glyphs] the modified query might be:

Select cast(XYZ as varbinary(32000)) from MyTable