Understanding Large Object Support - SQL Assistant

Teradata SQL Assistant for Microsoft Windows User Guide

SQL Assistant
Release Number
English (United States)
Last Update
Product Category
Teradata Tools and Utilities

Teradata SQL Assistant supports Large Objects. Large objects come in two types:

  • Binary – These columns can contain Pictures, Music, Word documents, and so on. The column type used by different vendors can be any of BLOB, IMAGE or LONG RAW.
  • Text – These columns contain text data such as Text, HTML, XML or Rich Text (RTF). The column type used by different vendors can be any of CLOB, TEXT, LONG or XML.
  • Note: When using ODBC, the ODBC Data Source option “Use Native Large Object Support” must be selected to retrieve large objects. Go to Tools > Define ODBC Data Source > Configure > Options and select Use Native Large Object Support.

    When an Answerset contains a Large Object (LOB) column the action taken depends on the setting for the option “Use default file names for LOB columns.”

    If this option is selected, Teradata SQL Assistant uses the column title as the file name stub and a default value for the file type: ‘txt’ or ‘xml’ for CLOB data, or the value specified for “Default file type for BLOB columns” for BLOB data.

    If the option is not selected, SQL assistant asks you to provide a file name, a file type, and (optionally) a directory for each Large Object column. Each LOB data value is written to a separate file.

    The file name for each data value is created by joining the name, a sequential number, and the file type.

    For example, if you provide 'Photo' as the name and 'jpg' as the type, the files are named Photo001.jpg, Photo002.jpg, and so on.

    Although you can change the directory each time SQL Assistant requests a file name, only the last directory specified for a given Answerset is used. This directory is used for all LOB files related to this Answerset.

    The file names appear as links within the Answerset grid. If you click on a link, the corresponding file opens. This feature relies on Windows File Associations so you must specify the file type that is appropriate for the type of data stored in the LOB column.

    If the data is a graphic (file type BMP, JPG, JPEG, GIF, PNG, TGA, TIF, ICO or WMF) SQL Assistant offers you the option of displaying the picture within the Answerset grid itself. You can request that the picture be displayed instead of being saved to a file or in addition to being saved to a file.

    If the LOB is a Text type then there will be two additional, optional fields:

  • The file encoding to use
    The encoding tells the system what character set encoding to use when writing the file. The default is UTF8. This writes a single byte for each of the standard ASCII characters, but writes two or three [encoded] bytes for each character that is not part of the standard ASCII character set. (The file will be identical to an 'ANSI' encoded file if it contains only standard ASCII characters.) The other option is Unicode which writes 2 bytes for each character.
  • A checkbox that specifies whether a Byte Order Mark (BOM) should be written
    The BOM is a two or three byte prefix that tells Windows what type of encoding was used. Some applications expect this prefix in order to determine how to read the file. Others do not expect a prefix and would therefore be confused by its presence.