Is there any DB2 ODBC DSN parameter that will allow DB2 BLOB fields to be seen as RichText? Currently, I am running DB2 9.7 FP9, but soon to go to DB2 10.5 (not sure which FP). The issue is that, for example, is this:
I create an ODBC DSN to my DB2 DB.
Within MS Access, I link to tables.
BLOB fields are automatically interpreted as Short Text (maximum 255 characters).
This precludes reading the full content of the field.
I know several things:
1. It is something in the ODBC driver. Access has a Long Text format that would work for this. But when I connect to the BLOB field via ODBC, the ODBC driver presents it to Access as Short Text.
2. The BLOB field is in RichText format within DB2. That is, I can create a new table & field within Access, then directly in DB2, run a query to retrieve the raw RTF content of a BLOB field in a record. When I copy and paste that into my Long Text field in Access and then display it on a form--it correctly appears formatted, with correct font styles and sizes & colors.
But I cannot see any of the many ODBC parameters that can be configured within the DSN to tell ODBC to interpret BLOB fields correctly as Long Text (used to be called Memo), or RTF fields.
Best Regards, Guy Przytula
Database Software Consultant
Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
Information Server Datastage Certified http://www.infocura.be
That got me part way. CLOBs now appear as Long Text; however, BLOBs now appear as OLE objects, which would normally be binary content that would be, for example, images or even programs (i.e. executable files) embedded within the field. So reporting applications, such as both Access and Crystal Reports, want to treat the content as a file, not as Rich Text. For that, I would need a way to have the ODBC driver present a BLOB the same way it now presents a CLOB. That is, it needs to simply recognize the field as a very wide text field, not as a file..
Is there, perhaps, a way to CAST a BLOB as a VARCHAR or CLOB? That would solve my problem. I see that there is no direct way to cast a BLOB as a CLOB, even if the BLOB content can easily be represented as a CLOB. And right now, when I simply CAST(MyField as VARCHAR(254)), the result is in VARBYTES, and if I cast over 254 characters (which is what I really need to do), the result remains as BLOB. There is no reason that the content of my field, begin simple text characters, should not be able to be represented as just a long string, with the work of interpreting the RTF formatting left to the client application (e.g. Crystal Reports or MS Access).