connect base to firebird(server) - how??

English
, , , , ,
#1

Reading a previous answer, I now understand that from LO 6.2 onwards Base will have Firebird as the embedded server. That is great - I really approve of the ethics behind the group who do the Fire* software :slight_smile:

So I thought I would get a head start on learning the little quirks that every different SQL engine has (so much for standards, eh wot?). It is not practical for me to install a newer version of LO (I am stuck on 5.2.7 at the mo) so I installed the firebird 3.0 server on the same machine that is running LO. According to ps the server is running and waiting for me.

I also know the first thing I need to do is to connect to the security3 database as the admin user, so I can create other databases. Tools->SQL methinks.

But that is where I am stuck: How do I connect from Base to Firebird?

I have now realised that my problems have been compounded by running an unusual OS, which has been causing problems that are not directly connected with either LO or FireBird.

I have now moved those issues to

I have LO 5.2.7 installed on a system based on an older Debian version.

Do you recommend ODBC or JDBC? (I am already familiar with JDBC as I use it for both Mariadb and MySQL external databases, but I am happy to start learning ODBC if appropriate)

If JDBC, where do I download the Java connector class? And does the URL start with firebird://localhost ? And what port number?

If ODBC, I know I will need libodbc.so.1 butwhat else, if anything, will I need? How would I register my firebird b with LO?

Anythijng else I should know?

Many thanks for your help already on other threads over the last few days… and thanks for your help on this one

Base - use of SDBC Firebird File
running LO with a local non-embedded database under Qubes OS
Can I open a database made in LO Base with another application?open
#2

Hello,

Connecting to Firebird server from Base is similar to connecting Base to any other external DB.

Have tried both JDBC & ODBC connections using Mint 18.3. ODBC connector has a few problems (cannot create Views for one) so using JDBC is probably the better choice. JDBC connector is available here → JDBC Driver.

Driver class is:

org.firebirdsql.jdbc.FBDriver

Connection string is:

firebirdsql:oo://localhost//var/lib/firebird/3.0/data/YOUR_DATABASE.fdb

On Mint 18.3 the above location is the default storage for Firebird server databases.

For Firebird DB management I have settled on FlameRobin found here → FlameRobin

Firebird documentation is here → Firebird Documentation

Edit: Have played further with ODBC connection parameters and have Views now working. Used the following for ODBC:

odbc.ini =

[Firebird-libretest]
Description = Firebird database libreoffice test
Driver = Firebird
Dbname = /var/lib/firebird/3.0/data/DB_NAME.fdb

odbcinst.ini =

[Firebird]
Description=Firebird ODBC driver
Driver64=/usr/lib/libOdbcFb.so

Edit 2018-07-30:

Use FlameRobin (correct name) to find where the stored DB’s are located. Double click on the DB name in FlameRodin to connect to it. The right click on the name & select Properties to present:

image description

which shows the location ( here the DB is ‘Employee’ ) located here:

/var/lib/firebird/3.0/data/employee.fdb

With a simple test, I get the same error with an incorrect Datasource URL. Pay careful attention to this location & how it is applied in the connection string shown above.

Also, mine works with:

firebirdsql:oo://localhost//var/lib/firebird/3.0/data/YOUR_DATABASE.fdb

or

firebirdsql://localhost//var/lib/firebird/3.0/data/YOUR_DATABASE.fdb

The additional oo: was noted somewhere when reading about the Jaybird connector. You may wish to try without.

#3

Also not sure why you are stuck on LO v5.2.7? Have newer on Debian myself.

#4

I was approximating a bit: its actually Qubes, which is itself based on Debian but lags some months behind Debain stable, which itself lags behind the cutting edge of Debian. So I am not surprised you have a more up to date Debian than me.

Qubes is tuned for security rather than to being state of the art. That is a trade-off, but one I perosnally like.

Oddly enough, Firebird 3.0 is included, so it is lagging less with firebird than with LO.

#5

I have always found distro versions to be lacking & sometimes even create their own specific problems. The preferred LO update method for Ubuntu based systems (not certain about yours) is to use a PPA → “LibreOffice Packaging” team. Personally, for various other reasons, install directly from TDF (The Document Foundation).

#6

Qubes is not a normal distro. It runs several semi-autonomous VMs under Xen for security enhancement. I do not understand the details sufficiently to install stuff from “outside”, because I do not know what (if any) changes were made to make firebird work in their system, and to work without inadvertently compromising their security model.

I agree on a stabdard Debian variant like Mint I would install directly from TDF

#7

I STILL can’t get this to connect, and a close to abandoning this :frowning:

FireRobin connects OK, and I created a user and password, created a new database, and created a table in that db, all from FireRobin,

LO connections are rejected, SQLState:08006, ISC error code:335544721

When I click Test Class, it confirms the Java driver is loaded OK, so I am thinking it must be URL causing the problems not the driver. If firebird will talk to Robin why won’t it work with LO?

#8

Also, don’t give up so easily. My early tries at connections took a lot of effort. Subsequently, different connectors can operate differently. ODBC was even more difficult for me to comprehend in the initial stages.

#9

Another note. I am using the Jaybird-full-3.0.3.jar in the Class Path for the connector.

#10

@trueriver Wanted to inform you since I just discovered, it appears LO v6.1 will NOT default to Firebird. It was probably changed due to conversion situations (still some problems with converting decimal & numeric data). Seems scheduled fix for this is v6.2.

#11

thanks for the headsup on this.

So that means HSQLDB v1.8 for another LO point release? (I am guessing nobody will feel enthusiastic about updating HSQLDB when it is already slated for moving away)

#12

regarding Jaybird.

I am using Jaybird 3.0.4 as that was the one that is designed to work with the relevant version of the Oracle JDK listed in the LO advanced settings (1.8, java 8).

I could not see any versions of 3.0.3 on the download page you linked. I am assuming (for now) that this is not an issue but may come back and re-visit this

#13

Just trying to point out to use full jar and not individual ones. Had problems using individual jars. Also, on Mint using Oracle JRE 10 but see no problem with v8.

#14

what permissions do you have on the following directories, please?

/var/lib/firebird

/var/lib/firebird/3.0

/var/lib/firebird/3.0/data

On my system FlameRobin will not create a new database unless there is r-x permission to the world: to get FlameRobin running I used chmod 755 on each of those directories. This seems necessary because FlameRobin runs as the user who is logged in at the time (and rightly so) and seems to want to access that directory as that user

#15

Directory Owner Permissions

/var/lib/firebird root drwxr-xr-x

/var/lib/firebird/3.0 firebird (DBA) drwxrwx—

/var/lib/firebird/3.0/data firebird (DBA) drwxrwx—

firebird (DBA) = firebird - Firebird Database Administrator

#16

I have also, for different testing purposes, created a regular Firebird DB (using FlameRobin), copied, renamed & changed permissions in a different directory and the registered it using FlameRobin. Was able to use this in different ways with FlameRobin & LO.

#17

This is PARTLY an issue with file permissions or locations. Qubes does clever stuff to make sure system changes roll back at reboot, whereas user changes are retained. See

I will Come back and edit this if / when I get a definitive answer to the aspects of this that are independent of the Qubes OS.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.