Description: Note well. Direct entry of an emoji into a table in LibreOffice via the Winkey period dialog, or by simply pasting it in, works. But, trying to enter the data via the BASIC SQL Statement command fails. Example code below. Similarly, reading a record from a SQLite table that contains an emoji into a recordset, and then writing from the recordset to another table fails. Using an SQL substring statement to copy a portion of the record from one table to another works. I therefore there is something in the LibreOffice functions to write to a database that is not Unicode compliant. Steps to Reproduce: 1.See attached document with sample code and screen shots and results and tracelogs 2. 3. Actual Results: It maps the emoji to either question marks, or to multiple characters with incorrect shapes. It appears to be interpreting a multi-byte emoji character as two single-byte characters Expected Results: Emoji entered into the database Reproducible: Always User Profile Reset: Yes Additional Info: Version: 6.3.4.2 (x64) Build ID: 60da17e045e08f1793c57c00ba83cdfce946d0aa CPU threads: 4; OS: Windows 10.0; UI render: GL; VCL: win; Locale: en-US (en_US); UI-Language: en-US Calc: threaded
Created attachment 158510 [details] detailed steps to reproduce Several pages of screenshots showing how to reproduce the problem, includes sample test code listing.
Created attachment 158511 [details] sample SQLITE Db containing a few records for the test code
Created attachment 158512 [details] Sample BASE db with code to demonstrate the bug.
Created attachment 158513 [details] SQL trace log from function that produces the error
Created attachment 158514 [details] SQL trace log from direct update, which works correctly
Created attachment 158528 [details] The correct TestEmoji for use with testing. Please discard the other one.
Comment on attachment 158512 [details] Sample BASE db with code to demonstrate the bug. Sorry, uploaded the wrong version. I've uploaded a correct version in another attahment with the irrelevent junk stripped out of this one.
I have tested only this: Pasted the emoji to an internal HSQLDB. No problem occured. Could also refresh the table - no problem. There are so many things reported here, which could be a part of the bug. Could you please test your BASIC macro with the internal HSQLDB?
Created attachment 158559 [details] TestDB for embedded Firebird I was requested to check the test macro in HSQL format. While i was at it, I tested it in embedded Firebird format. This is the test db in embedded Firebird format. The macro works as expected there, which shows the problem is apparently isolated to cases using the SQLite back end.
Created attachment 158560 [details] test database in HSQL format I was requested to check the test macro in HSQL format. This is the test db in embedded HSQL format. While i was at it, I tested it in embedded Firebird format, and uploaed that in a separate attachment. The macro works as expected with embedded HSQL or embedded Firebird, which shows the problem is apparently isolated to cases using the SQLite back end.
I was requested to test the test macro in HSQL format. I uploaded two test databases, one in HSQL and one in Firebird. The macro works as expected in both those formats, but fails for SQLite. Evidently, the processing of an SQL string as shown below is done differently based on the attached database. Whis is not really a surprise. Here's the statement that succeeds in embedded databases, but fails with SQLite Dim oStatement As Object oStatement = ThisComponent.CurrentController.ActiveConnection.createStatement() sql = "INSERT INTO TestEmoji (author, comment) VALUES ('test author', '❤❤SWIFTIE❤❤')" result = oStatement.execute(sql)
While reading your description, pasting of data into a table works, but "When I did Data / Refresh in LibreOffice I saw the same thing: " (2 ?? instead of the emoji). So it seems it has nothing to do with the Basic-macro. Then I tested the same with a MariaDB, connected with the direct driver, connected with ODBC and connected with JDBC to Base. All these drivers will give the same result. The data will be saved and could be seen well after refreshing the table. I will have a look at SQLITE now.
Have tested the same now in SQLITE. Connected to a SQLITE-database trough ODBC. Opened a table and added an emoji. Saved the row. Refreshed the row. Emoji has been saved. The same result I got when executing the Basic-Macro. So I couldn't see any buggy behavior here. Tested under OpenSUSE 15.1 64bit rpm Linux, LO 6.4.2.1, also LO 6.3.5.2 SQLITE 3.28.0 sqliteodbc 0.9995
What JDBC driver are you using to connect to BASE? I'll give that a try. I saw correct behavior using JDBC driver from Taro L. Saito, availble here https://github.com/xerial/sqlite-jdbc. However, this driver produced immediate cursor problems the moment I opened a table in the UI, a problem noticed by Ratslinger on ask libreoffice.org. So I had to abandon the JDBC driver.
(In reply to Max Fritzler from comment #14) > What JDBC driver are you using to connect to BASE? I'll give that a try. > > I saw correct behavior using JDBC driver from Taro L. Saito, availble here > https://github.com/xerial/sqlite-jdbc. However, this driver produced > immediate cursor problems the moment I opened a table in the UI, a problem > noticed by Ratslinger on ask libreoffice.org. So I had to abandon the JDBC > driver. I tested MariaDB with this connections. SQLITE-connection with JDBC doesn't work (SQLite only supports TYPE_FORWARD_ONLY Cursors). Seems to me we are looking for a special Windows-bug here. I will change the titel to set this to SQLite and ODBC
I just confirmed this on a second Windows machine, with a fresh install of LibreOffice 6.4, the current released version.
Hello Max Fritzler, Could you please try to reproduce it with the latest version of LibreOffice from https://www.libreoffice.org/download/libreoffice-fresh/ ? I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' if the bug is still present in the latest version.
As requested by Xisco Faulí, I upgraded to 7.0.3.1 and reran the test program. The bug is still present. The output of the test is still as shown in the above comments. Please note. Pretty much anyone can run this test. Just download the attachments and run the TestEmoji macro. The macro tells you which table to look in for the output, and the rows in the table tell you what to expect.