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
