Migrating from InterBase 6.01 to Firebird 1.5 and 2.0

Change record:
Version 1 – 2005-12-2
Version 2 – 2005-12-15 (updated with information from Roman Rokytskyy [RR], Jaybird Project)
Version 3 – 2005-12-23 (updated with additional comment from RR)

1 Introduction

We recently converted our InterBase 6.01 application to Firebird 2.0 Beta and kept a record of differences and their effect on our conversion process. Before we started this, we searched for any conversion guidance document on the web and did not find any one summarizing document, so we decided to keep a record and make it available to others who might want to tread the same path.

Our application (http://www.topicscape.com) in its personal edition, used InterBase to store user data. We selected the database engine before Firebird was well established. Being a personal edition, our product was not designed for network or multi-user access, but InterBase, in its open source version, does not provide for direct connection, only access through the network drivers. Now we have converted to a direct connection using Firebird.

Our application is mainly written in Java, and being a 3D application, uses OpenGL through the GL4Java binding. The Windows version (the first deployed) makes some native calls through JNI.

2 Errors here?

If you have a more intimate knowledge of the inner workings of Firebird and InterBase than us, and find any errors below, we would be happy to make corrections or additions.

We have already had valuable contributions from the leader of the Jaybird sub-project, Roman Rokytskyy. As a result, we have been able to remove some items, provide solutions to some and add appropriate commentary to others. Information below from Roman is introduced as [RR]:" ".

3 Differences that affected us

3.1 Embedded server

InterBase is said to allow a local connection using LIBS, but after a search, we concluded that this is not available for the open source version of InterBase – 6.01.

The embedded Firebird database server allows a direct connection between server and application. This is useful for single-user, single-PC applications as it bypasses network drivers, with a consequent gain in application performance.

Locations of versions that we used:

Live Firebird 1.5 http://prdownloads.sourceforge.net/firebird/Firebird-1.5.2.4731_embed_win32.zip

or

Beta 1 Firebird 2 http://www.firebirdsql.org/download/prerelease/win32/Firebird-2.0.0.11675-0_embed_win32.zip

We believe that later version have been released and should certainly be used in preference to the above, as they had issues that we worked around.

The application with embedded server required three files (around 2MB in total): fbembed.dll; jaybird.dll; and firebirdsql-full.jar.

Using the Firebird embedded server also overcomes problems caused by three personal-security software products that block InterBase when the user is running client and server on one computer. This can happen when all necessary permissions are given, or even if the security software is just installed, not running (see http://groups.yahoo.com/group/firebird-support/message/61830).

3.2 Firebird 2 embedded server and Jaybird 2.0 required libraries:

The main dll files required are: fbembed.dll, jaybird2.dll, and jaybird-full -2.0.0.jar, icudt30.dll, icuin30.dll, icuuc30.dll Libraries required if you perform GFIX and GBAK are: fbclient.dll, gds32.dll, And if you use user-defined functions: ib_udf.dll

3.3 SQL differences

3.3.1 Syntax:

  • If you are using SQL Dialect 1 syntax and double-quotes (") for literals instead of single-quote ('), you will need either a) to convert any queries using " to ' or b) include a statement to switch Firebird to Dialect 1 (see below). InterBase accepts both dialects; Firebird, in its default Dialect 3, accepts only single quote (standard SQL).

    A suitable statement might be something like:

    Connection conn = DriverManager.getConnection("jdbc:firebirdsql:embedded:" + fileName + "?isc_dpb_sql_dialect=1", "SYSDBA", "masterkey");

  • The TO_DATE function should be replaced by CAST(‘09-30-2005 00:00.00’, TIMESTAMP) to support casting a string to a date format attribute in a database.

  • Firebird 2.0: We believe that the following SQL is valid (it was extracted from Firebird documentation Firebird-2.0.0.11675-0_win32docambiguity.txt), but it will return an error message:

    UPDATE TableA
SET FieldA =
(
SELECT SUM(A.FieldB)
FROM TableA A
WHERE A.FieldID = TableA.FieldID
)

  • Cannot use “IN” keyword within nested query (permitted in InterBase); use “EXISTS” to replace any such uses of the “IN” keyword.

  • [RR]: "There are some differences in the "NOW" function behavior".

3.3.2 With Java:

  • Our testing in Java seemed to indicate CallableStatement can accept only one argument in Firebird / Jaybird 1.5. [RR]: "Firebird 1.5 with Jaybird 2.0.2 should also be OK (Jaybird 2.0.2 is due to be released in 2nd half December 2005).Jaybird 1.5.x has bug in parsing the SQL statement in a form EXECUTE PROCEDURE <name> (….) (note the space after the name and before the opening “(“). There is no limit on the number of parameters in either Firebird, or in Jaybird. Jaybird 2.0 can be safely used with Firebird 1.5.x."

After creating a VIEW in Java, you should perform COMMIT before performing a query on the VIEW. [RR]: "This is normal behavior in Firebird but is an anomoly. This works in InterBase. Before the Firebird issue is resolved, it is highly recommended not to mix DDL and DML statements in one transaction"

In Java, a SQL string cannot contain " or ', so you can only specify the quote using a single quote. Use of r n, etc. still works, however.

In Java using Firebird you cannot perform a COMMIT after a SELECT statement. This happens to work with Java in InterBase. [RR]: "The JDBC specification defines such a result set as closed and Jaybird handles it correctly according to the specification. Jaybird 2.0 supports holdable result sets (according to the JDBC specification they "survive" commit), but the drawback is that the complete result set is cached in memory."

3.3.3 Restrictions on using Jaybird: Some useful references

3.3.4 other SQL differences:

  • Connection parameter “charSet” with a value of “UTF8” in InterBase, will have to be changed to “UTF-8” in Firebird.
  • when Firebird cannot handle an exception, it will usually throw a Dynamic SQL Error.

3.4 Existing databases of InterBase origin

InterBase databases should be backed up and restored with Firebird GBAK before accessing them with Firebird.

InterBase has a lower version ODS than Firebird, so Firebird can read InterBase files but not vice versa.

3.5 GBAK and GFIX

GBAK performs backup and restore of a database file, which is used for database recovery.

GFIX performs validation of a database file, which is used to validate if a database is corrupt.

Firebird’s GBAK and GFIX utilities appear to do the same as those of InterBase.

3.6 Using Firebird with Excelsior JET

We compile the Windows version of our application with Jet. While still using Firebird 1.5, Jaybird 1.5 and Jet v.3.6, we found that we could not retrieve data if a database table contained NULL values. See next section under Firebird /Jaybird 1.5 vs. Firebird 2.0. A SELECT query cannot be performed using Connection.createStatement() if we put a transaction commit before iterating through the result set.

4 Firebird /Jaybird 1.5 vs. Firebird 2.0

We had to switch to Firebird 2.0 Beta and Jaybird 2.0 because:

  • Firebird 1.5 with Jaybird 1.5 does not fully support Callable Statements, and we required these to execute stored procedures. [RR]: "In fact, you do not need CallableStatement if you use EXECUTE PROCEDURE syntax. The main goal of CallableStatement is to support vendor-neutral escape JDBC syntax . For stored procedures that is {call <name> [<arg1>,….]} or {? = call <name> [<arg1>,…]} which should hide from the application developer specifics of the procedure call. Jaybird converts such syntax into EXECUTE PROCEDURE <name> …. call (that is why we needed a parser, otherwise we would rely on Firebird). Note, that in order to use selectable procedures (SELECT * FROM <name>[?, …] call) you have to use FirebirdCallableStatement.setSelectable(boolean) method."
  • We experienced some problems with question marks inside a string. Jaybird 1.5 interprets any question marks in Callable Statements as an argument. [RR]: "This is a Jaybird bug."
  • JET 3.6 cannot retrieve data with Firebird 1.5 / Jaybird 1.5 if a NULL value is found within a database table. We moved on to Jet 4.0 and the problem disappeared, so there is no evidence that this is a Firebird/Jaybird problem.

4.1 Differences between Firebird 1.5 and Firebird 2.0:

(The following comparison is based on Firebird 1.5 and Firebird 2.0 alpha 3)

  • Firebird 2.0 changes the ODS (on-disk structure) to version 11. Therefore, after a backup and restore (GBAK) using Firebird 2, the data file can no longer be opened by either InterBase version or Firebird 1.5.

  • Firebird 2.0 supports Callable Statements with multiple arguments.

  • Firebird 2.0 required some more external libraries: icudt30.dll, icuin30.dll, icuuc30.dll

  • Useful new SQL keywords are introduced.

  • Several bugs in Firebird 1.5 are fixed in Firebird 2.0 (since Firebird 2.0 Alpha 3):

  • When an alias is present it must be used or no alias at all must be used in Firebird 2.0. This query was allowed in FB1.5 and earlier versions:

    SELECT RDB$RELATIONS.RDB$RELATION_NAME
FROM RDB$RELATIONS R

    but will now correctly report an error that the field "RDB$RELATIONS.RDB$RELATION_NAME" couldn't be found.

    Solution:

    SELECT R.RDB$RELATION_NAME
FROM RDB$RELATIONS R

    or:

    SELECT RDB$RELATION_NAME
FROM RDB$RELATIONS R

  • This example didn't run correctly in FB1.5 and earlier:

    SELECT RDB$RELATIONS.RDB$RELATION_NAME, R2.RDB$RELATION_NAME
FROM RDB$RELATIONS
JOIN RDB$RELATIONS R2 ON (
R2.RDB$RELATION_NAME = RDB$RELATIONS.RDB$RELATION_NAME
)

If RDB$RELATIONS contains 90 records, it would return 90 * 90 = 8100 records, but in FB2.0 it will correctly return 90 records.

5 From the Firebird_v1.5.ReleaseNotes.pdf

Obviously the release notes need to be studied in full, but we found it handy to have this extract to hand.

5.1 New Reserved Words

The following new Firebird keywords should be added to the list of reserved words published for InterBase 6.0.1.:

BIGINT (1.5)
CASE (1.5)
CURRENT_CONNECTION (1.5)
CURRENT_ROLE
CURRENT_TRANSACTION (1.5)
CURRENT_USER
RECREATE ROW_COUNT (1.5)
RELEASE
SAVEPOINT

The following keywords are reserved for future planned use:

ABS
BOOLEAN
BOTH
CHAR_LENGTH
CHARACTER_LENGTH
FALSE
LEADING
OCTET_LENGTH
TRIM
TRAILING
TRUE
UNKNOWN

The following non-reserved words are recognized in 1.5 as keywords when used in their respective structural contexts:

COALESCE
DELETING
INSERTING
LAST
LEAVE
LOCK
NULLIF
NULLS
STATEMENT
UPDATING
USING

There is other information about keywords in the release notes but they are not relevant to migration from InterBase 6.01 to FB 1.5

6 From the Firebird_v2.0.0.ReleaseNotes.pdf

Obviously the release notes need to be studied in full, but we found it handy to have this extract to hand.

6.1 New Reserved Words & Changes

The following keywords have been added, or have changed status, since Firebird 1.5. Those marked with an asterisk (*) are not present in the SQL standard.

6.2 Added as reserved words

BIT_LENGTH
BOTH
CHAR_LENGTH
CHARACTER_LENGTH
CLOSE
CROSS
FETCH
LEADING
LOWER
OCTET_LENGTH
OPEN
ROWS
TRAILING
TRIM
CLOSE
OPEN

6.4 Added as non-reserved words

BACKUP *
BLOCK *
COLLATION
COMMENT *
DIFFERENCE *
IIF *
NEXT
SCALAR_ARRAY *
SEQUENCE
RESTART
RETURNING *

6.5 Changed from reserved words to non-reserved

Firebird v.200.30 Reserved Words, Third Alpha http://rusty/fb2/reserved_words.html (1 of 2) [7/07/2005 8:10:38 AM]

ACTION
RESTRICT
WEEKDAY *
CASCADE
ROLE
YEARDAY *
FREE_IT *
TYPE

No longer reserved words:

BASENAME *
GROUP_COMMIT_WAIT *
NUM_LOG_BUFS *
CACHE *
LOGFILE *
RAW_PARTITIONS *
CHECK_POINT_LEN *
LOG_BUF_SIZE *

7 Extract from forums

7.1 About ODS:

http://www.ibphoenix.com/main.nfs?a=ibphoenix&l=;KNOWLEDGEBASE;ID='20'

Question:
When connecting to InterBase V3.3 database from InterBase V4.0 on HP and getting error "Unsupported on-disk structure for file /path/v3.gdb: found 1, support 8".
Answer:

The first release on HP 10.20 was InterBase 4.0. Therefore, there is no V3 kit that InterBase can build the bridge from.

Here are two workarounds for this problem: * GBAK the database with transportable option on V3.3 and restore it on V4.0 on HP10. * There is a bridge for the V4.0 InterBase kit on HP9.

Firebird/InterBase Version: 4.0 Operating System: HP-UX

7.2 Subject: Re: [IB-Conversions] transferring InterBase gdb to firebird

From: Helen Borrie <helebor@...>
Date: Wed Mar 2, 2005 12:34 pm
At 10:58 AM 2/03/2005 +0000, you wrote:
>Hi,
>I am currently working on a project where the company is using InterBase as
>its development environment and have asked me to extract information from
>the database. I don't have a copy of InterBase but have come across Firebird
>which is apparently compatible with IB. The company have given me the gdb
>file that contains the IB database. How do I import this into Firebird (into
>an .fdb?)

If it is IB 6.5 or lower, you should be able to log into it directly. It
doesn't have to be renamed (Firebird doesn't care what its name is),
although on a Windows XP system you should rename it, because XP does nasty
things to files with the extension of ".gdb".
It depends on what version of IB it is. It's essential to find out.

>Are there any commands/tools to achieve this quickly and
>painlessly.

The first thing you need to do (assuming you have a Firebird server
installed) is to find out the On-disk Structure version of the
database. Assuming you're on Windows (are you?) and the database file is
d:dataMyDB.gdb....(substitute appropriately)...
Open a CMD window and go to c:Program FilesFirebirdFirebird_1_5bin.
Do this:
gstat -h d:dataMyDB.gdb -user sysdba -password masterkey
A display appears that tells you some stuff from the header page of the
database. Look for ODS version. If it is 10.0 or lower, then you should
be able to connect to it.
Another thing you are interested in is SQL Dialect. If it is dialect 1,
many tools need to be told to use Dialect 1.
Given that the db has the suffix ".gdb" one can be reasonably hopeful it's
not recently created. Borl is advising people these days to use ".ib" as
the suffix because of the wickedness of XP.
Report back and we'll advise you further.
Helen

7.3 Update InterBase data file to Firebird:

From: Helen Borrie <helebor@...>
Date: Wed May 8, 2002 1:49 am
Subject: Re: [IB-Conversions] Migrate IB db to a FB db
At 08:06 AM 08-05-02 +0700, you wrote:
>Hello all,
>I have just installed FB v1.0 server to a my new Windows 2000 Profesional,
and then back up existing IB v6.0 db with ibconsole.
If you did things in that order, then you already used Firebird gbak to
back up your IB 6 database. IB Console is just a client application and
works through gds32.dll, like any other.

>Question:
>1) How to restore and convert my IB database to a Firebird database format ?
>Can I use gbak in Firebird directory to restore the IB db backup and convert
>it to Firebird db, if yes then what is the syntax ?

Yes, you can use gbak in the Firebird directory...or you can use the
Restore feature in IBConsole.
As for the syntax for gbak, it would be best that you get hold of the
Operations Guide, which is part of the InterBase 6 documentation set,
obtainable here:
ftp://ftpc.inprise.com/pub/InterBase/techpubs/ib_b60_doc.zip

>2) Are there any similar IBConsole which can run in FireBird ??
>Freeware or shareware better.

Yes - many. Go to the Contributed Downloads pages at
http://www.ibphoenix.com and browse the options. I use IB_SQL, which you
can download from http://www.ibobjects.com
cheers,
Helen

8 Differences that we read about but that did not affect us

We did not confirm the stated “Limits of Firebird Database” but this reference may be useful: http://www.ibphoenix.com/main.nfs?a=ibphoenix&l=;FAQS;NAME='System+Limits'