Binary Data Types
There are three binary data types:
• Byte
• Varbyte
• Long byte
Binary columns can contain data such as graphic images, which cannot easily be stored using character or numeric data types.
Synonyms for byte, varbyte, and long byte are binary, varbinary, and binary large object, respectively.
Byte Data Type
The byte data type is a fixed length binary data type. If the length of the data assigned to a byte column is less than the declared length of the column, the value is padded with zeros to the declared length when it is stored in a table. The minimum length of a byte column is 1 byte, and the maximum length is limited by the maximum row width configured but not exceeding 32,000.
Byte Varying Data Type
The byte varying data type is a variable length data type. The actual length of the binary data is stored with the binary data, and, unlike the byte data type, the data is not padded to its declared length. The minimum length of a byte varying column is 1 byte, and the maximum length is limited by the maximum row width configured but not exceeding 32,000.
Long Byte Data Type
The long byte data type has the same characteristics as the byte varying data type, but can accommodate binary data up to 2 GB in length.
If a length is declared for this type of column, it represents the size of the inline space to store the initial n bytes of data for the large object. For more information, see Storing BLOBs Inline the Database Administrator Guide.
In embedded SQL
data handlers can be created, which are routines to read and write the data for long byte columns. For details about data handlers, see
Data Handlers for Large Objects (see
Data Handlers for Large Objects on page 285) and the
Embedded SQL Companion Guide.
Restrictions on Long Byte Columns
The following restrictions apply to long byte columns:
• They cannot be part of a table key.
• They cannot be part of a secondary index.
• They cannot be used in the ORDER BY or GROUP BY clause of a SELECT statement.
• They cannot have query optimization statistics. For details about query optimization statistics, see the optimizedb command in the Command Reference Guide.
• The following string functions do not work with long byte columns:
– LOCATE
– PAD
– SHIFT
– SQUEEZE
– TRIM
– NOTRIM
– CHAREXTRACT
– BYTEEXTRACT
– LEFT
– RIGHT
– SUBSTRING
• They cannot be directly compared to other data types. To compare a long byte column to another data type, apply a coercion function.
• A declared length for this type of column represents the size in bytes of the inline space, not a maximum size.
• A literal of more than 2000 bytes cannot be assigned to a long byte column. For details about assigning long values to long byte columns, see the description of data handlers in the
Embedded SQL Companion Guide,
Dynamic Programming on page 267, or the
OpenAPI User Guide.
Risk of Hardware Dependent SQL Code when Using Binary Data
Caution! SQL using binary data may not return the expected values. When using binary data types, it is possible to create hardware dependent SQL.
To demonstrate “hardware dependent SQL when using binary data,” create a table with data as follows:
CREATE TABLE t1 (code INT);
INSERT INTO t1 VALUES (0);
On most-significant-byte-first big-endian systems, the query:
SELECT LENGTH(CHAR(BYTE(code+48))),
RIGHT(CHAR(BYTE(code+48)),1)
FROM t1
returns:
+------+------+
| col1 | col2 |
+------+------+
| 4 | 0 |
+------+------+
Check the hex presentation of the value BYTE(code+48):
SELECT LENGTH(CHAR(BYTE(code+48))),
RIGHT(CHAR(BYTE(code+48)),1),
HEX(BYTE(code+48))
FROM t1
This query returns:
+------+------+--------+
| col1 | col2 | col3 |
+------+------+--------+
| 4 | 0 |00000030|
+------+------+--------+
For Ingres on an Intel x86 (increasing numeric significance with increasing memory addresses—little-endian) system, the same queries return:
+-------------+--------+
| col1 | col2 |
+-------------+--------+
| 1 | |
+-------------+--------+
+-------------+--------+----------------+
| col1 | col2 | col3 |
+-------------+--------+----------------+
| 1 | |3000000000000000|
+-------------+--------+----------------+
Last modified date: 11/28/2023