I can't work with a firebird .fdb file, after connecting to it with Libre Base

[jderica]

Edit: To clarify what I did to get to this issue. I used my personal PC this time.

Initial setup:

• Windows 10 machine
• Amazon Corretto 11 jdk for java
• Libre Office suite 7.0.0.3

Step 1: I installed Firebird 3.0.6 and used the following lines with isql:
CREATE DATABASE ‘localhost:G:\pj_secretara_3.1\pj_sec_3.1.fdb’
USER ‘SYSDBA’ PASSWORD ‘masterkey’
PAGE_SIZE 8192 DEFAULT CHARACTER SET UTF8;

Step 2: Opened an existing standard .odb database to acces options>advanced and added a class path archive to jaybird-full-4.0.1.java11

Step 3: Launched a new Base instance and used the connect to existing database, using JDBC option

Step 4: Added the following connection settings
URL: firebirdsql:oo:localhost:G:\pj_secretara_3.1\pj_sec_3.1.FDB?charSet=UTF-8
JDBC driver class: org.firebirdsql.jdbc.FBDriver

Step 5: Created a new table in design view and this is when I noticed that the INTEGER type is formated to currency (as seen in the picture 1) and there’s no longer and “autovalue” option for it. Saved the table and exit design view.

Step 6: This time around, I can add entries into the new table, but the “length” setting is greyed out (picture 2)

I also tried with amazon corretto 8 / jaybird 4.0.1 java 8, but also with open jdk 14 and 11 (both with jaybird 4.0.1 java 11), but still experienced these wierd things.

Now I’m not sure if these are just minor quirks or I’ve done something wrong along the setup. I’m using FlameRobin now to create the tables in SQL and it seems that Libre Base has no issues adding data into them using either the form or directly into the table.

[Ratslinger]

Hello,

Not certain where your problem lies. My current testing on Ubuntu 18.04 is using LO v7.0.0.3. I do have Firebird Server 3.02 installed and OpenJDK 11. Just installed Jaybird 4.0.1 java 11 connector and used in tests.

Java is not needed for a Firebird connection to a file. Have used this for some time without problem. It appears your .fdb may have been created from the server and our versions don’t match. This may be a possibility.

Did connect to a Firebird server file using JDBC and the noted Jaybird connector. Again no problem. Also noted you Java is amazon coretto 11 which is a modified version of OpenJDK. You may want to try OpenJDK or one from Oracle.

You have not mentioned, but it may be worth a try to create a new Firebird file when creating a Base file. If this works, it may be that something in Firebird server 3.06 is causing the problem.

Edit:

There is some confusion here. There are multiple Firebird connections. The most common is the embedded file. There is also the connection to a Firebird server (I believe your question). There is also a Firebird file which is a .fdb file outside of Base but not necessarily any connection or relation to a server. This is where some confusion occurs as this is in the title of your question.

Your issue relates to the auto increment of an integer. This is a problem. I am not certain if there is a bug report open on this. You can search/report here → Bugzilla.

You can create tables with an auto increment field using SQL. See answer in this post → How to create an auto-increment ID column in a Firebird embedded database table? Errors encountered.. Although the auto increment works, it only displays properly after a refresh:

Until the refresh, the value is 0 as shown above. Another item which needs to be resolved.

Another issue is that once the table design is saved using the graphical interface, modification through that means is only deleting then re-adding fields - no changing allowed. You can use SQL.

As for the Firebird file, you can change permissions of a server file and then connect but all security is gone. It is kind of like an HSQLDB split database.

Edit 2020-09-04:

First problem is because of the LO version you are using (recent versions have this fixed). It deals with sub forms and there is a fix. See → firebird_sdbc error for bug report link and one fix method there. Another method to fix is in my answer here → How to unzip ODB file and edit the content.xml file to fix Firebird bug?.

Now your filter table has two fields: ID_A_F1 and LINK_FROM_ID_B_F1. Your list box is saving the data to ID_A_F1 which is the primary key field of the filter table and should not be modified. Instead it should be saved to LINK_FROM_ID_B_F1.

Similar, your sub form link is using ID_A_F1 but it should be LINK_FROM_ID_B_F1:

When those issues are fixed, the form works.

[Ratslinger]

@jderica,

Just noticed your connection string. Don’t have Windows to test but this appears to be part of your problem. Try:

jdbc:firebirdsql:oo://localhost/c:\libre\FIRSTDB.FDB?charSet=UTF-8

or

jdbc:firebirdsql:oo://localhost/c:\\libre\\FIRSTDB.FDB?charSet=UTF-8

[jderica]

Hey and thank you for taking an interest in this.
I’ve redone the whole post in hope that the issue is clearer.

I’ve tried now with Java from openjdk 14 and 11, but still no luck. (couldn’t get a download link for openjdk 8 to try that too)

What do you mean with
“… worth a try to create a new Firebird file when creating a Base file.” ?
Is this about the option “Firebird File” when connecting to an existing database, after you launch Base? I’d like to get that option a shot, but after I select “create new”, type in a name for the .fdb and select finish, I get this error:

firebird_sdbc error:
*Unable to complete network request to host “PC NAME”.
*Failed to locate host machine.
*The specified name was not found in the hosts file or Domain Name Services.
caused by
‘isc_create_database’

I then tried this URL “localhost:file:///G:/a.fdb user SYSDBA password masterkey”, but I just get the “user name not defined in firebird message”. I tried with -u and -pw too, isntead of “user”.

[Ratslinger]

@jderica,

Will look at your post more closely later. Working on another in depth answer. However, have just tested creating a Firebird file and had no problem.

Edit -

Sorry, file is too big to upload. Again, will look at all this a bit later. Must finish other answer first.

[jderica]

I’m a bit lost here. I created the tables with SQL code and so I avoided the issues caused by the Base table designer, but now I don’t understand why my forms don’t work. (i created some forms for testing using an embedded HSQLDB file).
The issue with this form is that I have a list box that selects a record and then a push button that refreshes the form to display that record. This uses a “filter” table with 2 columns, the second of which stores the ID of the record to be listed on refresh.
At first I got a column not found error for LINK_FORM_ID_B_F2 (the column itself being ID_B_F2), so i renamed the column and added “LINK_FORM_” as a prefix. This avoids the column not found error, but the record in the filter table does not change according to the list box selection.

If you could take a look at it and see if it works for you, that would be great! The user is SYSDBA and the pass is “123”.
https://we.tl/t-HwOiofTr6k

[Ratslinger]

@jderica,

This is a question and answer site. Ask a question and get a response to the question asked. You question has started with connection to a file, moved to table interface problems and now with form problems (have found 3 issues with your form). This is three different issues.

Will edit original answer to answer the form problem. Then any further questions should be asked as a new question.

As with all questions, if this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.