| Summary: | EDITING: Embedded Firebird - List doesn't work in queries, works in external Firebird | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | robert |
| Component: | Database | Assignee: | Not Assigned <libreoffice-bugs> |
| Status: | NEW --- | QA Contact: | |
| Severity: | normal | ||
| Priority: | medium | CC: | andrzej, iplaw67, lionel |
| Version: | 4.2.0.0.alpha0+ Master | ||
| Hardware: | x86-64 (AMD64) | ||
| OS: | Linux (All) | ||
| Whiteboard: | |||
| i915 platform: | i915 features: | ||
| Attachments: | Start the query of the database - shows only <object>, but works with external Firebird. | ||
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 |
Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.
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.