Borland Interbase database for beginners

Versioning architecture of Interbase SQL Server

Borland Interbase database is based on versioning data storage architecture. This approach has a number of advantages over locking DBMSs:

• There is no need to support transaction log after a system failure to restore Interbase databases.
• Clients who read data never block clients who record data.

Interbase server advantages

Interbase Server - is a cross-platform DBMS, which supports the majority of known platforms: Windows, Linux, Unix, Solaris, Mac OS, etc.

Interbase has a number of advantages over other DBMSs:

• Updatable Views;
• Two-phase commit;
• Efficient trigger mechanism;
• Server processing of BLOB fields (BLOB filters);
• Events (notifications);
• Encryption of network traffic, databases, backup files, and individual database columns.

Interbase SQL language

Interbase SQL language is compatible with SQL-92 standard. Moreover, Interbase server supports extensions of SQL language standard subset via user defined functions (UDF). Interbase SQL provides advanced SQL capabilities for stored procedures and triggers - PSQL.

Interbase server current version

Currently the newest version is Interbase 2009.

Interbase 2009 Server is available in 4 variants:

• InterBase SMP 2009 Developer;
• InterBase SMP 2009 Server;
• InterBase SMP 2009 Desktop;
• Interbase SMP 2009 ToGo.

Interbase SMP 2009 Developer Edition

InterBase SMP Developer Edition is delivered free as a part of CodeGear integrated development environment; we recommend to use it for the first acquaintance.

Interbase SMP 2009 ToGo

Interbase SMP 2009 ToGo single-user version is embedded into application as a dll; it is aimed at development of portable applications with database size less than 3 megabytes. Meanwhile, it has all the necessary functions of a full-fledged database including transactions and SQL support.

You can download various Interbase versions here: Interbase download.

Interbase productivity

Interbase Server was developed taking into account modern requirements to DBMS productivity. A number of technologies have been applied in the latest versions of Interbase SQL Server to increase substantially DBMS operation speed:

• Support of Interbase SMP (symmetric multiprocessing) allows to use the capabilities of multiprocessor architecture with single server process of Interbase SuperServer.
• Hyperthreading technology support.
• Batch processing of SQL queries allows to reduce network traffic and to raise productivity.

Interbase versus Firebird

Though Interbase 2009 and Firebird 2.5 are based on the common source code Interbase 6.0, these DBMSs are very different from each other on the modern development stage.

Interbase 2009 has implemented the following options, which are not supported in the latest version Firebird 2.5:

• Database and individual columns encryption;
• Network traffic encryption (Over-the-Wire (OTW) Encryption);
• Journaling of operations;
• Possibility of authentication on the database level;
• Dump export in on-line mode;
• Point-in-Time Recovery;
• Boolean logical data type.

Client-server version of Firebird SQL Server exists in two variants: Firebird Super Server and Firebird Classic. Interbase has implemented the architecture that unites the advantages of Classic and SuperServer - Interbase SuperServer + SMP.

Super Server architecture - all client's connections are served by the single server process, there exists common client's cache. This allows lower consumption of resources for clients serving. Super Server disadvantage is inability to involve several processors for server operation.

Classic Server Architecture creates individual server process for each client’s connection. Classic Server architecture is more reliable, as failure in one server process doesn’t cause rejection to serve all the clients. Moreover, Classic Server allows distribution of client’s connections between several processors. The architecture disadvantage is additional overheads for serving and synchronization of processes and the absence of common client’s cache.

Interbase Super Server + SMP (symmetric multiprocessing) unites the advantages of two architectures - Classic è Superserver. Single server process of Interbase Super Server allows to reduce overheads and to use common client’s cache; SMP technology involves the potential of multiprocessor architecture, which was earlier unavailable for Classic Server.

Use CONNECT or CREATE DATABASE to specify a database
SQL> CREATE DATABASE 'localhost:d:\temp\test.gdb' user 'SYSDBA' password 'masterkey' DEFAULT CHARACTER SET WIN1251;

SQL> select TMP$DATABASE_PATH from TMP$DATABASE;

==========================================================================
D:\TEMP\TEST.GDB

ALTER DATABASE ADD ADMIN OPTION
ALTER DATABASE SET ADMIN OPTION INACTIVE
ALTER DATABASE SET ADMIN OPTION ACTIVE

CREATE USER EMPLOYER SET PASSWORD 'PASSWORD';
ALTER USER EMPLOYER SET NO LAST NAME, DEFAULT ROLE ABC;

Both authentication schemes, Classic and EUA, can be used on Interbase server simultaneously. You can read about authentication in detail in the article: Embedded User Authentication in InterBase 7.5

C:\CodeGear\Interbase\bin>gsec
GSEC> modify SYSDBA -pw NEW_PASS

First download and install the package of IBProvider Professional Edition .

IBProvider Professional Edition is the set of COM-components that allows working with any version of Firebird and Interbase. The components are supported by most development tools: Delphi, C++ Builder, Visual C++, .Net Framework, C#, Visual Basic, VBScript, VBA and others.

Let’s write simple VBScript to check connection to Interbase. Create empty vbs file and paste the following code into it stating the right path to the database:

Dim cn, cmd, rs, i
Set cn = CreateObject ("ADODB.Connection")

cn.Open "Provider=LCPI.IBProvider.3;" & _
	    "Data Source=localhost:d:\temp\test.gdb; " & _
	    "User Id=SYSDBA;" & _ 
	    "password=masterkey;" & _ 
	    "ctype=win1251;" & _
	    "auto_commit=true"

set rs = cn.execute("select * from TMP$ATTACHMENTS")

do while not rs.EOF

   for i=0 to rs.Fields.Count - 1
       wscript.echo rs(i).Name & "=" & rs(i).Value       
   next   rs.MoveNext
loop

rs.close
cn.close

Run the script in the command line to see the list of active connections to the database.

For the access to Interbase from VBScript, VBA, Visual Basic, ADO library is used (ActiveX Data Objects). You will find a lot of examples of working with the library in the documentation: examples of Interbase VBScript, VBA, Visual Basic.

Interbase Delphi Examples: Interbase Delphi.

To access Interbase from .Net ADO .Net library is used. IBProvider site contains large step-by-step manual dedicated to working with Firebird in Visual Studio .Net (ADO .Net).

Additional materials to the topic: Examples of working with ADO .Net for beginners.

Examples for Interbase C++

Creation of Interbase tables using IBConsole

Creation of Interbase tables using isql.exe

Create the table:

SQL> CREATE TABLE cross_rate
CON> (
CON>     from_currency   VARCHAR(10) NOT NULL,
CON>     to_currency     VARCHAR(10) NOT NULL,
CON>     conv_rate       FLOAT NOT NULL,
CON>     update_date     DATE,
CON>
CON>     PRIMARY KEY (from_currency, to_currency)
CON> );

Than paste one entry and select from the table:

SQL> INSERT INTO cross_rate VALUES ('Dollar', 'CdnDlr',  1.3273,  '11/22/93');
SQL> SELECT * from cross_rate;

FROM_CURRENCY TO_CURRENCY      CONV_RATE UPDATE_DATE
============= =========== ============== ===========
Dollar        CdnDlr           1.3273000 1993-11-22

There are many graphic utilities of Interbase administration other than isql.

IB/FB Development Studio

Blaze Top

Database Workbench