Created attachment 87567 [details] Start the query of the database - shows only <object>, but works with external Firebird. In the external version of Firebird exists a function called LIST. It lists all values of one field together. When you group another fields it works like "GROUP CONCAT" in MySQL. This functions works in the GUI, not only in direct SQL-mode. When I start this function in the embedded Firebird it only recognizes an <OBJECT> inside the field. Also when I change to direct SQL-mode it doesn't work. Also direct input in Tools → SQL doesn't show anything.
Robert, If I open your file in GUI Query Design mode, I see the following as the Field Name : LIST( [Vorname], ' and ' ) shouldn't the function appear in the Function are of the UI ? Alex
LIST doesn't appear in the dropdown list of supported functions, so I'm guessing that it is one that hasn't been implemented, or is a missing feature ? In which case, this would be a RFE ? Alex
The SQL statement that I see in your file : SELECT LIST( "Vorname", ' and ' ) FROM "Name_VarcharID" Alex
(In reply to comment #3) > The SQL statement that I see in your file : > > SELECT LIST( "Vorname", ' and ' ) FROM "Name_VarcharID" > Hi Alex, there are two new functions in Firebird, which I missed in HSQLDB: DATEADD and LIST. In the description of Firebird I found LIST ([ALL | DISTINCT] expression [, separator]) The result type is a BLOB - could be this problem could be solved together with Bug70664. When I put SELECT LIST( "Vorname", ' and ' ) FROM "Name_VarcharID" in a query of the external Firebird database it shows in Base all Values of "Vorname" in one field - looks like 'Robert and Alex and Eva and Adam' In MySQL/MariaDB this function is called "GROUP_CONCAT()". When this function appears as a choice in the GUI it could be that users think it will work with all databases - especially with the internal databases of LO. Don't know if it should appear there anyway.
The question whether the GUI shows the choice or not is independent on whether it should work when typed directly. If you want to discuss that enhancement, please fork it into another bug report. For clarity, *this* bug is (to the best of my understanding) about the result of "LIST()" function being recognised as BLOB with internal firedb, but as varchar (string) using external firedb. Robert: for "external" firedb, do you use the native connector or something like ODBC or JDBC? Robert: try adding the explicit cast to varchar of the result; something like: SELECT CAST( LIST("Vorname", ' and ') AS VARCHAR) FROM "Name_VarcharID"
(In reply to comment #5) > For clarity, *this* bug is (to the best of my understanding) about the > result of "LIST()" function being recognised as BLOB with internal firedb, > but as varchar (string) using external firedb. When I read the description of Firebird 2.5, I see on p. 166 Result type: BLOB The result is a text BLOB, except when expression is a BLOB of another subtype. Firebird has different sub_types of BLOB. The sub_type of LIST is a text-BLOB > Robert: for "external" firedb, do you use the native connector or something > like ODBC or JDBC? I have tried it with JDBC and ODBC. Both work as expected. > > Robert: try adding the explicit cast to varchar of the result; something > like: > SELECT CAST( LIST("Vorname", ' and ') AS VARCHAR) FROM "Name_VarcharID" Tried this (it's a function implemented in Firebird), but it doesn't work. Gives a firebird_sdbc error.
(In reply to comment #6) > (In reply to comment #5) >> For clarity, *this* bug is (to the best of my understanding) about the >> result of "LIST()" function being recognised as BLOB with internal firedb, >> but as varchar (string) using external firedb. > When I read the description of Firebird 2.5, I see on p. 166 > Result type: BLOB > The result is a text BLOB, except when expression is a BLOB of another > subtype. > Firebird has different sub_types of BLOB. The sub_type of LIST is a text-BLOB I see. Then maybe the subtype has to be mapped to css::sdbc::DataType::CLOB, but currently is not (is mapped to BLOB instead). Andrzej? Does that ring a bell? >> Robert: for "external" firedb, do you use the native connector or something >> like ODBC or JDBC? > I have tried it with JDBC and ODBC. Both work as expected. Probably the JDBC / ODBC drivers do the above mapping.
Adding self to CC if not already on
Created attachment 128678 [details] Version 3.0 of original file On pc Debian x86-64 with master sources updated today, I could reproduce this. I attached V3 version of the file On console, I noticed these: warn:connectivity.firebird:16904:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1485: Not yet implemented warn:legacy.osl:16904:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1489: Not implemented yet! warn:connectivity.firebird:16904:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1485: Not yet implemented warn:legacy.osl:16904:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1489: Not implemented yet! warn:connectivity.firebird:16904:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1485: Not yet implemented warn:legacy.osl:16904:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1489: Not implemented yet! warn:connectivity.firebird:16904:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1485: Not yet implemented warn:legacy.osl:16904:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1489: Not implemented yet!
I submitted https://gerrit.libreoffice.org/#/c/30789/ so LO uses subtype to distinguish BLOB/CLOB. Now the field is empty (instead of display "OBJECT"). On console, I noticed this: warn:connectivity.firebird:17270:1:connectivity/source/drivers/firebird/ResultSetMetaData.cxx:177: Column 'LIST' not found in database
Julien Nabet committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=a2c6112a4753347070608480be21066b6dab0bc7 Related tdf#70433: Use subtype to distinguish BLOB/CLOB for Firebird It will be available in 5.3.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
I don't know how to fix this, it's just a related patch. So let's remove target.
Created attachment 129663 [details] Firebird 3.0 of original file I'm afraid the previous firebird 3.0 version of attachment was accidentally an attachment of another bug.
As I see, the result is an empty Clob right now, because the getClob() function in Resultset.cxx returns always a nullpointer. (not implemented). Before Julien's patch, the type received from getColumnTypeFromFBType (in Util.cxx) was the type of a blob, instead of clob. That's why the result was "OBJECT" before, instead of and empty cell. This experiment makes me assume, that the UI could display a non-empty clob (or text-Blob in case of Firebird, whatever) properly. So we have two opportunities: - Implement the XClob interface just as XBlob is implemented, and use it in getClob(): https://www.openoffice.org/api/docs/common/ref/com/sun/star/sdbc/XClob.html - Use list with an extra CAST(): SELECT CAST (LIST( "Vorname", ' and ' ) as VARCHAR(32000) ) FROM "Name_VarcharID" That should work. At least it works for me.
(In reply to Tamas Bunth from comment #14) > - Use list with an extra CAST(): > > SELECT CAST (LIST( "Vorname", ' and ' ) as VARCHAR(32000) ) FROM > "Name_VarcharID" > > That should work. At least it works for me. Thanks Tamas! This is a good workaround for me. Works also here with LO 5.4.0.0.alpha0+ and with LO 5.2.4.1 (Firebird 2.5). We shouldn't set this to WORKSFORME, but could wait a little bit with this bug.
Tamás Bunth committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=882db8709f4ebc9f170cf743f39434d791b34b14 tdf#70433 tdf#104734 implement sdbc XClob It will be available in 5.4.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
(In reply to Commit Notification from comment #16) > Tamás Bunth committed a patch related to this issue. > It has been pushed to "master": > > http://cgit.freedesktop.org/libreoffice/core/commit/ > ?id=882db8709f4ebc9f170cf743f39434d791b34b14 > > tdf#70433 tdf#104734 implement sdbc XClob > > It will be available in 5.4.0. Patch works well in LO 5.4.0. Thanks Tamás! Could it be also included to LO 5.3.0?
(In reply to robert from comment #17) > Could it be also included to LO 5.3.0? I think so. But I'm not familiar with the aspects of including code to previous versions. It doesn't depend on code created after LO 5.3.0.
I cherry picked the patch on 5.3 branch, it's on review here: https://gerrit.libreoffice.org/#/c/33364/
Tamás Bunth committed a patch related to this issue. It has been pushed to "libreoffice-5-3": http://cgit.freedesktop.org/libreoffice/core/commit/?id=c6d9e749b72c18cb71f7ffc25bb3474ee5c355b4&h=libreoffice-5-3 tdf#70433 tdf#104734 implement sdbc XClob It will be available in 5.3.1. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Let's put this one to FIXED now.