Base connection to firebird with username / role

[matteo1]

Hi,

my setup consists of writer forms (ubuntu, windows, mac os) as front end, and firebird (docker container running on a synology / DSM server) as the data base.

As we need flexibility with database permissions, I have started using roles that I then assign to users for avoiding any burden and errors.

I however discovered that roles to user assignment (after firebird 3 …my guess) only works if identification specifies the role as well as the user name. This is possible for example with isql, flamerobin and so on.

Anyone knows if this is possible when registering or logging into the database to LO / Base ? If not I am probably forced into writing a specific login macro, something I would like to avoid.

Any help is appreciated.

[fpy]

@matteo1

firebird

server ? or embedded ?

see some gory details here : Base - connecting to Firebird database (w/o ODBC) - #5 by mariosv

[matteo1]

firebird is running on a (docker) server …

I will look if JDBC allows a role parameter like user or password …

[RobertG]

For JDBC try something like this:

jdbc:firebirdsql:oo://localhost/libretest.fdb?charSet=UTF-8

Driver will be org.firebirdsql.jdbc.FBDriver from https://www.firebirdsql.org/en/jdbc-driver/

[matteo1]

Thank you guys : I will try JDBC. For the time being I use sdbc.

BTW which driver can be considered the best in terms of performance and functionality ? I remember that Ratslinger (akalo-guro) was recommending JDBC.

Gave it a try but it fails

Test class succeed but test connection fails …

[mariosv]

Maybe:
jdbc
instead of
dbc
in the jdbc connection

[Villeroy]

Without any such prefix. Protocol jdbc: is already set as indicated by the label in front of the input box.

[matteo1]

Villeroy, many thanks : I did non notice the jdbc: prefix … IT WORKS NOW

[matteo1]

Stopped working
Test Class passed
Test Connection
Unable to complete network request to host “oo”. [SQLState:08006, ISC error code:335544721]

jdbc:firebirdsql:oo://192.168.1.37/var/lib/firebird/data/firebird.fdb?charSet=UTF-8
org.firebirdsql.jdbc.FBDriver

[jucasaca]

The connection strig should be firebirdsql:oo:192.168.1.37:/var/lib/firebird/data/firebird.fdb?roleName=ROLE_NAME&charSet=UTF-8 (the jdbc part is already written)

[Villeroy]

What is the oo:? And the colon behind the IP is one too many unless you add a port number.

[fpy]

@Villeroy

What

a wild recommendation

@RobertG

For JDBC try something like this:
jdbc:firebirdsql:oo://localhost/libretest.fdb?charSet=UTF-8

pointing to a decent documentation wouldn’t hurt

[jucasaca]

@Villeroy

What is the oo:?

The oo is specific for openoffice/libreoffice with jaybird. see Jaybird JDBC Driver Java Programmer’s Manual

@Villeroy

And the colon behind the IP is one too many unless you add a port number.

The colon behind the ip is another way of define a connection string, see it in point two of Firebird 5 Quick Start Guide

[matteo1]

Thanks to all the input I was able to connect : however, when I double click on a table, it opens the window but it does not refresh the view and after few second it crashes.

the string I have used is:

firebirdsql://192.168.1.37//var/lib/firebird/data/firebird.fdb

I will therefore stick to sdbc that appears to be rock solid for the time being (I am probably doing something wrong). The java programmer’s manual is very helpful.

I will try to submit a bug … here what a table looks after you open it, clicking around reveals values.

[RobertG]

You could only use a Firebird server with JDBC or ODBC. The internal driver opens the database, but you can’t connect to a table another user connects to. So it is unusable for server databases.
And: Writing a bug for JDBC without using the setup, which has been described, won’t help here and wont help in 165796 – Cannot display tables content from a JDBC Firebird connection in Base : VIEWING
Reading Connecting to Firebird the support for special oo-extension has been removed in Jaybird 6. So you only could use Jaybird up to version 5 for connecting well to Firebird. Using the direct connection of LO for the connection to a server is described there as a solution, but it isn’t usable because you can’t connect to the same table with two different users this way. See Bug 164433 for connecting problems with JDBC/ODBC and only one user connecting possible with direct connection.

[matteo1]

Many thanks Robertg.

1. I am connecting via SDBC remotely with / and I able to manage simultaneous connections to the remote FIrebird database. I did not know that this is not supposed to work.

2. I am not using the oo as it did not work. Jaybird 6 connects but - as I described - tables do not show up.

I will carefully read the links provided.

UPDATE : as you said the documentation is clear “This URL format is deprecated with jaybird 5 and was removed in Jaybird 6. As a replacement, use the “Firebird External” option in LibreOffice Base.”

In fact I am using the “Firebird External” and it works.

Thanks.