[fpc-pascal] Problems with Postgresql Character Encoding

[Mark Morgan Lloyd]

Andrew Brunner wrote:
> My cloud file/folder sync client desktop app builds and maintains a 
> manifest of all files and folders from a specified starting point. I use 
> this manifest to synchronize disk entries across devices.  It uses HTTP 
> requests and XML to carry out all transactions.  Meaning there is no 
> TSQLConnection.  The problem is that the HTTP server receives the data 
> as XML and refuses to post changes.  If data (in updates or inserts) 
> contains non-supported UTF8 characters postgresql rejects with character 
> encoding notices.
> 
> I've tried with using AnsiToUTF8 and UTF8toAnsi.  That works for email 
> messages.  However, since sync code has to scan tens of thousands of 
> disk files/folders I don't think it's good for battery and memory 
> consumption for a background app to consume cpu resources in this case 
> going between 2 forms of the same file/folder name.
> 
> I need to be able to take advantage of Postgresql's client encoding 
> method.  I know postgresql allows me to tell the server that the 
> connection is presently character set "LATIN1".  And presumably 
> postgresql server will convert what it needs to my database encoding of 
> UTF8.
> 
> <snip>
> SystemP^.Connection.Charset:='LATIN1';
> 
> This does nothing for me when Connection is a postgresql connection.
> 
> Anyone have any experience with postgresql character sets / encoding???

No, but I'm interested since this is something that I anticipate having 
to do.

You might need to issue an explicit instruction to the PostgreSQL server 
telling it to assume that the current session is encoded as /whatever/. 
If you have to do that then you might find yourself having to use the 
PQConnection's (or whatever the correct name is) low-level handle, since 
working from memory the .Exec method uses a temporary handle so any 
state change isn't retained. I had to delve into this when getting 
listen/notify running reliably.

-- 
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]