Start working with Firebird Sql Server using IBProvider database client

---

What is Firebird SQL Server?

Firebird SQL Server is a database server based on InterBase 6.0. open source code. There exist Firebird distribution packages for Windows, Linux, Unix, Solaris, MacOS, 32- and 64-bit architecture. Firebird SQL Server is distributed free of charge and has no license restrictions.

What Firebird version to choose?

The newest version for today is Firebird 3.0. The stable version — Firebird 2.5.

• Download Firebird 2.5
• Download Firebird 3.0

Firebird installation: choosing server type

Firebird SQL server is available in 3 variants:

• Firebird Super Server
• Firebird Classic Server
• Firebird Embedded

Firebird Super Server

Firebird Super Server — all client’s connections are served by the single server process, there is common client’s cache. This allows spending fewer resources for clients serving. Firebird Super Server disadvantage is absence of the ability to involve several processors for Firebird server work.

Firebird Classic Server

Firebird Classic Server creates separate server processes for each client’s connection. Firebird Classic Server architecture is more reliable, as failure of one server process does not cause rejection to serve all the clients. In addition Firebird Classic allows involvement of multiprocessor architecture.

While installing Firebird you may choose between Firebird Super Server and Firebird Classic.

Firebird Embedded

Firebird Embedded Server is designated for embedded databases. It consists of one dll — fbembed.dll that includes a client and a server of Firebird Super Server. Firebird Embedded does not need to be installed on the client’s work station. It is sufficient just to copy fbembed.dll and some more files to the client’s computer.

Firebird Embedded disadvantage is impossibility of simultaneous connection of several clients to the single database. After successful connection Firebird Embedded blocks database file for the exclusive access.

Firebird Embedded Server has separate distribution package.

• Download the archive with Firebird Embedded

If it is difficult to choose

If you are hesitating what Firebird installation type to choose use Firebird Super Server. In future you will change the server architecture easily.

Start working with Firebird

You may use test database employee.fdb to familiarize with Firebird. It is included into installation kit and is located in the folder Program Files\Firebird\Firebird\examples\empbuild\.

New Firebird database creation

To create empty Firebird database:

• start the utility: C:\Program Files\Firebird\Firebird\bin\isql.exe
• perform database creation instruction:

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

In ISQL each expression ends with semicolon.

Connection can be tested by running the query:

SQL> select MON$DATABASE_NAME from MON$DATABASE;

If everything has been done in the right way it will return the path to the connected database:

========================================================================== D:\TEMP\TEST.FDB

Firebird security

• Firebird keeps all users in a single auxiliary database security.fdb.
• Starting from Firebird 2.1 SSPI authentication with Windows tools is supported. Domain administrators of database level are identified as SYSDBA superusers.
• It is planned to implement the ability of storing users in the client’s database in Firebird 3.0.

SYSDBA User. Changing default password

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

c:\Program Files\Firebird\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 Firebird 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 Firebird. 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.fdb; " & _
	    "User Id=SYSDBA;" & _
	    "password=masterkey;" & _
	    "ctype=win1251;" & _
	    "auto_commit=true"

set rs = cn.execute("select * from MON$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.

Firebird and VBScript, Visual Basic, VBA

For the access to Firebird and 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, Firebird VBScript, VBA, Visual Basic.

Firebird and Delphi

IBProvider offers several means of working with InterBase and Firebird 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).

Firebird Delphi, InterBase Delphi examples of work.

Firebird and .Net

To access Firebird 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
• Firebird 2.0. Examples for ADO .Net (c#)
• Firebird 2.1. Examples for ADO .Net (c#)

Firebird 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 Firebird C++ and InterBase C++

Firebird database editing

You need to create tables, links between tables, primary keys, indexes, stored procedures, generators and other objects. To edit Firebird databases you may use isql.exe utility:

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 Firebird administration other than isql.

The List of Firebird Administration Utilities

FlameRobin

FlameRobin — Supports Firebird. Cross-platform editor of SQL, DDL, management of users.
License: open source, distributed free of charge.
www: FlameRobin

IBExpert

IBExpert — Supports Firebird, InterBase, Yaffil. DDL and DML editors, visual query builder, code auto completion, Metadata Extractor, and many other capabilities.
License: from 179 euro.
www: IBExpert

IB/FB Development Studio

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

Blaze Top

Blaze Top — Supports Firebird, InterBase. Developer and database administrator tool.
License: from 129 euro.
www: Blaze Top

Database Workbench

Database Workbench — Supports several database servers including Firebird and InterBase. Stored procedures debugging, analysis of plans, embedded data mining and metadata transfer.
Licensed separately for InterBase and Firebird. 171$ for each engine (InterBase or Firebird).
www: Database Workbench

If your utility is absent in the above list, please write us at ibprovider.com and we will add its description.

Useful links

• Download Firebird
• Download IBProvider Professional Edition.
• Access to Firebird and InterBase from Delphi
• Firebird ODBC, InterBase ODBC
• Firebird and InterBase documentation

Tags: Firebird, Firebird C++, Firebird Delphi, Firebird .Net, Firebird VBScript, Firebird VBA, Firebird Visual Basic

---