ESQL Overview
BASIS first introduced ESQL (Exclusive SQL) tables with the release of BBj 6.0. As the name suggests, ESQL tables are only accessible via SQL statements so the traditional verbs like OPEN() and READ() do not apply to these files. Instead, developers can create these files via SQL "Create Table" statements and add or read rows by SQL "Insert" and "Select" statements. Additionally, ESQL also offers true SQL data types such as DECIMAL with a defined precision and scale, DATE, and TIMESTAMP, to name a few. ESQL tables offer a host of other features as well, such as variable length records and dynamic index creation.
The previous paragraph is an excerpt from the BASIS International Advantage article ESQL Files: Constraining Your Data to Guarantee Integrity. Read the article in its entirety for a comprehensive understanding of as ESQL.
ESQL tables now support standard SQL transactions using the standard transaction API of both JDBC and ODBC allowing third party SQL tools to use BBj databases seamlessly. The new transaction support defaults to auto-commit, so that users who do not use transactions do not have to make any changes. Third party tools will already have support for transactions built in. Developers who would like to write code using transactions should check the documentation of their development tool for how to do so. For example, the JDBC API documentation describes how to use transactions directly from Java.
Character Type Columns and Index Collation
In BBj 9.0 and higher, ESQL tables provide a powerful feature not present in the other BBj data file types - true locale-specific index collation on character column values. Traditional BBj file types store values for keys in their natural byte order which means that the letter "A" comes before "B", etc. However, note that lowercase "a" comes after upper case "Z" due its ASCII byte value. To remedy this, ESQL tables by default store key values for indexes using a locale-specific collation key. This means that the values are stored in "dictionary order" according to the specific locale. The only side effect is that this significantly increases the size of ESQL files because the collation key is much larger than a simple UTF-16 value (which ESQL uses to store key values).
If ordering is not important, but the size of the file is, the Enterprise Manager provides a setting to disable the use of collation keys when creating new ESQL tables. This setting must be set to the desired setting at the time the table is created. The image below shows the setting in the database properties in the Enterprise Manager.
Transactions and Foreign Keys
-
ESQL Foreign Keys - a simple way to codify information in a database where the values of columns "a" and "b" actually refer to the values of columns "c" and "d" in another table. The term "foreign" means that it refers to a different table and "key" indicates that the columns in the other table have defined keys in order to use them. The syntax follows standard SQL. For more information, refer to Wikipedia.
ESQL Data Types
|
Data Types |
Description |
|
bigint |
Integer data from -2^63 through 2^63-1 |
|
binary |
No maximum number of bytes |
|
bit |
Integer data with either a 1 or 0 value |
|
boolean |
Integer data with either a 1 or 0 value |
|
char |
No maximum number of characters |
|
datetime |
Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of 3.33 milliseconds |
|
decimal |
Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1 |
|
double |
Floating precision number data from -1.79E + 308 through 1.79E + 308 |
|
float |
Floating precision number data from -1.79E + 308 through 1.79E + 308 |
|
integer |
Integer data from -2^31 through 2^31 - 1 |
|
long varbinary |
Variable-length data with arbitrary length |
|
long varchar |
Variable-length data with arbitrary length |
|
numeric |
Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1 |
|
real |
Floating precision number data from -3.40E + 38 through 3.40E + 38 |
|
smallint |
Integer data from -2^15 through 2^15 - 1 |
|
timestamp |
Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of 3.33 milliseconds |
|
tinyint |
Integer data from -128 through 127 |
|
varbinary |
Variable-length binary data with a maximum length of 255 bytes |
|
varchar |
Variable-length data with a maximum of 255 characters |