InterBase SQL Server — is a databases management system originally developed by Borland Company. Now the developer of InterBase – Embarcadero Company.
InterBase database is based on versioning data storage architecture. This approach has a number of advantages over locking DBMSs:
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:
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.
Currently the newest version is InterBase 2009.
InterBase 2009 Server is available in 4 variants:
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 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 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:
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:
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.
You may use test database employee.gdb to familiarize with InterBase. It is included into installation kit and is located in the folder C:\CodeGear\InterBase\examples\database\.
A new database can be created via management console IBConsole. It is located in the folder C:\CodeGear\InterBase\bin:
Create InterBase database
The alternate way is using isql utility:
InterBase SQL Server supports several user authentication methods:
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. You can read about authentication in detail in the article:
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_PASSWith gsec utility you may create, delete, modify and view users. You may get the full list of commands by typing help.
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.
IBProvider offers several means of working with InterBase from Delphi:
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.
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.
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.
Create InterBase tables
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.
Article tags: InterBase Server, InterBase SQL language, InterBase database, InterBase 2009, InterBase 2007, InterBase vs Firebird, Firebird 2.5, InterBase tables, IBExpert, IB/FB Development Studio, Blaze Top, Database Workbench.