Foreign/accented characters in sql query
- by FromCanada
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.):
