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.
How can I create a portable split embedded/local firebird database in the manner that is done with HSQLDB?
@baseuser1001 Have you created this DB with the Firebird 3.x server? If not then how?
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 Am working on answer will post after some testing is done. Couple of solutions available.
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.
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 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?
Also, could you point me to this SDBC bug and macro fix? Have not run across this yet.
First the problem with Firebrd File SDBC is found in this issue: https://bugs.documentfoundation.org/show_bug.cgi?id=106463
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)
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.
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 I did misstate one detail on the embedded Firebird sdbc behavior, data changes (inserts, deletes and edits) are written to the disc image when you save the file, not when you close it, per se. (a significant difference)
@drewjensen Thanks. That is what I had originally understood from various posts on Bugzilla.
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 is Rem’ed & the new noted.
In light of the comments by @drewjensen above, do not use the process above for Firebird File connection unless the commit is incorporated (see comments below question). Will update this answer when I’ve had a chance to investigate further. In the meantime, using the DB in a Firebird embedded .odb does work as I have used this for months.
Edit #2 2018-08-10:
Have done some initial testing. With this macro:
Sub PerformCommit Dim oStatement As Object Dim sSQL As String Dim result if IsNull(Thisdatabasedocument.CurrentController.ActiveConnection) then Thisdatabasedocument.CurrentController.connect endif oStatement = Thisdatabasedocument.CurrentController.ActiveConnection.createStatement() sSQL = "commit" on error goto SQLerror result = oStatement.execute(sSQL) SQLerror: End Sub
attached to the
View is going to be closed event of the .odb all data seems to be saved. Note that this does not mean you can forgo the normal saving when required. This is simply to alleviate the problem noted previously.
If attempting, any problems noted will be appreciated.
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
Because of LO v7.2.x tdf#144329 this sub does not work correctly.
To fix, replace:
sName = ThisDatabaseDocument.Title
Rem Fix for no name in title bar GlobalScope.BasicLibraries.LoadLibrary("Tools") sName = FileNameoutofPath(sPathURL)
OR can fix and place title at same time. Place:
Rem Fix for no name in title bar GlobalScope.BasicLibraries.LoadLibrary("Tools") ThisDatabaseDocument.Title = FileNameoutofPath(sPathURL)
sPathURL = ThisDatabaseDocument.URL