# How to set character encoding on strings in SQL statements

When I write a string to a table in BASE using BASIC, the encoding is lost. How do I explicitly set it? Consider below. The first attempt fails. The second succeeds, however, it uses SQL subselect so that BASE doesn't actually touch the string with the non-ASCII encoding. But the problem with using that solution is that if you need to manipulate the source string from the source table, say by making substitutions, breaking it into smaller pieces, whatever, that rapidly becomes so complex that trying to it in pure SQL is very difficult -- beyond me. Is there a way for me to specify the encoding on the SQL in the createStatement command below? I'm using LO 6.3 on Windows 10, but have seen the same thing in LO 6.4 on Windows 10. This is a small project for an academic linguist, hence the interest in these characters. I have a Linux machine so could test there, but need to deliver on Windows 10. Any ideas?

  sub TestEmojiHandling
dim sql as string, emoji as string, result as string
emoji = "❤❤SWIFTIE❤❤"
result =  join(split(emoji))    ' Result still shows the emoji

' Write via native createStatement command.  This changes the emoji into question marks, e.g. "??SWIFTIE??:
REM make sure your connected to the database
if IsNull(ThisComponent.CurrentController.ActiveConnection) then
ThisComponent.CurrentController.connect
endif

REM execute!
Dim oStatement As Object
oStatement = ThisComponent.CurrentController.ActiveConnection.createStatement()
sql = " INSERT INTO exports (author, comment) VALUES('via native BASIC', '❤❤SWIFTIE❤❤')"

result = oStatement.execute(sql)

' Now via subselect.  This WORKS!  It keeps the emoji.
' In this case, the string containing the emoji is only handled by the back-end database, in my case SQLite3
sql = "insert into 'exports' (author, comment) select 'via subSelect' AS author, comment from comments where comment_id = 'UgwvzoLx-WeAdw7q_PR4AaABAg'"
DoCmd.RunSQL(sql)
end sub

edit retag close merge delete

Sort by » oldest newest most voted

Hello,

Do not have SQLite3 installed but did test using Firebird embedded and PostgreSQL with a native connector.

Experienced no problem with the code:

Table and field names double quoted because of DB requirement. Also used executeUpdate but tested with just execute and there was no difference.

Your problem may lie within the connector used. Have seen various problems in the past where one connector type works but another doesn't.

Unfortunately this is about all I can offer. There are not many SQLite questions here so not certain what other responses you may get.

Edit 2020-03-01:

Had some time so I loaded SQLite3 and having only used ODBC previously used JDBC from here -> sqlite-jdbc. I used v3.30.1

Ran the macro from a Base file with no error but when trying to view table got some error about forward error (vaguely familiar). In stead of trying to mess with it, just decided to look at it through the terminal. All looked fine:

Did this on Ubuntu 18.04 Mate with LO v6.4.0.3

more

Just tested with Firebird server using JDBC connector and same result - no problem.

( 2020-03-01 00:54:34 +0100 )edit

Many thanks Ratslinger. That helps. It is late in my timezone and I cannot work on this tomorrow. I will see if I can find a different ODBC connector to SQLITE and if so, report back the results. It strikes me that I should test under Linux because the driver will be different there.

( 2020-03-01 04:41:39 +0100 )edit

OK went the final step with SQLite3 & Base- JDBC is a problem for me so used ODBC (libsqliteodbc through Synaptic). Results (could now see in table view) were the same as all other tests posted - all OK!

( 2020-03-02 07:12:46 +0100 )edit

I confirm Ratslinger's results. Many thanks!!
I eventually installed a JDBC driver, and then my test code worked fine. I used Taro L. Saito's JDBC driver sqlite-jdbc-3.30.1.jar found at https://bitbucket.org/xerial/sqlite-j..., with very good install instructions found at: https://sites.google.com/site/prahlad....

I researched the open source ODBC driver I was using: http://ch-werner.de/sqliteodbc/. The author noted here: https://stackoverflow.com/questions/2... that the OEMCP Translation has to be turned on in the ODBC configuration tools. Several attempts with that produced no difference, however.

BOTTOM LINE: Ratslinger is correct, it was a driver problem, not a LibreOffice problem, and was fixed by switching to a JDBC driver. Again, many thanks!

( 2020-03-03 14:49:38 +0100 )edit

Have done many JDBC connections to various DB's but this one keeps stopping with SQLite only supports TYPE_FORWARD_ONLY cursors' when select table view or a form. Have tried many different settings in advanced but this has been the same problem for me for years. This is why I stick with ODBC for SQLite connections. Even tried different Java installs.

Found many posts with the same problem including LO tdf#93094

So if you got this to work it would be nice to know how.

( 2020-03-03 20:11:47 +0100 )edit

I was just going to post to ask if you had gotten it. No, I haven't gotten it to work. I looked at many posts, and it seems like the driver maker considers this not a bug in the driver but rather an inherent limitation of sqlite. Christian Werner also has a small jdbc driver. http://www.ch-werner.de/javasqlite/ I'm going to try that, if I can figure out how to install it, and will report back in a day or two. In the meantime, I coded around my original problem as much as I can. I have designed, in my mind, a way to do the processing that was generating the unwanted transformation of unicode to question mark characters by substituting a bunch of find and replace operations in Writer, which is the ultimate destination for selected parts of this data. Thanks for responding and have ...(more)

( 2020-03-03 20:24:49 +0100 )edit

Please note that I don't have a problem with ODBC - just JDBC.

( 2020-03-03 20:49:37 +0100 )edit

HMMM. Further research proves that the ODBC driver does not seem to be the problem. See bug 131238 https://bugs.documentfoundation.org/s... on the LibreOffice bugzilla page. You can do a direct edit of the table row in LibreOffice BASE and press the win key plus period to call a grid of emoji. Select one and it will insert it into the field. Move off the field and it will save it to the database. This is with the choosing the utf-8 for character set (Edit / Database / Connection, third screen Character set listbox). We are only seeing the problem in Windows, and only with SQLite as the back end, not with HSQL or Firebird.

( 2020-03-10 19:51:54 +0100 )edit

I added code to my test routine:

        dim rst as Object
set rst = CurrentDB.OpenRecordset("select * from TestEmoji")
with rst
.Fields("author").Value = "Adding from a recordset."
.Fields("comment").Value = emoji
.Update
.Fields("author").Value = "Adding from a recordset."
.Fields("comment").Value = emoji
.Update
end with
`

This worked on Windows, leaving us with only the one failure method documented above.

Next, I got my Ubuntu partition going on one of my windows machines, and let it update to Ubuntu 18.04.4 LTS. It has LibreOffice 6.3 installed on it. I ran the test code and ALL methods of update worked. I then updated LibreOffice to 6.4 and again observed that ALL methods of update worked. The problem does not appear on my Linux partition. I have solved my problem on the application by using the method above to do the updates I needed to do ...(more)

( 2020-03-24 21:30:35 +0100 )edit