Bug 115893 - LO Base Report Builder Does Not Display CLOB (memo field) Data of External HSQLDB and Firebird (embedded / file)
Summary: LO Base Report Builder Does Not Display CLOB (memo field) Data of External HS...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected) release
Hardware: x86-64 (AMD64) Linux (All)
: medium enhancement
Assignee: Not Assigned
: 122400 (view as bug list)
Depends on:
Blocks: Database-Reports-Builder
  Show dependency treegraph
Reported: 2018-02-21 00:45 UTC by JimJ_Wpg
Modified: 2020-08-15 00:48 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:

Screenshot of LO Base Report Writer not displaying CLOB data (435.01 KB, image/png)
2018-02-21 00:47 UTC, JimJ_Wpg
mydb.script DDL of "Business Contacts" HSQLDB 2.4 file (17.23 KB, text/plain)
2018-02-23 19:32 UTC, JimJ_Wpg
mydb.properties file (99 bytes, text/plain)
2018-02-23 19:34 UTC, JimJ_Wpg
same error firebird engine (7.52 KB, application/vnd.oasis.opendocument.database)
2018-05-31 14:30 UTC, Drew Jensen
example firebird odb with workaround (41.56 KB, application/vnd.oasis.opendocument.database)
2018-05-31 15:28 UTC, Drew Jensen

Note You need to log in before you can comment on or make changes to this bug.
Description JimJ_Wpg 2018-02-21 00:45:22 UTC
LibreOffice 6.0.1 and prior versions does not import data from CLOB fields, for example a "memo" or "comments" field.

Instead LO Base Report Writer displays the following for each record:

Any[Type[com.sun.star.sdbc.XClob], com.sun.star.bridges.jni_uno.JNI_proxy@7ee0f142 [oid=7f627403f070;gcc3:affine[0];fe0d3ee695f4212879a7aa3fb6ec575, type=com.sun.star.sdbc.XClob]]

Steps to Reproduce:
1. Create a Query to list fields that you want printed.
2. Design Report as you like. Include a CLOB field. Close Report Builder.
3. 'Open' the Report you just created.

Actual Results:  
The folowing is printed in place of the actual CLOB data in each record:

Any[Type[com.sun.star.sdbc.XClob], com.sun.star.bridges.jni_uno.JNI_proxy@7ee0f142 [oid=7f627403f070;gcc3:affine[0];fe0d3ee695f4212879a7aa3fb6ec575, type=com.sun.star.sdbc.XClob]]

Expected Results:
LO Base Report Writer prints contents of CLOB field.

Reproducible: Always

User Profile Reset: No

Additional Info:
Build ID: 60bfb1526849283ce2491346ed2aa51c465abfe6
CPU threads: 2; OS: Linux 4.10; UI render: default; VCL: gtk2; 
Locale: en-CA (en_CA.UTF-8); Calc: group

User-Agent: Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:54.0) Gecko/20100101 Firefox/54.0
Comment 1 JimJ_Wpg 2018-02-21 00:47:58 UTC
Created attachment 140026 [details]
Screenshot of LO Base Report Writer not displaying CLOB data
Comment 2 JimJ_Wpg 2018-02-21 00:50:25 UTC
Query looks like this:

SELECT "Company", "Notes" FROM "Business Contacts" AS "Business Contacts" ORDER BY "Company" ASC
Comment 3 JimJ_Wpg 2018-02-21 00:52:52 UTC
The only workaround for this problem is to use a spreadsheet and import the data there.
Comment 4 Alex Thurgood 2018-02-23 07:38:35 UTC
@Jim : 

Please provide detailed, step-by-step instructions on how to reproduce and provide a small test database file with which to experiment.

I'm assuming that you refer to a Firebird database, as CLOB support is/was inexistent or at the least inconsistent in the version of embedded hsqldb that we currently use.

Please also mention:

- how you access the database : is it an embedded db (Firebird, hsqldb) or an externally hosted db ?

- are you using any particular driver, and if so, which one ?

Setting to NEEDINFO pending requested information.
Comment 5 JimJ_Wpg 2018-02-23 19:30:10 UTC

I am using LO Base 6.0.1 on Linux. I am using Villeroy's "Split" HSQLDB Macro with the HSQLDB 2.4.0 jar and the latest SQLTool.

Attaching the .script and .properties files for examination.
Comment 6 JimJ_Wpg 2018-02-23 19:32:35 UTC
Created attachment 140098 [details]
mydb.script DDL of "Business Contacts" HSQLDB 2.4 file
Comment 7 JimJ_Wpg 2018-02-23 19:34:02 UTC
Created attachment 140099 [details]
mydb.properties file
Comment 8 Robert Großkopf 2018-02-26 19:36:44 UTC
Have tested with internal HSQLDB and LONGVARCHAR (Memo-fields) - works here:
Build-ID: f7f06a8f319e4b62f9bc5095aa112a65d2f3ac89
CPU-Threads: 4; BS: Linux 4.4; UI-Render: Standard; VCL: kde4; 
Gebietsschema: de-DE (de_DE.UTF-8); Calc: group

Don't know why using a field with many characters in a report, because there is no possibility to fill more than one page with the content of a field. So you will get a red arrow, which shows the whole content couldn't be shown.
Comment 9 Alex Thurgood 2018-04-13 08:38:20 UTC
Can't test this as using an external hsqldb.jar screws up my LO installation on Mac.
Comment 10 Xisco Faulí 2018-05-31 11:59:23 UTC
@Drew, Do you reproduce this ?
Comment 11 Drew Jensen 2018-05-31 14:30:55 UTC
Created attachment 142447 [details]
same error firebird engine

using a field defined as BLOB SUB_TYPE 1 (CLOB) in odb files with both embedded and file firebird type and getting the exact error in report builder as reported. 

Here is the embedded firebird test file with one table and report.
Comment 12 Drew Jensen 2018-05-31 15:28:34 UTC
Created attachment 142449 [details]
example firebird odb with workaround

There is a workaround if you accept up to 32K of text as a suitable solution, for today at least, and that is to use a view with the cast function.

This new test file includes a Query and View, both identical SQL:
SELECT "ID", "First_Name", "Last_Name", cast( left( "contacts"."Notes", 1024) as  CHAR(1024) ) as "Notes" FROM "Business Contacts" "contacts"

Likely a separate issue is found by executing the Query look at Notes content, then look at the form and report which use that query, empty content for Notes, while the form and report using the view gets the expected results.
Comment 13 Drew Jensen 2018-12-20 14:04:46 UTC
*** Bug 122167 has been marked as a duplicate of this bug. ***
Comment 14 Drew Jensen 2018-12-20 15:44:59 UTC
(In reply to Drew Jensen from comment #13)
> *** Bug 122167 has been marked as a duplicate of this bug. ***

FYI - after testing that issue, using the test files with a HSQL embedded ODB and a MEMO field, which works fine with the 6.2Beta build, changed that other issue to new as it is specific to FB and Mailmerge.  Leaving this link in the see also property however.
Comment 15 Drew Jensen 2019-01-01 15:21:33 UTC
*** Bug 122400 has been marked as a duplicate of this bug. ***
Comment 16 trowelandmattock 2020-05-11 12:26:10 UTC

This is sadly still a problem on Win7 and Win10 /Base 6.4.3 / Firebird & GUI methods on empty tables: - CLOB can only be created via the wizard...

    CLOB will not save in design-table view ...

        And any changes to any other field in a table with CLOB / BLOB will result in all CLOB / BLOB being turned to BLOB...!!!(wtf)

        It is not possible to alter a CLOB to another type or vice-versa, and same is true for tables copied in from HSQL2.x project:

"firebird_sdbc error: *unsuccessful metadata update " ...sadness:(
Comment 17 Robert Großkopf 2020-05-11 12:52:59 UTC
https://bugs.documentfoundation.org/show_bug.cgi?id=115893#c16 has nothing to do with this bug. This bug is about CLOB in Report-Builder. Report a new bug for your special problem.
Comment 18 trowelandmattock 2020-08-15 00:48:12 UTC
There appears to be no fix for this yet, making design/modification of databases with data difficult for CLOB types, and especially difficult for migration to firebird with existing database structures.

Here is my work-around for changing a field to CLOB in existing table with data:

1: create the new intended CLOB field as VarChar
2: copy the table and data to a spreadsheet
3:copy the spreadsheet
4: paste back into base as new table, and use the dialog to change the VarChar to CLOB as needed - no other method appears to work :/

The first attempt may not get all the data copied, AND might rearrange some columns (!!!)

Fix this by creating new firebird DB (for safety) - then copy original table there, making sure to use initial dialog to set CLOB - proceed through the many dialog boxes... (errors and all!) - now delete all the spurious data just leaving the definitions - note if any column order has changed - now copy the original data to a spreadsheet, and modify to match any changed columns in new version - now copy spreadsheet and append to the new table - check it again as the first row seems to get buggy, but all the other data should be there ...sigh.

Love You Libre! (firebird don't us down now please:)