InterBase

From Bauman National Library
This page was last modified on 10 May 2016, at 15:16.
InterBase
fraimed
Developer(s) Embarcadero Technologies
Repository {{#property:P1324}}
Operating system Windows, OS X, Linux and Solaris[1]
Platform x32, x64
Available in English
Type RDBMS
License Proprietary
Website official website

InterBase — is a cross-platform databases management system originally developed by Borland Company, which supports the majority of known platforms: Windows, Linux, Unix, Solaris, Mac OS, etc. Now the developer of InterBase – Embarcadero Company. [2]

Overview

Versioning architecture

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.

Advantages

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.

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.

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.


InterBase Server security

InterBase SQL Server supports several user authentication methods:

  • Classic Authentication Scheme — users and passwords are common for all the bases and are stored in InterBase system database — admin.ib (isc4.gdb).
  • Embedded User Authentication — users and passwords are stored in the client’s database. Such a scheme protects databases from direct copying of client’s database or substitution of admin.ib on server.

To enable Embedded User Authentication, select WITH ADMIN OPTION when creating a database via SQL. You can simply set the appropriate parameter in database settings in IBConsole.

The following instructions allow to control Embedded User Authentication mechanism:

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

After enabling the storage of users in a database, you can manage users via SQL operators:

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.

SYSDBA User. Changing default password

SYSDBA is administrative InterBase user with exclusive rights. Default SYSDBA password is masterkey. To change password use gsec utility from InterBase:

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

With gsec utility you may create, delete, modify and view users. You may get the full list of commands by typing help.

Connection to InterBase from client’s application

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.

InterBase и VBScript, Visual Basic, VBA

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 and Delphi

IBProvider offers several means of working with InterBase from Delphi:

  • dbGo (ADO Express) components working via ADO library.
  • Direct access to COM-interfaces of ADO bypassing dbGo components.
  • Direct access to COM-interfaces of OLE DB using external VCL-component (OLE DB Direct/OLE DB Express).

InterBase Delphi Examples: InterBase Delphi.

InterBase and .Net

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.

InterBase and C++

IBProvider Professional Edition includes C++ library for working with OLE DB providers. It is the fastest means of working with OLE DB providers from Visual C++ 2005-2008 and from C++ Builder.

Examples for InterBase C++

Database editing — creation of tables, connections, and keys

You need to create tables, connections between tables, primary keys, indexes, stored procedures, generators, and other objects in an empty database. You can use InterBase inbuilt features — IBConsole or isql.exe utility — to edit databases.

Creation of InterBase tables using IBConsole

  1. In the Interactive SQL dialog, load the Tables.sql script to enter the remaining table definitions into the TUTORIAL database by choosing Query > Load Script. [3]
  2. Click on Tables in the left pane of IBConsole to check that you now have ten new tables in the TUTORIAL database.

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

The List of InterBase Administration Utilities

IBExpert

IBExpert — Supports Firebird, InterBase, Yaffil. DDL and DML editors, visual query builder, code auto completion, Metadata Extractor, and many other capabilities.

IB/FB Development Studio

IB/FB Development Studio — Supports Firebird, InterBase. Visual database designer, embedded MERGE, scheduler, code auto completion, query analyzer, performance monitor.

Blaze Top

Blaze Top — Supports Firebird, InterBase. Developer and database administrator tool.

Database Workbench

Database Workbench — Supports several database servers including Firebird and InterBase. Stored procedures debugging, analysis of plans, embedded data mining and metadata transfer.

References

  1. https://www.embarcadero.com/products/interbase/faq
  2. http://www.ibprovider.com/eng/documentation/interbase.html
  3. http://docwiki.embarcadero.com/InterBase/XE7/en/InterBase_Quick_Start:_Part_II_-_Creating_Tables_with_a_Script