LibreOffice as front-end to external Firebird 4 Database (installation notes)

I have recently/currently ‘migrated’ a complex project from LO+FB(embedded) to LO+sdbc+FB4(external) …

Since I found various useful info in different places I thought I would put some notes here for others to use (draft1):

First, Why Use Firebird as EXTERNAL database anyway?

  • NB: not at all necessary, even for complex projects used by a small team or individual – I fact I did most development (ie learning :o) using LO6 with embedded FB only…
  • However there are numerous admin/dev functions that are possible/much easier with (any) external DB ….
  • … Firebird is suitable for me because (amongst other things) the deployment is for single/few users, using local WLAN/LAN connections, with no actual need for high concurrence with connections or data-syncing between users in this use case (‘daily’ sync of DB ‘shards’ to a master DB is enough)
    -….Additionally, the hardware used includes some very low spec devices using Linux, Win7 and Win10 – FB(c++) is highly functional in this environment.
    -…Also, the project requires ‘seamless’ integration with portable PCs/tablets used in variable fieldwork conditions - .odb and .fbd files are easy to pass around
    -…Final products of the project include full text publishable reports, scientific and museum data, archive formats, GIS, photogrammetry data, ect ect – so integration with other LO software and the wider open-source community is very useful!
    -…Finally the project aims to create a ‘template’ which can be easily modified or harvested for parts in future (and many examples of my amateurish SQL :slight_smile: )

INSTALLATION NOTES: (mainly with reference to win7)

-currently I find issues with LO7.2 and my existing projects…so suggest use LO7.0 or LO7.1 (or earlier)

  • download and install Firebird4 > follow install options (superclassic mode seems a good choice for me – also to use thrid party tools enable “copy to directory service” option)


-start FB and use isql.exe to CREATE a blank database …

*create database ‘q:\Libre_ArcBASE\SMB20\ARCBASE\SMB20_arcfire.fdb’ page_size 8192 default character set ISO8859_1 *
(note the ; as confirmation at CON> - this is automatically logging in as default user=SYSDBA password=masterkey at this stage)
-Also useful to CREATE USER at this stage … … …
-Finally, close isql.exe but leave FB running …

-check your own OS Java version (suggest use/install Java8, not later, as FB/JayBird seems to plan variable support only for later versions)
-download the Firebird JDBC connector (JayBird) – make sure the version matches both the FB version and your Java version.
-unzip Jaybird and place somewhere useful :slight_smile:

-open LO > options > advanced > class path > add archive > … go to the JayBird folder, and add both the xxxx<java_version>.jar and the xxxx_FULL.jar
-confirm and LO should ask for restart…

                                             **!!!  IMPORTANT  !!!**

there is an unresolved issue (at least one!) that has been addressed with a macro-extension fix from @Ratslinger (thankyou!)– WITHOUT THIS FIX LO FORMS/SUBFORMS WILL BREAK!!! get the .oxt here and apply it following the instructions given:

restart LO

-open LO > new database > connect to…>select JDBC connection type >

  • enter the “datasource string” and “driver string” …
    firebirdsql:oo:localhost/3050:C:\Program Files\Firebird\Firebird_2_1\examples\empbuild\EMPLOYEE.FDB
    firebirdsql:oo:\Program Files\Firebird\Firebird_2_1\examples\empbuild\EMPLOYEE.FDB
    firebirdsql:oo:GNOMESGHOST-PC/3050:C:\Program Files\Firebird\Firebird_2_1\examples\empbuild\EMPLOYEE.FDB

add “ ?charSet=UTF-8” to define e.g. UTF8 character set, e.g.
firebirdsql:oo:localhost/3050:C:\Program Files\Firebird\Firebird_2_1\examples\empbuild\EMPLOYEE.FDB?charSet=UTF-8

                                       ! note the “ :oo: “ in the string !


test driver > test connection (using either user=SYSDBA/masterkey or your new created USER and passwords)
continue onwards to create the new .odb and open it!

INSTALL FlameRobin !!! (and wonder how you ever made an FB(embeded) .odb without it ! :slight_smile: )

To do the ‘migration’ I did the following exploiting the drag-drop GUI functionality;

open both the old LO(embedded)(live) project and the new LO(external)(blank) projects >

drag-drop each TABLE,QUERY,FORM, REPORT from old to new > has to be one at a time :confused: (no shift-select)
this works great for most tables with data…
HOWEVER clob data types are (as always) a pain …. in FB(LO(emb)) they are BLOB SUB TYPE 1 … in FB4 they are BLOB SUB TYPE TEXT … this mean table can not be copied in one operation > first drag-drop>paste definitions only
then copy old data to a spreadsheet > select the data in the spreadsheet and copy again > then paste into the new table definitions in the new .odb (this effectively converts ‘old’ clob to varchar, then varchar to ‘new’ clob, + puts all your other data back : )
----beware with CLOB that might exceed the spreadsheet cell limit…you will have to find a different approach to re-fill those columns from the old database.

Since you will need to remake all the non-primary-key indexes, now is a great time to review your structure !!! (using FlameRobin tools here is much better than LO>table>edit…)

-other issue with datatypes likely – take some time – troubleshoot and test queries, forms

on this journey, some better coding, you may do :slight_smile:

macros can be copied and pasted all together from the old to the new using the LO editor
do other tasks and checks before recreating your TRIGGERS (copy/paste into the LO sql-tool - you did keep them all in a handy text file didn’t you?)

nb/ some PROCEDURES might need to be recreated earlier to test any QUERY or FORM that calls for them.

I also had to modify some macros (harvested previously from this very site) that called merge/insert/delete/update SQL statements;
-previously LO+FB(emb) did not seem to care about executeQuery vs executeUpdate, but in LO+FB(ext) this had to be altered to be defined explicitly (ie SELECT with results returned vs ‘UPDATE’ with no results returned) - the corresponding “DIM ResultSet Object” associated with executeQuery had also to be removed to make the macro function again … always something new to learn on the way.

Here for reference are example codes for buttons on forms > they do not run on their own as they use the Parent property of the open form …

Dim DatabaseContext As Object
Dim DataSource As Object
Dim Connection As Object
Dim Statement As Object
'Dim ResultSet As Object
DatabaseContext = createUnoService(“”)
DataSource = ThisComponent.Parent.DataSource
Connection = DataSource.GetConnection("","")
Statement = Connection.createStatement()
ResultSet = Statement.executeUpdate (" add_your_SQL ")
End sub


Dim DatabaseContext As Object
Dim DataSource As Object
Dim Connection As Object
Dim Statement As Object
Dim ResultSet As Object
DatabaseContext = createUnoService(“”)
DataSource = ThisComponent.Parent.DataSource
Connection = DataSource.GetConnection("","")
Statement = Connection.createStatement()
ResultSet = Statement.executeQuery (" add_your_query_SQL ")
End sub

for a stand alone macro, datasource needs to be defined:

Dim DatabaseContext As Object
Dim DataSource As Object
Dim Connection As Object
Dim InteractionHandler as Object
Dim Statement As Object
'Dim ResultSet As Object 'add in again if a QUERY
DatabaseContext = createUnoService(“”)
DataSource = DatabaseContext.getByName(“MY_REGISTERED_ODB_NAME”)

If Not DataSource.IsPasswordRequired Then
Connection = DataSource.GetConnection("","")
InteractionHandler = createUnoService(“”)
Connection = DataSource.ConnectWithCompletion(InteractionHandler)
End If

Statement = Connection.createStatement()
ResultSet = Statement.executeUpdate(" SOME_SQL ") 'change to executeQuery if a query + add in the last few lines

'If Not IsNull(ResultSet) Then
’ While
’ MsgBox ResultSet.getString(1)
’ Wend
'End If
End Sub

1 Like


Please note macro extension seems to have problems in latest LO version. Also, I believe Jaybird connector is JDBC not SDBC.

Seems issue was on my system.

1 Like

…noted:), cheers

some additional notes:

FB3 (LOemb) uses GENERATED BY DEFAULT AS IDENTITY for Base “auto-increment” function –
FB4(external) can also use GENERATED ALWAYS AS IDENTITY –

to make LO(emb) and LO(ext) Base documents with ‘simple copy/paste’ table exchange, make sure to use the FB3 syntax in SQL-tool to initially create the tables in LO !!!


… … LO(ext) does pay attention to GRANT options, so you may need to set these explicitly in e.g. FlameRobin for users of the LO front-end … to do this ‘efficiently’ I did this: get all table/view/proc/trigger names from RDB$DATABASE as result set > put into a spreadsheet > export as CSV > then use regex and/or text editor find/replace to build a bulk-grant script for FlameRobin, eg.
" GRANT ALL TO PUBLIC ON <table_name1> ;
GRANT ALL TO PUBLIC ON <table_name2> ;
… ; "

opps: I forgot that LO SQL-tool does not like multiple instructions > just one at a time :confused: …so creating a PROCEDURE in LO(emb) to execute multiple SQL statements copied from re-build scripts(from e.g. FlameRobin) might be the way …

NB/ there does not appear to be simple way to copy/paste LO(emb) auto-increment field to LO(ext) > the auto property is always dropped :confused: — however with LO(ext) 3rd party editors (e.g. FlameRobin) can be used to create dependency drop + rebuild scripts where the GENERATE clause can be added to the CREATE TABLE () sections — this is a bit tedious, but hey ho …