The journey continues: PostgreSQL and Unicode in VAST 2024

I wasn’t planning on writing a second installment on this little journey this soon. But instead of going back to my normal work I wanted to go the extra mile and see if and how I can add a UnicodeString to the Database from VAST.

Of course I found myself struggling with writing a UncodeString. Well, actually I seem to be struggling in creating a UnicodeString with UTF-8 in a workspace. I seem to be overseeing the obvious, but somehow whatever i try to create and write is ending up as ugly garbage in the database….

So let’s start with my workspace (using VAST 13 on Windows 10 x64):

| conn logonSpec conSpec newRow table |

conSpec := AbtDatabaseConnectionSpec
     forDbmClass: #AbtPostgreSQLDatabaseManager
	 databaseName: 'testdb'.


logonSpec := AbtDatabaseLogonSpec id: 'postgres' password: 'pw' server: 'server-ip:5432'.
conn := conSpec connectUsingAlias: 'TestConSpec' 
logonSpec: logonSpec.
conn enableUnicode.


table := conn openTableNamed: 'text'.
newRow := table emptyRow.
newRow at: #id put: 5.
newRow at: #text put: ('Zürich ist übrigens nicht häßlich sondern sehr schön'  asUnicodeString ).
table addRow: newRow.

conn disconnect.

| conn logonSpec conSpec qSpec result resultCollection newRow table |
resultCollection := OrderedCollection new.

I tried a few variants of creating an appropriate String for insertion, but all I end up seeing in psql on the server is this (please not that row #1 was inserted on that linux box using psql, the others were inserted trying different ways of creating a UnicodeString).

The problem is: I am not even sure I ever created a valid utf-8 String, because they also show up broken in a Workspace. This all feels like trying to build a card house on a layer of marbles lying on the trunk of a car at 120 mph. What is right, what is wrong?

Inspecting

‘Zürich ist übrigens nicht häßlich sondern sehr schön’  asUnicodeString

yields

‘Zürich ist übrigens nicht häßlich sondern sehr schön’

So I guess the problem is not the PostgreSQL driver. The current codepage in my image is 1252. Inspecting the parameter to UnicodeString class>>utf8: looks like this:

‘Zürich ist übrigens nicht häßlich sondern sehr schön’

and I guess this is the main problem here. The conversion from codepage 1252 to utf-8 before creating a UnicodeString fails already (I guess…).

Then, there is this interesting question: the Workspace is configured to be UTF-8 (the menu “Encoding” has a checkmark at the “UTF-8′ entry, the other 3 options are unchecked.

So is my code wrong or is my Workspace configured incorrectly?

… ah, yes, that’s it: I need to change the Workspace setting to be ANSI, which converts the String to a broken representation, so I have to correct the String back to contain Umlauts. Inspecting the result now works….

So now I try to insert ths String to the Database.

Et voila:

This of course brings up an interesting bundle of question I need to solve.
First, I need to find a combination of Workspace/Inspector setup that allows for handling Strings of different origin: the imagr or tha database (and later: Seaside or API hanlders).

Another question is: what about text constants in my source code that are now in the manager library. We do a lot of nextPutAll: with German Strings that contain Umlauts. Since my images on Windows and Linux always were configured to use 1252 or iso-8859, these Strings will of course be in that encoding in the manager library.

I don’t want to sound too pessimistic, but I am afraid I am up to a lot of even more fun with Umlauts in the months to come (Marten, Michal, Peter: I hear you laughing out loud!)…