Ask Your Question

How can I create a portable split embedded/local firebird database in the manner that is done with HSQLDB?

asked 2018-08-09 21:23:17 +0200

baseuser1001 gravatar image

updated 2020-08-02 17:34:39 +0200

Alex Kemp gravatar image

Hi ppl, I was wondering if there's a way to have a portable split firebird database. In my current setup I'm using LO 6.0, firebird 3.0 *.dlls and jaybird 2.x but I have to manually register the class path when the files are copied to a different machine.

edit retag flag offensive close merge delete


@baseuser1001 Have you created this DB with the Firebird 3.x server? If not then how?

Ratslinger gravatar imageRatslinger ( 2018-08-09 21:48:02 +0200 )edit

I've created a something.fdb file with the a standalone embedded server, not the provided by LO, using FlameRobin.

After some warnings about character sets (I haven't figured yet how to pass the correct lc_type through Base) the client connects just fine.

baseuser1001 gravatar imagebaseuser1001 ( 2018-08-09 21:57:16 +0200 )edit

@baseuser1001 Am working on answer will post after some testing is done. Couple of solutions available.

Ratslinger gravatar imageRatslinger ( 2018-08-09 22:38:35 +0200 )edit

First I would ask a question back: Why? When using an embedded HSQL file there was a legitimate reason to jump through this hoop but with firebird that reason is non-existent. Basically with HSQL the way data was written to disk, which was using a compressed file format, was problematic, so people did this split trick to get around that. Using Firebird SDBC the data is never written to disk until the Base file, the container, is closed then the entire data base is written in a single function.

DrewJensen gravatar imageDrewJensen ( 2018-08-10 21:44:15 +0200 )edit

One more comment - you could look at using the Firebied File sdbc, which expects the actual .fb file to exist outside the Base file to begin with. There is a glaring bug with that driver at the moment, but the macro code to fix it is actually less complicated then the split database code.

DrewJensen gravatar imageDrewJensen ( 2018-08-10 21:50:28 +0200 )edit

@DrewJensen My view is this. If you are using Firebird server as your main DB this becomes a nice mechanism to share with another or to temporarily copy to another system for portability. Also, isn't it true that in Firebird embedded the data is not saved (committed) until you actually do a Save?

Ratslinger gravatar imageRatslinger ( 2018-08-10 21:55:32 +0200 )edit

Also, could you point me to this SDBC bug and macro fix? Have not run across this yet.

Ratslinger gravatar imageRatslinger ( 2018-08-10 21:57:56 +0200 )edit

First the problem with Firebrd File SDBC is found in this issue: The fix is easy, issue a commit command to the data engine before the file closes (or assign to appropriate events, such as Closed a sub-component though I haven't tested that fully)

DrewJensen gravatar imageDrewJensen ( 2018-08-10 22:23:32 +0200 )edit

As for the embedded Firebird sdbc driver, as data updates to the datastore that doesn't happen till you close the file, other artifacts in the base file new forms, query defs, etc are written when those are created or altered and effects from DDL commands (create table, view, etc) are written as they happen. That is what the developer had to say and that is what I have found to be true.

DrewJensen gravatar imageDrewJensen ( 2018-08-10 22:26:34 +0200 )edit

Much appreciate the information and quick response. Will do some more testing with Firebird File.

Just as a note, I also use Firebird server just so I can transfer data to Firebird embedded. It is currently the only method to get my data transferred without errors (numeric & decimal fields major problems). The parallel two month test vs MySQL & PostgreSQL went very well.

Ratslinger gravatar imageRatslinger ( 2018-08-10 22:30:28 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-08-10 00:34:55 +0200

Ratslinger gravatar image

updated 2018-08-11 07:45:36 +0200


Hope I am correct in saying when this file is to be moved to another computer (or wherever) it is no longer accessible from the Server. If this is incorrect, please give more info on what the entire picture is you are attempting.

The following is based upon the DB no longer being connected to the Firebird 3.x server.

There are a couple of ways to make the DB (or copy of) portable. First is to actually turn it into an embedded DB. The embedded Firebird file is just a backup file of the .fdb DB you have. The basics of this is covered in my answer on this post -> Base: HSQLDB to Firebird migration for existing databases.

The other manner (just tested) is to use the SDBC connection and a Macro (a bit like portable split HSQL DB's). You select this connection when creating the DB by selecting Firebird File after selecting Connect to an existing database. You can change an existing connection (JDBC for example) by selecting from menu on opening .odb screen Edit->Database->Connection Type.... With this you connect to a xxx.fdb DB via a file path.

With the above in place the next element is to check if the current setting is in concert with the current location. In case it is not obvious, for this to be effective the .odb & .fdb need to be in the same directory. The following is the macro I used to test the process (Linux Mint 18.3 OS):

Option Explicit

Sub CheckLocation
    Dim sPathURL      As String
    Dim sPath         As String
    Dim sName         As String
    Dim sPath2        As String
    Dim sLeft         As String
    Dim sDB           As String
    Dim sCheckPath    As String
    Dim iLen2         As Integer
    Dim iURLLen       As Integer
    Dim x             As Integer
Rem Get Document location
    sPathURL = ThisDatabaseDocument.URL
    sPath = ConvertFromURL(sPathURL)
    sName = ThisDatabaseDocument.Title
    iLen2 = InStr(sPathURL, sName)
    sPath2 = Left(sPathURL, iLen2-1)
Rem Strip needed current source info & get fdb name
    sPathURL = Right(ThisDatabaseDocument.DataSource.URL,Len(ThisDatabaseDocument.DataSource.URL)-14)
    iURLLen = Len(sPathURL)
    for x = iURLLen -4  to 1 Step -1
        sLeft = Left (sPathURL,x)
        If Right(sLeft,1) = "/" then Exit For  'Check for "\" if on Windows'
    Next x
    sDB = Right(sPathURL,iURLLen - x)
Rem create what should be current location

Rem  Removed       sCheckPath = "sdbc:firebird:" & sPath2 & sDB
Rem Replaced with:
    sCheckPath = sPath2 & sDB

Rem If current location is not same as constructed location, replace the current location
    If sCheckPath <> sPathURL then

Rem   Removed         ThisDatabaseDocument.DataSource.URL = sCheckPath
Rem Replaced with:
        ThisDatabaseDocument.DataSource.URL = "sdbc:firebird:" & sCheckPath

    End If
End Sub

This was attached to the Open Document event of the .odb file.

Limited testing has been done, and again on Linux only, but have seen no problems in testing thus far.


While the original routine worked, it was always making the change. Changing two lines will correct this. The above code was modified. The changes are near the end of the routine. The old ... (more)

edit flag offensive delete link more


Note: Commit problem has been fixed in newer LO versions. LO v6.3.x seems to work without problem. Fix may have been as early as v6.2.1.x

Ratslinger gravatar imageRatslinger ( 2019-09-02 23:05:15 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-08-09 21:23:17 +0200

Seen: 1,001 times

Last updated: Aug 11 '18