This page lists features introduced in InterBase 6.
IBConsole
InterBase provides an integrated graphical user interface called IBConsole. Using
IBConsole, you can configure and maintain an InterBase server, create and administer
databases on the server, and execute interactive SQL, manage users, and administer
security.
IBConsole is a GUI front end for interBase's command-line tools. It replaces the Server
Manager and InterBase Windows ISQL interfaces found in earlier versions of InterBase.
IBConsole runs on Windows, but can manage databases on any InterBase server on the
local network, and on UNIX, Linux, and NetWare.
You can use IBConsole to:
- Perform data entry and manipulation
- Configure and maintain a server
- Enter and execute interactive SQL
- Manage server security
- Backup and restore a database
- View database and server statistics
- Validate the integrity of a database
- Sweep a database
- Recover "in limbo" transactions
For details on using IBConsole, see the Operations Guide.
Dialects
InterBase 6 introduces the concept of dialects to allow users to move ahead with new
features that are incompatible with older versions of InterBase: delimited identifiers, large
exact numerics, and the SQL DATE, TIME, and TIMESTAMP datatypes.
As InterBase moves forward in complying with SQL standards, some new features and
usages are incompatible with older usage. Dialects assist in this transition. Dialect 1
guarantees compatibility with older databases and clients. Dialect 3 allows full access to
new features. There is also a dialect 2 available for clients. It is a diagnostic mode only.
Transition features:
Features that behave differently in dialect 1 and dialect 3 are called
transition features. The transition features are:
- Anything delimited by double quotes
- Date/time datatypes
- Large exact numerics (DECIMAL and NUMERIC datatypes with precision greater than 9)
Both clients and databases must be assigned a dialect. Databases are created in dialect 3
by default. The isql client takes on the dialect of the database to which it is attached unless
you specify a different dialect.
The following section describes the differences between dialects.
Features that are the same in all dialects
The following InterBase 6 features are available in both dialect 1 and dialect 3:
- IBConsole
- The ALTER COLUMN clause of the ALTER TABLE statement
- The TIMESTAMP datatype, which is the equivalent of the
DATE datatype in earlier versions of InterBase
- The EXTRACT() function and CURRENT_TIMESTAMP
- Read-only databases
- SQL warnings
- The Services API, Install API, and Licensing API
- InterBase Express (IBX) components in Delphi 5
DIALECT 1
In version 6 dialect 1, the transition features behave in the InterBase 5 manner:
- String constants can be delimited by either single or doubles
quotes. Dialect 1 does not recognize delimited identifiers.
- The DATE datatype is not available, but is replaced by the
TIMESTAMP datatype, which contains both date and time information.
When an InterBase version 5 database is restored as version 6,
datatypes that were formerly DATE are restored as TIMESTAMP.
- DECIMAL and NUMERIC datatypes with precision larger than 9 are
stored as floating point numbers.
DIALECT 2
Clients can be assigned dialect 2. In this mode, they issue errors whenever they encounter
double quotes, DATE datatypes, or NUMERIC/DECIMAL datatypes with precision greater
than 9. This behavior is intended to alert the developer to potential problem areas during
migration and is not useful for production purposes. To detect problem areas in the
metadata of a database that you are migrating, extract the metadata and run it through a
version 2 client, which will report all instances of transition features. For example:
isql -i v5metadata.sql
Do not assign dialect 2 to databases.
DIALECT 3
The following features are unique to dialect 3 and are incompatible with dialect 1 and all
older InterBase databases and clients:
- String constants must be delimited by single quotes. Double
quotes are used only for delimited identifiers.
- The DATE datatype holds only date information. Two new datatypes
are available: a TIME datatype that holds only time information,
and a TIMESTAMP datatype that holds the whole timestamp. TIMESTAMP
replaces the functionality of the DATE datatype in earlier versions
of InterBase. In addition, dialect 3 provides the CURRENT_DATE,
CURRENT_TIME, and CURRENT_TIMESTAMP functional operators.
- DECIMAL and NUMERIC datatypes with precision larger than 9 are
stored as 64-bit exact numerics if they are created in dialect 3.
Note that columns that have such datatypes are still stored as
floating point if they are migrated from an earlier version. See
the migration chapter in Getting Started for more information on
migrating columns to INT64 exact numerics.
SQL delimited indentifiers
InterBase now supports SQL delimited identifiers. Delimited identifiers are database
object names that are delimited by double quotes. SQL delimited identifiers are permitted
only in InterBase 6 clients and databases using dialect 3.
In InterBase 6 clients and databases using dialect 3, a string constant is delimited by
single quotes, and an SQL delimited identifier is delimited by double quotes. Because the
quotes delimit the boundaries of the name, the possibilities for object names are greatly
expanded from previous versions of InterBase.
InterBase object names can now:
- Be a keyword
- Use spaces, except for trailing spaces
- Use non-ASCII characters
- Be case sensitive
InterBase Express (IBX) for Delphi 5
Borland Delphi 5 users can now use the InterBase Express (IBX) components to build
InterBase database applications without the overhead of using the Borland Database
Engine (BDE). IBX accesses the InterBase API directly, allowing increased speed and
control within InterBase applications.
The version of IBX that comes with Delphi 5 addresses only InterBase 5 features. The IBX
version that is included with InterBase 6 addresses all InterBase 6 features, using calls to
the new Service API, Install API, and Licensing API, as well as the newest InterBase API.
The InterBase 5 version of IBX provides one additional tab in Delphi, labelled InterBase,
that contains the IBX components for InterBase 5. The InterBase 6 version of IBX
provides two tabs in Delphi: the InterBase tab is the same as in version 5 IBX; in addition
there is an InterBase Admin tab. The InterBase Admin tab contains components that
address the Services API, Install API, and Licensing API. It contains configuration,
backup, restore, licensing, statistics, logging, and install, and uninstall components. The
InterBase Admin tab is found at the extreme right of the tabs in Delphi 5. You will have
to scroll to find it.
To install the InterBase 6 version of IBX, ensure that Delphi 5 is installed, Then display
the InterBase Launcher and choose InterBase Express.
For more information on using IBX, refer to the InterBase 6 Developers Guide and the
Delphi online help.
Large Exact Numerics
In dialect 3, InterBase 6 conforms with the SQL92 standard by storing NUMERIC and
DECIMAL datatypes with 10 to 18 digits of precision as 64-bit integers (INT64 datatype).
InterBase has always implemented NUMERIC and DECIMAL datatypes with precision less
than 10 as exact numerics, but those with precision of 10 thorugh 15 were implemented
as DOUBLE PRECISION. Now, NUMERIC and DECIMAL datatypes are all stored as exact
numerics. They are 16, 32, or 64 bit, depending on the precision. NUMERIC and DECIMAL
datatypes with precision greater than 9 are referred to as "large exact numerics" in this
discussion.
- These new 64-bit integer types ar available in all contexts where
datatypes are defined or used.
- NUMERIC and DECIMAL datatypes with a precision of 9 and scale S
that caused arithmetic error messages in InterBase 5 return correct
64-bit results in InterBase 6.
- When an arithmetic operation on exact numeric types overflows,
InterBase 6 reports an overflow error, rather than returning an
incorrect value.
- If one operand is an approximate numeric, then the result of any
dyadic operation (addition, subtraction, multiplication, division)
is DOUBLE PRECISION.
- Any value that can be stored in a DECIMAL(18,S) can also be specified
as the default value for a column or a domain.
SQL DATE, TIME, and TIMESTAMP datatypes
The old InterBase DATE datatype, which contains both date and time information, is being
replaced with the SQL92 standard TIMESTAMP, DATE, and TIME datatypes in dialect 3.
- In dialect 1, only TIMESTAMP is available. TIMESTAMP is
the equivalent of the DATE datatype in previous versions.
When you back up an older database and restore it in version
6, all the DATE columns and domains are automatically restored
as TIMESTAMP.
- In dialect 3, TIMESTAMP functions as in dialect 1, but two
additional datatypes are available: DATE and TIME. These
datatypes function as their names suggest: DATE holds only
date information and TIME holds only time.
TIMESTAMP is a 64-bit datatype and DATE and TIME are 32-bit datatypes.
New operators for retrieving current time, date, and timestamp
The CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP functional operators return
the date and time values based on the moment of execution of an SQL statement using
the server's clock and time zone. It is no longer necessary to cast TODAY or NOW as DATE
to obtain the current date, time, or timestamp.
For a single SQL statement, the same value is used for each evaluation of CURRENT_DATE,
CURRENT_TIME, and CURRENT_TIMESTAMP within that statement. This means that if
multiple rows are updated, as in the following statement, each data row will have the
same value in the aTime column.
UPDATE aTable SET aTime = CURRENT_TIME;
Similarly, if row buffering occurs in a fetch via the remote protocol, then the
CURRENT_TIME is based on the time of the OPEN of the cursor from the database engine,
and not the time of delivery to the client.
New operator to extract information from datetime datatypes
The EXTRACT() function extracts date and time information from databases.
EXTRACT() has the following syntax:
EXTRACT ( part FROM value)
The value passed to the EXTRACT() expression must be DATE, TIME, or TIMESTAMP.
Extracting a part that doesn't exist in a datatype results in an error. For example:
EXTRACT (TIME FROM aTime)
A statement such as EXTRACT (YEAR from aTime) would fail.
ALTER COLUMN and ALTER DOMAIN statements
You can now alter the name, datatype, and position of a column using the ALTER COLUMN
clause of the ALTER TABLE statement. Extensions to the ALTER DOMAIN statement allow you
to alter the name and datatype of a domain. This functionality is available in InterBase 6
dialects 1 and 3.
The new ALTER COLUMN clause of the ALTER TABLE statement allows you to change:
- The datatype of a field
- The name of a field
- The position of a field with respect to the other fields
The ALTER DOMAIN statement has new options that allow you to change the name and
datatype of a domain.
The ALTER COLUMN clause of ALTER TABLE and the TYPE clause of ALTER DOMAIN do not
allow you to make datatype conversions that could lead to data loss. For example, they
do not allow you to change the number of characters in a column to be less than the
largest value in the column.
Read-only Databases
You can now change InterBase databases to read-only mode. This provides enhanced
security for databases by protecting them from accidental or malicious updates.
Databases are always in read-write mode at creation time. You can change any
InterBase 6 database, regardless of dialect, to read-only mode using gbak or gfix.
An InterBase 5 or older client can select from a read-only database. However, these older
clients cannot distinguish between a read-only and read-write database. If an older client
attempts to do anything other than select from an read-only database, the attempt fails
with an error.
For information on creating a read-only database, see "Read-write and read-only
databases" in the Operations Guide.
New SQL Keywords
InterBase 6 introduces the following new keywords:
| COLUMN |
SECOND |
| CURRENT_DATE |
SQL |
| CURRENT_TIME |
TIME |
| CURRENT_TIMESTAMP |
TIMESTAMP |
| DAY |
TYPE |
| EXTRACT |
WEEKDAY |
| HOUR |
YEAR |
| MINUTE |
YEARDAY |
| MONTH |
If you want to use these keywords as object names in databases using dialect 3, then you
must delimit them with double quotes.
Updated status vector and SQL Warnings
The InterBase status vector is a mechanism that holds information about the current
operation, where it is accessed via API calls. In previous versions of InterBase, the status
vector contained only the error code, but in InterBase 6 it also contains the information
about the source of the error message, and the error message type, one of error, warning,
or informational. Warning and informational messages do not impede normal
client/server operations, but may advise the client of a problem that needs investigation.
Warnings can be issued for the following conditions:
- SQL statements with no effect
- SQL expressions that produce different results in InterBase 5 versus InterBase 6
- API calls which will be replaced in future versions of the product
- Pending database shutdown
New gbak functionality
The InterBase 6 gbak command incorporates the functionality of the version 5 gsplit utility,
allowing the database owner or SYSDBA to back up to and restore from multiple files.
gbak now allows you to set databases to read-only with the -mode read_only switch. Refer
to "Read-only databases" on page 30 for more information.
You can use gbak's new -service switch to perform server-side backups. In this mode, gbak
uses the new Services API and performs the backups on the server, incurring significantly
less network traffic. Previously, backups were all performed on the client.
When using the -service switch, make sure that all path names to databases and backup
files must be given relative to the server.
When backing up without using the Services API, backups are performed on the client
platform where gbak is running. All pathnames to databases and backup files must be
given relative to the client.
For more information on the gbak command, see the Operations Guide
Services API
The InterBase 6 Services API allows you to write applications that monitor and control
InterBase servers and databases. Tasks that you can perform with this API include:
- Performing database maintenance tasks such as database backup
and restore, shutdown and restart, garbage collection, and
scanning for invalid data structures
- Creating, modifying, and removing user entries in the security database
- Administering software activation certificates
- Requesting information about the configuration of databases and the server
The features that you can exercise with the Services API include those of the
command-line tools gbak, gfix, gsec, gstat, and iblicense. The Services API can also perform
other functions that are not provided by these tools.
For more information about the Services API, refer to "Working with Services" in the API
Guide.
Install API
InterBase provides developers with a new group of functions that facilitate the process of
silently installing InterBase as part of an application install on the Win32 platform. In
addition, it allows you to interact with users if desired, to gather information from them
and to report progress and messages back to them.
For more information refer to "Using the Install and Licensing APIs" in the "Developer's
Guide."
InterBase Client for Unix
InterBase 6 comes with a UNIX-based client package called "InterBase Client Only" (not
to be confused with the InterBase 5.x driver called InterClient). The package contains a
version for Solaris, which can be installed from the CD-ROM using the setup.ksh script. The
setup.ksh script can also be used to install the InterBase documentation and the Adobe
Acrobat Reader from the CD-ROM For more information on installing and setting up the
InterBase Client for UNIX, refer to the Installation chapter in Getting Started.
Replication
IBReplicator is a component of the InterBase 6 product that facilitates replication and
synchronization between multiple InterBase databases that have similar structure.
IBReplicator synchronizes databases by copying changes of the entire database or a
subset of the database, as you specify. Replicant databases can reside on different servers
or on the same server. This product component includes a replication server executable
for each respective InterBase server platform, and also a Windows graphical tool for
configuring and invoking data replication. You can find documentation for configuring
and using IBReplicator in the "Data Replication" chapter of the InterBase 6 Operations
Guide.
