16.20 - Understanding Large Object Support - Teradata SQL Assistant

Teradata® SQL Assistant for Windows User Guide

Product
Teradata SQL Assistant
Release Number
16.20
Published
October 2018
Language
English (United States)
Last Update
2020-01-03
dita:mapPath
gfk1537201040714.ditamap
dita:ditavalPath
ft:empty
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.

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, you are prompted 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 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 to determine how to read the file. Others do not expect a prefix and would therefore be confused by its presence.