Unanswered: How to have ant's <sql> task insert special chars appropriately?
What I'm trying to do is to insert some data from a sql file into a postgres DB by calling the <sql> ant task. My problem is that I can't get special characters (even if they can be represented by the standard ASCII charset, such as ä, ö, ü, é, etc.) to be inserted correctly.
When I check the DB with a DB browser such as Squirrel or pgAdmin, after running the <sql> task they can only see question marks where these characters are supposed to be. So does the actual web application using the DB. (The web app can store and read such special characters seamlessly, though. It's also OK if I enter some of them by hand with the DB browsers mentioned above.)
This all suggests that the problem is with the way I call the <sql> task. I've tried several versions: inserting from a sql file with iso-8859-1 encoding, then from one with utf-8 encoding. (In pgAdmin I can see the default encoding of the DB is utf-8.)
(This works perfectly as long as there are no special characters.)
I tried changing the "encoding" attribute above to UTF-8, adding "SET client_encoding = 'UTF8';" to the UTF-8 file, then to the iso-8859-1 file; "SET client_encoding = 'LATIN1';" to the iso-8859-1 file (this latter in addition produced the error "The server's client_encoding parameter was changed to LATIN1. The JDBC driver requires client_encoding to be UNICODE for correct operation.")
Does anyone have any idea how to get those special characters loaded into the DB from a pre-written sql file (possibly with the ant <sql> task)?
In Postgresql, the database encoding is defined when the database is created. You would have to recreate the database in order to change the way it stores string data.
That being said, take a look at the encode string function.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert
I didn't have any problem with the encoding of the database (UTF-8), nor did I want to change it.
The problem was actually only that during the build I copied over the sql files and applied a <filterchain> to them -- where I didn't know I had to specify the encoding explicitly in order not to have the characters get messed up. (But I wasn't looking at the copied file, I was looking at the original one.)
After correcting this, everything is all right, so there was no problem with either the <sql> task or PostGres in the first place.