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):
BACKGROUND:
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
)
INSTALLATION NOTES: (mainly with reference to win7)
INSTALL FIREBIRD
-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)
CREATE FIREBIRD DB and USER
-start FB and use isql.exe to CREATE a blank database …
e.g.
*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 …
GET JAYBIRD JDBC CONNECTOR
-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
CONFIGURE LIBREOFFICE
-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
PREPARE LO-to-FB CONNECTION :
-open LO > new database > connect to…>select JDBC connection type >
-
enter the “datasource string” and “driver string” …
DATASOURCE STRING
firebirdsql:oo:localhost/3050:C:\Program Files\Firebird\Firebird_2_1\examples\empbuild\EMPLOYEE.FDB
or
firebirdsql:oo:192.168.0.1/3050:C:\Program Files\Firebird\Firebird_2_1\examples\empbuild\EMPLOYEE.FDB
or
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 !
DRIVER STRING
org.firebirdsql.jdbc.FBDriver
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 ! )
…
EMBEDDED TO EXTERNAL ‘MIGRATION’
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
(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
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.
MACROS
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 …
Sub UPDATE_MY_TABLE
Dim DatabaseContext As Object
Dim DataSource As Object
Dim Connection As Object
Dim Statement As Object
'Dim ResultSet As Object
DatabaseContext = createUnoService(“com.sun.star.sdb.DatabaseContext”)
DataSource = ThisComponent.Parent.DataSource
Connection = DataSource.GetConnection("","")
Statement = Connection.createStatement()
ResultSet = Statement.executeUpdate (" add_your_SQL ")
End sub
and
Sub QUERY_MY_TABLE
Dim DatabaseContext As Object
Dim DataSource As Object
Dim Connection As Object
Dim Statement As Object
Dim ResultSet As Object
DatabaseContext = createUnoService(“com.sun.star.sdb.DatabaseContext”)
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:
Sub SQL_EXAMPLE
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(“com.sun.star.sdb.DatabaseContext”)
DataSource = DatabaseContext.getByName(“MY_REGISTERED_ODB_NAME”)
If Not DataSource.IsPasswordRequired Then
Connection = DataSource.GetConnection("","")
Else
InteractionHandler = createUnoService(“com.sun.star.sdb.InteractionHandler”)
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 ResultSet.next
’ MsgBox ResultSet.getString(1)
’ Wend
'End If
End Sub