Getting Started with PHP and InterBase

Introduction

In this article, I will talk a little about installing and using InterBase with php4. It includes:

• Some words about InterBase
• Getting and installing InterBase
• Compiling/configuring php with InterBase support
• Creating an example database
• Some code examples

Some words about InterBase

InterBase has been around since the mid 1980's. The database has come a long way, and was eventually opensourced by by Borland/Inprise in July 2000. The version now available, and described here is 6.01 (=6.0 + security patch). It runs on Linux, a variety of Unix platdoms and on Windows.

It doesn't require many resources. The required disk space is around 10MB (without your Database). We use it at work with apache/php on a Pentium75 with 80MB and it runs ok!

From the website:

InterBase® is an open source relational database that runs on Linux, Windows, and a variety of Unix platforms. It is the same commercial database that Motorola, Nokia, Boeing, and the Boston Stock Exchange have used for many years, now available without license fees. InterBase® offers excellent concurrency, high performance, and a powerful language for stored procedures and triggers. Since 1985 InterBase® has provided the strength of a powerful, high performance, proven architecture with the sophisticated technology applications need to be successful.

Currently, development of the source code is made in two separate parts:

• InterBase
• Firebird

Firebird is kind of a fork of the released code from Borland. The people behind it are IBPhoenix. This code seems to develop quicker than the original InterBase branch and will be released as V1.0 (currently RC2). The sourcecode is at http://firebird.sourceforge.net.

As for now, InterBase and Firebird seem to be more or less compatible.

There are a few sites on the net with good information:

Firebird official: http://www.ibphoenix.com/ Borland official: http://www.borland.com/interbase/

Getting and installing InterBase

InterBase 6.01 can be downloaded from Borland here: http://www.borland.com/devsupport/interbase/opensource/ Or you can download the Firebird version from http://firebird.sourceforge.net/index.php?op=files

For Linux, two versions exist:

• The classic server, and
• The super server.

Compiling php with InterBase support

Since many distributions do not compile InterBase support into php, it's a good idea to check out how to achieve this. If you do not have the php sources, get them here. Untar them in your prefered sources location and configure them like this:

./configure --with-interbase=/opt/interbase --with-apxs

Make sure you give the right InterBase root directory as option. (/opt/interbase here) Then issue:

make
make install

When installing is finished, open the php.ini configuration file (located in /usr/local/lib/), and check if these lines exist (uncomment/modify them if necessary):

magic_quotes_sybase = On     ; Use Sybase-style magic quotes (escape ' with '' instead of ')
extension=php_interbase.so

Configuring Apache

As for apache, it has only to be configured to work with the php module. InterBase itself is handled by php. If you used php before, you should not need to touch it. If you didn't, open your apache configuration file, (httpd.conf, often located in /etc or /etc/httpd), look for these lines and uncomment/add them.

LoadModule php4_module        /usr/lib/apache/libphp4.so
AddModule mod_php4.c

<IfModule mod_php4.c>
AddType application/x-httpd-php .php
AddType application/x-httpd-php .php4
AddType application/x-httpd-php-source .phps
<IfModule>

Eventually, for your pages to be parsed by the php processor, they need the extension .php. Also, do not forget to add index.php (or whatever.php) as DirectoryIndex in your apache config.

Creating a new database user

Before creating a new db, we create a user, which we will use for these examples (username 'phptest' and password 'phptest'). This can be done using the 'gsec' tool that comes with InterBase. Assuming your 'sysdba' password is 'masterkey', use it like this:

/opt/interbase/bin/gsec -user sysdba -password masterkey -add 'phptest' -pw 'phptest'

Creating a test database

SET SQL DIALECT 3;

CREATE DATABASE 'phptest.gdb'
PAGE_SIZE=8192
DEFAULT CHARACTER SET ISO8859_1;

CREATE TABLE ADDRESS
(
CATEGORY INTEGER NOT NULL,
NAME VARCHAR(100) NOT NULL,
KEYINDEX INTEGER NOT NULL,
ADDRESS BLOB SUB_TYPE TEXT SEGMENT SIZE 100,
PRIMARY KEY (KEYINDEX)
);

GRANT SELECT,DELETE,INSERT,UPDATE ON ADDRESS TO phptest;

commit;

Create the database, using this command:

/opt/interbase/bin/isql -i createdb.sql -u sysdba -p masterkey

It's always a good idea to keep scripts around that are able to (re)create your database(s). I remind you to change the 'sysdba' password, the whole world knows 'masterkey' ;-)

Some code examples

Select:

$dbh = ibase_connect('/home/yves/projects/php/phptest.gdb','phptest','phptest','ISO8859_1',0,1);
$sth = ibase_query('SELECT NAME,ADDRESS FROM ADDRESS WHERE KEYINDEX=?',$HTTP_POST_VARS['KEYINDEX']);
while ($row = ibase_fetch_row($sth)) {
      print $row[0]."n";
      ibase_blob_echo($row[1]);
}
ibase_free_result($sth);
ibase_close($dbh);

The first line makes a connection to your database. Our sql statement is set in the second line. (Note how the form-variable KEYINDEX is substituted into the query.) Finally, with ibase_fetch_row($sth), we fetch line per line (well, only one in this example). In the loop, the db field NAME is printed by:

print $row[0]."n";

As our field ADDRESS is a blob, is comes out with:

ibase_blob_echo($row[1]);

Text blobs need to be treated like this, you cannot print them with a simple 'print'.

InterBase blob functions are not documented in the php manual, but I found the functions in this document from Borland. It describes the php3 api, but all the functions I used happen to work in php4 as well.

Insert:

$dbh = ibase_connect('/home/yves/projects/php/phptest.gdb','phptest','phptest','ISO8859_1',0,1);

$blob_id = ibase_blob_create();
ibase_blob_add($blob_id,$HTTP_POST_VARS['ADDRESS']);
$blob_id_str = ibase_blob_close($blob_id);

$sth = ibase_prepare('INSERT INTO ADDRESS (KEYINDEX,NAME,ADDRESS,CATEGORY) VALUES (?,?,?,?)');
$trans=ibase_trans();
ibase_execute($sth,$HTTP_POST_VARS['KEYINDEX'],
stripslashes(strip_tags($HTTP_POST_VARS['NAME'])),
$blob_id_str,$HTTP_POST_VARS['CATEGORY']);
ibase_commit($trans);
ibase_free_query($sth);
ibase_close($dbh);

Take a look at the lines 3-5. The function ibase_blob_add is needed to prepare the data before you can insert it into the db. Actually, the data from the variable $HTTP_POST_VARS['ADDRESS'] is prepared and put into the blob, using $blob_id_str.

Update:

$dbh = ibase_connect('/home/yves/projects/php/phptest.gdb','phptest','phptest','ISO8859_1',0,1);
$sth = ibase_prepare('UPDATE ADDRESS SET NAME=? WHERE KEYINDEX=?');
$trans=ibase_trans();
ibase_execute($sth,stripslashes(strip_tags($HTTP_POST_VARS['NAME'])),$HTTP_POST_VARS['KEYINDEX']);
ibase_commit($trans);
ibase_free_query($sth);
ibase_close($dbh);

Not much to say for this example.

Well, that's it!

I hope this gives you a successful start with php and InterBase. It is always a good idea to look for existing projects that use php/InterBase. Searching freshmeat for 'php interbase' currently shows about a dozen.