Progress
SQL-92
Guide and Reference
Bit String Data Types
This is the syntax for a bit_string_data_type:
BIT
Corresponds to a single bit value of 0 or 1.
SQL statements can assign and compare values in
BIT
columns to and from columns of typesCHAR
,VARCHAR
,BINARY
,VARBINARY
,TINYINT
,SMALLINT
,and INTEGER.
However, in assignments fromBINARY
andVARBINARY
, the value of the first four bits must be 0001 or 0000.No arithmetic operations are allowed on
BIT
columns.BINARY [ ( length ) ]
Corresponds to a bit field of the specified length of bytes. The default length is 1 byte. The maximum length is 2000 bytes.
In interactive SQL, INSERT statements must use a special format to store values in
BINARY
columns. They can specify the binary values as a bit string, hexadecimal string, or character string. INSERT statements must enclose binary values in single-quote marks, preceded byb
for a bit string andx
for a hexadecimal string.
Table 2–1: Specification Formats for Binary Values Specification Format Example bit string b’ ’ b’1010110100010000’ hexadecimal string x’ ’ x’ad10’ character string ’ ’ ’ad10’
SQL interprets a character string as the character representation of a hexadecimal string.
If the data inserted into a
BINARY
column is less than the length specified, SQL pads it with zeros.
BINARY
data can be assigned and compared to and from columns of typeBIT
,CHAR
, andVARBINARY
. Arithmetic operations are not allowed.VARBINARY ( length )
Corresponds to a variable-length bit field of the specified length in bytes. The default length is 1 byte. The maximum length is 31995 byte. The default length is 1.
NOTE: Due to index limitations, only the narrowerVARBINARY
columns can be indexed.LVARBINARY ( length )
Corresponds to an arbitrarily long byte array with the maximum length defined by the amount of available disk storage up to 2,000,000,000. A BLOB is an object of data type
LVARBINARY
.Maximum length for VARBINARY
Specifically, the maximum length of the VARBINARY data type is:
The maximum length of the VARBINARY data type depends on:
LVARBINARY Limitations
Current limitations for LVARBINARY support are listed below:
- LVARBINARY data type will only be accessible from the SQL Engine. LVARBINARY data columns added to tables created by the 4GL are not visible to the 4GL. ESQLC does not support this data type.
- LVARBINARY data columns cannot be part of an index.
- LVARBINARY data columns cannot be used for variables or as parameters in stored procedures.
- Comparison operations are not supported on LVARBINARY columns. Comparison operations between LVARBINARY columns are not supported. Comparison operations between LVARBINARY columns and columns of other data types are not supported.
- Conversion, aggregate, and scalar functions are disallowed on this data type.
- LVARBINARY does not have National Language Support (NLS).
Language Support for LVARBINARY
This data type has normal column functionality except for the following exceptions:
- A column of data type LVARBINARY is not a valid column name in a CREATE INDEX statement.
- When issuing a CREATE TABLE statement, a valid data type for the column definitions is LVARBINARY. However, LVARBINARY do not allow the column constraints of PRIMARY KEY, FOREIGN KEY, UNIQUE, REFERENCES, and CHECK.
NOTE: When creating a table with a column of data type LVARBINARY, place the table in a new AREA.
- The VALUES option on the INSERT statement is not valid for the LVARBINARY data type.
- In a SELECT statement, a WHERE, GROUP BY, HAVING, or ORDER BY clause cannot use a column of data type LVARBINARY.
- There is no support for an UPDATE of a LVARBINARY column on a table which contains a column of data type LVARBINARY. Obtain the functionality of an UPDATE on an LVARBINARY column by using the DELETE and INSERT statements for the record.
Utility Support for LVARBINARY
Database utility support for tables including the LVARBINARY data type is listed below:
NOTE: SQLDUMP and SQLLOAD do not support tables with LVARBINARY column data.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |