Description: 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: Version: 6.0.1.1 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
Created attachment 140026 [details] Screenshot of LO Base Report Writer not displaying CLOB data
Query looks like this: SELECT "Company", "Notes" FROM "Business Contacts" AS "Business Contacts" ORDER BY "Company" ASC
The only workaround for this problem is to use a spreadsheet and import the data there.
@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.
, 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.
Created attachment 140098 [details] mydb.script DDL of "Business Contacts" HSQLDB 2.4 file
Created attachment 140099 [details] mydb.properties file
Have tested with internal HSQLDB and LONGVARCHAR (Memo-fields) - works here: Version: 6.0.2.1 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.
Can't test this as using an external hsqldb.jar screws up my LO installation on Mac.
@Drew, Do you reproduce this ?
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.
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.
*** Bug 122167 has been marked as a duplicate of this bug. ***
(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.
*** Bug 122400 has been marked as a duplicate of this bug. ***
https://ask.libreoffice.org/en/question/193710/how-create-clob-field-in-design-view-in-base-firebird/ 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:(
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.
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 :/ NB// 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:)
Description: LibreOffice 6.4 and prior versions - Base Report Writer displays the following for each CLOB 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/View/Table with a CLOB field 2. Create associated Report (inc the clob field). 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 Version: 6.4.7.2 (x64) Build ID: 639b8ac485750d5696d7590a72ef1b496725cfb5 CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win; Locale: en-GB (en_GB); UI-Language: en-GB Calc: CL
EDIT: actual error for me is " Any[Type[com.sun.star.sdbc.XClob], com.sun.star.bridges.jni_uno.JNI_proxy@6c7ed3b5 [oid=4bde9030;mscx[0];2496293a89a44266bb876e15a5bad382, type=com.sun.star.sdbc.XClob]] " Love you Libre!
(... and to confirm, this is still the behavior in LO 7.1.3.2 )