Firebird Novice's Guide

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

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 Identifiers

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 & C++Builder

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 available 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.

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 are 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.

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.

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.

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.

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.

What is Firebird RDBMS ?