Foreign/accented characters in sql query

Question

I'm using Java and Spring's JdbcTemplate class to build an SQL query in Java that queries a Postgres database. However, I'm having trouble executing queries that contain foreign/accented characters.

For example the (trimmed) code:

JdbcTemplate select = new JdbcTemplate( postgresDatabase );

String query = "SELECT id FROM province WHERE name = 'Ontario';";

Integer id = select.queryForObject( query, Integer.class );

will retrieve the province id, but if instead I did name = 'Québec' then the query fails to return any results (this value is in the database so the problem isn't that it's missing).

I believe the source of the problem is that the database I am required to use has the default client encoding set to SQL_ASCII, which according to this prevents automatic character set conversions. (The Java environments encoding is set to 'UTF-8' while I'm told the database uses 'LATIN1' / 'ISO-8859-1')

I was able to manually indicate the encoding when the resultSets contained values with foreign characters as a solution to a previous problem with a similar nature.

Ex:

String provinceName = new String ( resultSet.getBytes( "name" ), "ISO-8859-1" );

But now that the foreign characters are part of the query itself this approach hasn't been successful. (I suppose since the query has to be saved in a String before being executed anyway, breaking it down into bytes and then changing the encoding only muddles the characters further.)

Is there a way around this without having to change the properties of the database or reconstruct it?

PostScript: I found this function on StackOverflow when making up a title, it didn't seem to work (I might not have used it correctly, but even if it did work it doesn't seem like it could be the best solution.):

Edit: I have selected my own answer for this, since it will be what I am using for now; however, as mentioned in a comment below I would be happy to look at other suggestions that may be better, for as long as I have access to the database.

Answer 1

In fact if your database is "SQL_ASCII" encoded, it basically understands ASCII and nothing else. It means that the word "Québec" has been stored "as provided", meaning "as provided as a set of bytes, according to the encoding used by the tool which processed the insert or update sql order against the database at this moment". Consequently, when you try to select such values, you must use the same encoding, but you have to know beforehand which one it is.

This first matter handled, you then need a way to express that your request should use this encoding.

Let's say that it has been stored with ISO-8859-1 encoding.

I'm not sure that it might work, but I would try something like that :

String myReq = "SELECT id FROM province WHERE name = 'Québec';";
byte[] iso8859sequence = myReq.getBytes("ISO-8859-1");
String myReqAscii = new String(iso8859sequence, "US-ASCII");
Integer id = select.queryForObject( query, Integer.class );

Answer 2

Hmm okay, after slugging through the postgreSQL documentation, I found a solution in the String Functions and Operators section.

I used the convert(string bytea, src_encoding name, dest_encoding name) function and managed to get the province id for Québec.

Ex.

String query = "SELECT id FROM province WHERE name = convert( 'Québec', 'UTF-8', 'ISO-8859-1' );"; 

Answer 3

If you are connecting from Java with encoding UTF-8 and the database is ISO-8859-1, then you should run this SQL command just after you initially connect to the DB:

SET client_encoding = 'UTF8';

PostgreSQL will then interpret all input as UTF-8 and then convert it to ISO-8859-1 on the server side. You shouldn't have to do anything other than that.