2842:InterBase BLOB Fields: A Primer
KEYWORDS: LIBS INTERBASE BLOB MEMO IMAGE BITMAP FIELD AREA: Database Programmi
InterBase BLOB fields are not all the same. They actually consist in a
variety of forms, or sub-types of the general BLOB type. Knowing which
sub-type of BLOB field to use when is essential to creating database appl-
ications that incorporate InterBase BLOB fields. BLOB fields come in three
varieties: sub-type 0 and sub-type 1 (the two predefined sub-types), and
user-defined sub-types.
Sub-type 0 BLOB fields are the type created when a CREATE command is
issued and a sub-type is not specified. For clarity in SQL syntax, though,
it is possible to explicitly indicate that the BLOB field is to be of sub-
type 0. This sub-type of BLOB field is for the storage of binary data.
InterBase makes no analysis of the data stored, it just stores it in the
BLOB field on a byte-for-byte basis. The most common intended use for BLOB
fields in Windows applications is the storage of bitmap binary data, typi-
cally for display in a TDBImage component. Either the BLOB field sub-type
0 or a user-defined sub-type BLOB field will work for this purpose.
The second predefined sub-type is 1. This BLOB field sub-type is designed
for the storage of text. Typically, this is the free-form memo or notes
data displayed and edited with the TDBMemo component. This BLOB field sub-
type is better for storing text data than the VARCHAR field because,
unlike with the VARCHAR field, there is no design-time limit placed on the
storage capacity of the field.
In SQL syntax, the sub-type 1 BLOB field is created by following the BLOB
field type keyword with the SUB_TYPE keyword and the integer one:
CREATE TABLE WITHBLOB
(
ID CHAR(3) NOT NULL PRIMARY KEY,
MEMO BLOB SUB_TYPE 1,
AMOUNT NUMERIC
)
Aside from the two predefined BLOB field sub-types, there are user-defined
sub-types. User-defined sub-types are designated by a negative integer
value in association with the SUB_TYPE keyword. The actual integer value,
as long as it is negative, is actually arbitrary and up to the discretion
of the table creator. A designation of -1 is functionally the same as that
of a -2. The only consideration when using user-defined sub-types is
ensuring that the same type of binary data is stored for every row in the
table for a BLOB field of a given user-defined sub-type. InterBase will
not evaluate whether this criteria is met, and it is the responsibility of
the application inserting the binary data to store the appropriate type of
data. No error will occur from the InterBase side if an incorrect type of
binary data is stored in a user-defined BLOB field sub-type, but an appl-
ication can incur difficulties if it is expecting one type of data but
encounters another.
A BLOB field of a user-defined sub-type is created with the SQL syntax
such as that below:
CREATE TABLE IMAGE_DATA
(
FILENAME CHAR(12) NOT NULL PRIMARY KEY,
BITMAP BLOB SUB_TYPE -1,
EXEs BLOB SUB_TYPE -2,
)
When using a table created with the above command, the field BITMAP would
only be used to store one distinct type of binary data for all records. In
this case, bitmap data. The field EXEs implies the storage of executable
files loaded from disk. If an application using this table were to mis-
takenly store binary data that should have been in the EXEs field into the
BITMAP field, InterBase would generate no errors, but the application
would have extreme difficulties displaying a stored executable file in a
TDBImage component.
InterBase BLOB fields and Delphi
--------------------------------
When defining TField objects for InterBase BLOB fields in Delphi, the
various BLOB field sub-types are assigned TField derivative types as
follows:
Sub-type 0: TBlobField
Sub-type 1: TMemoField
User-defined: TBlobField
Because both the predefined sub-type 0 and user-defined sub-types are
recognized as TBlobField objects, care must be taken when designing an
application to not mistake a field of one sub-type for that of another.
The only way to differentiate between a field of sub-type 0 from that of
a user-defined type is by viewing the metadata information for the table,
which cannot be done from within Delphi. The Local InterBase Server
utility WISQL can be used to view table metadata.
InterBase BLOB fields and Database Desktop
------------------------------------------
The Database Desktop utility that comes with Delphi (DBD) does not create
user-defined subtypes. When using BLOB fields created in Database Desktop,
use the "BLOB" field type for binary data, including bitmap data. This
creates a BLOB field of the predefined sub-type 0.
The DBD also offers a BLOB field type TEXT BLOB. This is equivalent to the
pre-defined subtype 1, and should be used where free-form text storage
will be needed. While it is functionally equivalent to the pre-defined
subtype 1 BLOB field, it will appear with a slightly different type des-
ignation if you view the metadata for the table in the WISQL utility.
TI
LiveJournal
PhotoFile