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.
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)
Hello,
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.
Edit:
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.
Edit 2018-08-10:
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
.
.
Edit 2021-10-30:
Because of LO v7.2.x tdf#144329 this sub does not work correctly.
To fix, replace:
sName = ThisDatabaseDocument.Title
with:
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)
after:
sPathURL = ThisDatabaseDocument.URL