Release 10.1A: OpenEdge Data Management:
SQL Reference

> ## Bit string data types

> Bit string data types are used to define bit strings, which are sequences of bits having the value of either 0 or 1. This is the syntax for a bit string data type:

> Syntax
>     BIT | BINARY | VARBINARY | LVARBINARY [ ( length ) ]

> `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 types `CHAR`, `VARCHAR`,`BINARY`,`VARBINARY`,`TINYINT`,`SMALLINT`,`and INTEGER.` However, in assignments from `BINARY` and `VARBINARY`, 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.

> When inserting literals into binary data types, `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 by `b` for a bit string and `x` for a hexadecimal string. Table 48 lists the specification formats for binary values.

> Table 48: 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 type `BIT`, `CHAR`, and `VARBINARY`. 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 31,995 bytes. The default length is 1.

> Note: Due to index limitations, only the narrower `VARBINARY` 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 1,073,741,823. A `BLOB` is an object of data type `LVARBINARY`.

> ##### Maximum length for VARBINARY

> The maximum length of the `VARBINARY` data type depends on:

> - The number of columns in a table — More columns in a table further limits the length of `VARBINARY` data.

> - When a table was created — Tables created earlier can support longer `VARBINARY` data than tables created later.

> ##### 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 Progress 4GL are not visible to the 4GL.

> - `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` does 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 an `LVARBINARY` column on a table that 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

> Use `BINARY DUMP/LOAD` to dump and load data that contains the `LVARBINARY` data type.

> Note: `SQLDUMP` and `SQLLOAD` do not support tables with `LVARBINARY` column data.

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095