Bug 130334 - firebird direct connector fails to display tables with a field representing an array
Summary: firebird direct connector fails to display tables with a field representing a...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.4.0.3 release
Hardware: All All
: medium normal
Assignee: Julien Nabet
URL:
Whiteboard: target:7.0.0 target:6.4.3
Keywords:
Depends on:
Blocks: Database-Firebird-Default
  Show dependency treegraph
 
Reported: 2020-01-31 18:15 UTC by Dan Lewis
Modified: 2020-05-15 17:08 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
this is the requested database. (2.40 MB, application/octet-stream)
2020-01-31 19:28 UTC, Dan Lewis
Details
This is the database I used to access the employee.fdb file. (10.70 KB, application/vnd.oasis.opendocument.database)
2020-01-31 21:53 UTC, Dan Lewis
Details
picture of my search for libfbclient file on my hard drive (38.19 KB, image/png)
2020-02-04 14:52 UTC, Dan Lewis
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Dan Lewis 2020-01-31 18:15:21 UTC
Description:
The installation file from the computer's O/S distribution repository, employee.fdb. Connecting to this database using Base using the direct firebird connector works. Viewing the tables of this database results in all of the tables except for JOB and PROJ_DEPT_BUDGET being open for viewing fields and data. The error message is:
 firebird_sdbc error:
*Incorrect values within SQLDA structure
*empty pointer to data
*at SQLVAR index 3
caused by
'isc_dsql_fetch'

Steps to Reproduce:
1.Install firebird using your distributor's repository.
2.For Ubuntu, extract employee.sql from employee.sql.gz. Then using isql-fb to create employee.fdb from employee.sql.

3.New > Connect to an existing database > Firebird file. Click Next. 
4.Browse to the location of employee.fdb.
5.Click Finish.
6.Browse to where this LO database is to be located and name it.
7.Click Save.
8.Open the LO database and click Tables in the main database window.
9. In the view area, change View to Document to the right of the list of tables for employee.fdb.
10.Click each table in the table list to view the content of each one.


Actual Results:
I got an error message for the tables JOB and PROJ_DEPT_BUDGET. The other tables can be opened revealing their fields and data. I had also connected to this database using isql-fb -user SYSDBA -password 'password' <path to employee.fdb>. Then SQL> SHOW TABLES; All of tables of employee.fdb are then shown including JOB and PROJ_DEPT_BUDGET.

Expected Results:
all of the tables can be opened.


Reproducible: Always


User Profile Reset: No



Additional Info:
I use Ubuntu 18.04 with the latest updates and LibreOffice 6.3.4.1. It seems to work for Open SuSe according to Robert Großkopf.
Comment 1 Julien Nabet 2020-01-31 18:53:10 UTC
Would it be possible to have a file with embedded Firebird (hoping it can be reproduced too in this case)?
It would avoid to require Firebird server install + config.
Comment 2 Dan Lewis 2020-01-31 19:28:01 UTC
Created attachment 157568 [details]
this is the requested database.
Comment 3 Julien Nabet 2020-01-31 20:56:08 UTC
I expected an odb file containing the firebird tables.
Comment 4 Dan Lewis 2020-01-31 21:53:42 UTC
Created attachment 157571 [details]
This is the database I used to access the employee.fdb file.
Comment 5 Dan Lewis 2020-01-31 22:03:42 UTC
(In reply to Julien Nabet from comment #3)
> I expected an odb file containing the firebird tables.

My problem is not with an embedded firebird database; it is with using firebird1.odb which connects to employee.fdb. Because of that, firebird1.odb contains the location of employee.fdb. The only way for you to change the address containing employee.fdb.
Comment 6 Julien Nabet 2020-01-31 22:25:11 UTC
Indeed it seems specific to Firebird external to an odb, can't help here=>uncc myself.
Sorry for the noise.
Comment 7 Alex Thurgood 2020-02-04 08:38:01 UTC
@Dan : errors like the one you reported are often associated with the fbclient library being used.

It could be that the libfbclient library provided within LO does not have support for the datatypes defined in the table.

Alternatively, it could be that LO is trying to use a libfbclient that is external to LO and that it is that library which is causing the issue. Only an inspection of the Ubuntu packages will be able to indicate whether you have more than one libfbclient library installed.

This might also explain why the tables are read correctly by isql-fb, as that client program would use the system libfbclient, rather than any lib provided with LO.


You don't mention where you get your LO6341 from. I'm not sure that Ubuntu provides LO6341 in the default package repositories, so have you installed it from somewhere else ? (PPA, TDF download website ?)
Comment 8 Alex Thurgood 2020-02-04 08:54:19 UTC
I can confirm the described behaviour with

Version: 6.3.4.2
Build ID: 60da17e045e08f1793c57c00ba83cdfce946d0aa
Threads CPU : 4; OS : Mac OS X 10.15.3; UI Render : par défaut; VCL: osx; 
Locale : fr-FR (fr_FR.UTF-8); Langue IHM : fr-FR
Calc: threaded

and the employee.fdb file provided with the firebird ext source code in my master LO core build tree.

The main difference I can see between the tables that fail and the tables that don't is that the failing ones have 3-field primary keys.

Most of the other tables have only 1 primary key field, with EMPLOYEE_PROJECT having a 2-field primary key. This table is also displayed without error.

So, it could be that the problem is due to our code being unable to handle more than 2 fields defined as joint primary key ?
Comment 9 Dan Lewis 2020-02-04 14:52:35 UTC
Created attachment 157646 [details]
picture of my search for libfbclient file on my hard drive
Comment 10 Dan Lewis 2020-02-04 15:10:00 UTC
(In reply to Alex Thurgood from comment #7)
> @Dan : errors like the one you reported are often associated with the
> fbclient library being used.
> 
> It could be that the libfbclient library provided within LO does not have
> support for the datatypes defined in the table.
> 
> Alternatively, it could be that LO is trying to use a libfbclient that is
> external to LO and that it is that library which is causing the issue. Only
> an inspection of the Ubuntu packages will be able to indicate whether you
> have more than one libfbclient library installed.
> 
> This might also explain why the tables are read correctly by isql-fb, as
> that client program would use the system libfbclient, rather than any lib
> provided with LO.
> 
> 
> You don't mention where you get your LO6341 from. I'm not sure that Ubuntu
> provides LO6341 in the default package repositories, so have you installed
> it from somewhere else ? (PPA, TDF download website ?)

You may be right. To find out, I first used synaptic to remove firebird files that came from Ubuntu. Then I use a terminal window with administrative rights to remove every libfbdlient on my hard drive. I used sudo updatedb several times as I do so to make sure that I had removed every one. I have downloaded Firebird 3.05 for their website and then installed it. I still get the same results: an error for the same tables of employee.fdb. The latest attachment was made after connecting to employee.fdb using the direct connection. I conclude that the Ubuntu version of Firebird has anything to do with the problem.
   I download all of my LibreOffice versions are downloaded from the libreoffice.org. The are the pre-release versions which I try always get the latest version when it comes out. So, here is an update: I am now using 6.3.4.2 getting this error message. I also have LO 6.4.0.3 which I used as a test getting the same results: an error message.
Comment 11 Julien Nabet 2020-03-09 13:46:16 UTC
On Win10 with master sources updated today, I gave a new try.

Personal note:
- in DB wizard, use "connect to an existing database"
- select Firebird file
- click "Browse..."
- select fdb file
and validate.

Then, on Windows only because it works directly on Linux:
- Menu Edit/Database/Property
- Replace file:///...
by Replace file://...

I had an assertion (because I use debug option) for these 2 tables.
The problem isn't multiprimary key but the use of SQL_ARRAY which isn't implemented at some locations:
- https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/firebird/Util.cxx?r=1dd9200b#313
- https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/firebird/Util.cxx?r=1dd9200b#370

However, I noticed in https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-datatypes-bnrytypes.html#fblangref25-datatypes-array that an Array was in fact a BLOB, extract:
"Arrays in Firebird are stored in BLOB of a specialized type"

Considering this can be treated as a BLOB, I changed locally the 2 elements I quoted and I could open the tables.
Comment 12 Commit Notification 2020-03-09 19:55:36 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/2ede753a8b7adecbf6ca78745e43e23c7498e289

tdf#130334: Firebird deal with array fields

It will be available in 7.0.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 13 Commit Notification 2020-03-25 15:32:21 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-6-4":

https://git.libreoffice.org/core/commit/ee2f51cd43ef4e5fbda33918daee26e06ab38580

tdf#130334: Firebird deal with array fields

It will be available in 6.4.3.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.