Postgresql/JDBC fails with or without UTF8 encoding

Question

Using Java 8 and a Postgres 10 Server, and the v 42.2.16 Postgresql Driver, I have a basic JDBC query function:

public List<Map<String, Object>> Query(String sql) throws Exception {
    Connection con = null ;
    PreparedStatement pstmt;
    List<Map<String, Object>> resultSetToList = null;
    try {
        
        con = DriverManager.getConnection(ConnectionString, Properties);
        pstmt = con.prepareStatement( sql );
        pstmt.execute();
        
        ResultSet resultSet = pstmt.getResultSet();
        resultSetToList = resultSetToList(resultSet);

        pstmt.close();

    } catch(Exception e){
        throw e;
    }
        finally {
    
        if (con != null)
            con.close();
    }
    return resultSetToList;
}

I execute a query like this:

Query("SELECT * FROM bi_functions");

But it throws an Exception

org.postgresql.util.PSQLException: ERROR: invalid byte sequence for encoding "UTF8": 0xa3

The database (provided by 3rd party) has encoding SQL_ASCII. 0xA3 is the 'British Pound' character.

So I change the query to:

SET LOCAL CLIENT_ENCODING TO 'SQL_ASCII'; SELECT * FROM bi_functions;

It fails with:

org.postgresql.util.PSQLException: The server's client_encoding parameter was changed to SQL_ASCII. The JDBC driver requires client_encoding to be UTF8 for correct operation.

Is there a way using plain JDBC to circumvent this error?

No, you cannot do that. The interesting question is: what is the server encoding, and what bytes are stored in the database? — Laurenz Albe
DB (provided by 3rd party) has encoding SQL_ASCII. 0xA3 is the 'British Pound' character. Are you saying I can't read this DB via JDBC? — Black

Laurenz Albe Laurenz Albe · Accepted Answer · 2020-09-21T06:12:04

Java uses a Unicode encoding internally, so you cannot use a client_encoding different from UTF8 with the JDBC driver.

You should figure out the actual encoding of the database (probably one of the ISO 8859 or a Windows encoding). Then create a database with that encoding and dump the original database and load your dumped database into it.

Otherwise you won't be able to use this database with JDBC.

Postgresql/JDBC fails with or without UTF8 encoding

1 Answers