Displaying BLOBs and CLOBs

Dealing with LOBs

BLOB and CLOB columns are difficult to display because:

• they are often very large, potentially several megabytes
• BLOBs often contain hex data, which is difficult to display meaningfully in the normal results grid

BLOBs and CLOBs are known generically as LOBs.

In addition, BLOBs and CLOBs often need to be sent to an external application to be displayed. For instance, an image file held in a BLOB column will need to be displayed using an image-viewer such as MS Paint. A Word document will need to be sent to MS Word.

Setting LOB options

To deal with these requirements, AQT has an option to export a LOB column to a file. Select this option by Options > Display Options > How to Display Lobs. Select Write LOBs to Files.

You can also select what column-types this is done for: BLOBs, CLOBs and LONG VARCHAR. To explain what these types mean:

• BLOBs means BLOB (DB2/Oracle), IMAGE and LONG VARBINARY (Sybase, SQL Server)
• CLOBs means CLOB (DB2)
• LONG VARCHAR means LONG VARCHAR (DB2), CLOB and LONG (Oracle), TEXT and NTEXT (Sybase, SQL Server) and MEMO (Access). It also includes any VARCHAR column that is longer than 5K bytes.

When you use this option:

• every LOB value will be written as a separate file
• when you display a table / result-set, the name of the LOB file will appear in the display
• when you double-click a LOB filename it will show you the Detail of a Data Value window. The first 1000 bytes of the LOB file will be shown. You can use Show As to display the LOB using a particular program.
• alternatively, from the results-grid, right-click a lob filename and use View As.

Storing and clearing LOBs

• the BLOB files are stored in directory lobs within the AQT default directory.
• the contents of the lobs directory is cleared out every time you switch on the Send to Files option.
• when you close a result-window, AQT will delete all the lob-files used in that display. If you wish to “permanently” save a lob-file, right-click it and select Copy LOB File to.
• If you wish to save the lob-files permanently, you can use the Export Data function. This can export LOBs to a directory which is not purged.

Problem when running multiple AQT Sessions

If you are running multiple AQT sessions, then it is possible for these sessions to interfere with each others LOB files. This is because both these sessions will use the same LOB directory and it could happen that they use the same LOB file names.

This issue is more of a problem in batch AQT as some functions (such as Data Compare) can retain the LOB files for the duration of the batch session.

To avoid such issues, you can manually set the directory used for LOBs. This is done with the following AQT scripting command:

--aqt options,lobdir="c:\aqt\workfiles\lobs"

Performance issues with LOBs

• the system will run slower with this option switched on. Not only are you creating a lot of files; but you will also be pulling a lot more data from your database server.
• if you return a large result-set, or have very large LOBs, then you can fill up your disk very fast. Use this option with a degree of caution.

Unicode

If you have enabled Unicode, AQT will write all CLOBs and Long Varchars values as unicode files. If you don't want your files in this format, you should disable Unicode (see Options > Unicode Options).

Limitations and issues with LOBs

The Row Detail window does not (yet) display LOB values. In a future release we may amend this to display the LOB files, plus edit and create LOBs (by linking to the appropriate external application).

Oracle ODBC driver needed

• if you are an Oracle user, you need to use the Oracle ODBC driver rather than the Microsoft ODBC Driver for Oracle (which does not support BLOBs).

Displaying LOBs with Sybase

In order to display Sybase LOBs, you need to have a high value set for your TEXTSIZE setting. This can be done with (for example):

set textsize 20971520

Alternatively, you can change the Default Buffer Size for Long Columns setting on the configuration of the ODBC Datasource.