Getting Started With Interbase
Installation

I've successfully installed Interbase 6.0.1 on Linux. I've never tried to install it on any other platform or any other version on Linux.

You can download binary distributions of Interbase 6.0.1 in tar.gz or RPM formats from the Borland web site for Linux and Solaris. The tar.gz distribution comes with an install script. The RPM distribution can be installed using rpm -i. Source code distributions are also available.

There are 2 versions of Interbase: Classic and Super Server. Classic only allows a single connection while Super Server allows multiple connections. I recommend using Super Server and this document describes it's installation.

Starting the Database at Boot Time

You can use the following script to start/stop the database at boot/shutdown time.

#!/bin/sh

case "$1" in
  start)
        /opt/interbase/bin/ibserver &
        ;;
  stop)
        kill `ps -efa | grep ibserver | grep -v grep | awk '{print $2}'`
        ;;
  *)
        echo $"Usage: $0 {start|stop}"
        exit 1
esac

exit 0

Install this script and run it with the "start" option to start up the database. Running it with the "stop" option shuts the database down. To access a database, it must be running.

Creating a Database

After installation, Interbase is ready to use but to do any useful work, you'll have to create a database.

The installation process creates a security database named isc4.gdb containing host and user privileges and an administrative user named SYSDBA who's password is changeme.

Interbase user names are case insensitive and can be up to 31 characters long. Interbase passwords are case sensitive and can be up to 32 characters long, but only the first 8 characters are actually used.

Use the gsec utility to change the SYSDBA password to newpass and create a new user named testuser with password testpass. gsec must be run as root, from the directory containing isc4.gdb. A gsec session follows.

[root@localhost root]# cd /opt/interbase
[root@localhost interbase]# ls -l isc4.gdb
-rw-rw-rw-    1 root     root       618496 Feb 18 14:29 isc4.gdb
[root@localhost interbase]# /opt/interbase/bin/gsec
GSEC> modify sysdba -pw newpass
GSEC> add testuser
GSEC> modify testuser -pw testpass
GSEC> quit 

Though you can create tables in the isc4.gdb database using the SYSDBA user, it's not a good idea. You should create a new database using a regular user. The following sequence of commands creates a database called /home/user/testdb.gdb which is owned by testuser.

[user@localhost user]$ /opt/interbase/bin/isql -u testuser -p testpass
Use CONNECT or CREATE DATABASE to specify a database
SQL> create database '/home/user/testdb.gdb';
SQL> commit;
SQL> quit;

Interbase databases are referenced by the pathname of the database file. If you try to create a database using a file name that already exists or if you forget to enclose the file name in single quotes, you'll get a cryptic error indicating that the 'database' token is unknown. So, if you get a message like that, make sure that you enclosed the file name in single quotes and make sure that the database file that you're trying to create doesn't already exist.

Now you can log into the /home/user/testdb.gdb database as testuser with the following command.

/opt/interbase/bin/isql -u testuser -p testpass /home/user/test.gdb

The only users that can drop a database are the user that owns the database and the SYSDBA user. If you want to drop the database, you can do so using isql as follows.

[user@localhost user]$ /opt/interbase/bin/isql -u testuser -p testpass /home/user/testdb.gdb 
Database:  /home/user/testdb.gdb, User: testuser
SQL> drop database;
SQL> commit;
SQL> quit;

To delete a user, use the gsec utility as the root user and delete the appropriate user as follows.

[root@localhost root]# cd /opt/interbase
[root@localhost interbase]# ls -l isc4.gdb
-rw-rw-rw-    1 root     root       626688 Feb 18 20:43 isc4.gdb
[root@localhost interbase]# /opt/interbase/bin/gsec
GSEC> delete testuser
GSEC> quit

This should be enough to get you started. To set up more complex configurations, consult the Interbase documentation.

Accessing a Database

The first step in accessing an Interbase database is setting up your environment. The Interbase client is called isql. Note that unixODBC and Sybase have a client called isql as well which could be installed in /usr/bin, /usr/local/bin or /opt/sybase*/bin. If you have unixODBC or Sybase installed, you'll have to decide which client you want the isql command to run. If you want it to run the Interbase client, follow the instructions below. Otherwise, you'll have to run the Interbase isql client by it's full pathname.

In the configuration script for your shell, add /opt/interbase/bin to your PATH environment variable as follows.

For Bourne shells:

PATH=$PATH:/opt/interbase/bin
export PATH

For C-shells:

setenv PATH ${PATH}:/opt/interbase/bin

Now log out and log back in and you can access the database using the isql client. For example, to access a database called /home/user/testdb.gdb on the local machine as the testuser user with password testpassword, use the following command.

isql -u testuser -p testpassword /home/user/testdb.gdb

To connect to an interbase database on a remote machine, prepend the database filename with the remote hostname, followed by a colon. For example, to access a database called /home/user/testdb.gdb on the remote machine testhost as the testuser user with password testpassword, use the following command.

isql -u testuser -p testpassword testhost:/home/user/testdb.gdb

Once you're connected to the database, the isql client prompts you to enter a query. Queries may be split across multiple lines. To run a query, end it with a semicolon. To exit, type quit;.

A sample isql session follows.

[user@localhost user]$ /opt/interbase/bin/isql -u testuser -p testpass /home/user/testdb.gdb
Database:  /home/user/testdb.gdb, User: testuser
SQL> create table testtable (
CON> col1 char(40),
CON> col2 integer
CON> );
SQL> show table testtable;
COL1                            CHAR(40) Nullable 
COL2                            INTEGER Nullable 
SQL> insert into testtable values ('hello',50);
SQL> insert into testtable values ('hi',60);
SQL> insert into testtable values ('bye',70);
SQL> select * from testtable;

COL1                                             COL2 
======================================== ============ 

hello                                              50 
hi                                                 60 
bye                                                70 

SQL> update testtable set col2=0 where col1='hi';
SQL> select * from testtable;

COL1                                             COL2 
======================================== ============ 

hello                                              50 
hi                                                  0 
bye                                                70 

SQL> delete from testtable where col2=50;
SQL> select * from testtable;

COL1                                             COL2 
======================================== ============ 

hi                                                  0 
bye                                                70 

SQL> commit;
SQL> drop table testtable;
SQL> quit;
Accessing a Database With SQL Relay

Accessing Interbase from SQL Relay requires an instance entry in your sqlrelay.conf file for the database that you want to access. Here is an example sqlrelay.conf which defines an SQL Relay instance called interbasetest. This instance connects to the /home/user/testdb database as the user testuser with password testpassword.

<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>

        <instance id="interbasetest" port="9000" socket="/tmp/interbasetest.socket" dbase="interbase" connections="3" maxconnections="5" maxqueuelength="0" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass">
                <users>
                        <user user="interbasetest" password="interbasetest"/>
                </users>
                <connections>
                        <connection connectionid="interbasetest" string="user=testuser;password=testpass;database=/home/user/testdb.gdb;dialect=3" metric="1"/>
                </connections>
        </instance>

</instances>

Note that in this example, the database parameter of the string attribute in the connection tag references the local database /home/user/testdb.gdb. If you want to access a remote database, prepend the database filename with the remote hostname followed by a colon. For example: testhost:/home/user/testdb.gdb.

Now you can start up this instance with the following command.

sqlr-start -id interbasetest

To connect to the instance and run queries, use the following command.

sqlrsh -id interbasetest

The following command shuts down the SQL Relay instance.

sqlr-stop interbasetest