Macro to connect LibreOffice Base to Firebird Server

[nexturejohn]

Hi, on Windows and using LibreOffice 6.3, I now have a working LibreOffice Base program that uses an embedded Firebird database. I am in the process of substituting the embedded server with a Firebird 3.0 server. The Firebird server is now working and I am able to access it through LibreOffice and SDBC. I have also installed FlameRobin as the GUI for Firebird.

In the tests that I have done, I connect through the LibreOffice database wizard using JDBC to a database (test.fdb) previously created using FlameRobin . This results in a test.odb file which when opened in LibreOffice lets me get to the database test.fdb. The trouble is that LibreOffice prompts me for a username and password for test.fdb

This is not though what I want. I want to be able to open the test.odb file and “automatically” connect to the appropriate
Firebird database in a transparent way, just like when using an embedded database. I presume that I need to write a macro that connects to the test.fdb database and passes username and password.

Can you please help me with this macro, and indicate where to include it in LibreOffice? Thanks.

[Ratslinger]

Hello,

For a description on macros and where they are to be placed see the LibreOffice documentation → Getting Started with LibreOffice. Chapter 13 is “Getting Started With Macros”. Further down that post is the Base section. Chapter 9, “Macros” may be of further interest.

A macro to do this for MySQL was posted in my answer here → How to store password for MySQL?.

Just below the code, noted are the two lines needing change. Theses changes are specific to your environment. Then, as also noted there, the macro is attached to the Open Document event of the .odb - again refer to the documentation.

Have just re-tested this using Firebird server and no other code changes were necessary.

[Ratslinger]

To make it easier, the URL can be gotten automatically. Replace this line:

sURL = "file:///home/YOUR_DIRECTORY/YOUR_BASE_FILE.odb"

with this:

sURL = ThisComponent.getLocation()

Tested on Ubuntu 18.04.

[nexturejohn]

you must have been reading my mind! I had tried what I thought were all possible combinations of the filename c:\d\libre\connection_firstdb.odb, and none worked. Your brilliant suggestion to use getLocation worked straightaway, and also allowed me to see what getLocation was providing. Despite it being on a Windows machine, it wanted forward slashes and a capital C, so file:///C:/d/libre/connection_firstdb.odb was what it wanted! Thanks again. I’ll now move all my tables from the embedded Firebird to the Firebird server, set up the global temporary tables and then move it all to Lubuntu. Thanks again, buona notte from Italy

[mariosv]

Please, if the answer solves the question click .

[nexturejohn]

just for the record, here is the macro that worked for me on a Windows machine running LO 6.3, connecting to a Firebird 3.0 server, with standard username and password (SYSDBA masterkey):

Sub connect_to_database
' bound to Open Document Event 
	Dim sURL as string
	Dim oArgs(0) As New com.sun.star.beans.PropertyValue
	Dim oDocStatus as object
	Dim oIhandler
	oIhandler = CreateUnoListener("InteractionHandler_", "com.sun.star.task.XInteractionHandler")
	sURL = ThisComponent.getLocation()
	oArgs(0).Name = "InteractionHandler"
	oArgs(0).Value = oIhandler
	oDocStatus = StarDesktop.loadComponentFromURL(sURL, "_default", 0, oArgs)
	oDocStatus.DataSource.Password = "masterkey"
End Sub