SQL_ASCII and Java remote access to PostgreSQL

Question

Hallo,

I tried to send the request to PostgreSQL 8.x that has character encoding SQL_ASCII. Sadly I can not convert it to UTF-8 in any way: neither sending connection properties client_encoding=UTF8 using springframework, nor "SET CLIENT_ENCODING = 'UTF8';" directly in jdbc transaction - nothing helps.

On setting client encoding in jdbc transaction I checked, if client_encoding is really set - yes, the client_encoding is realy set in the UTF8, but the next statement of the same session returns me still not recognized special chars.

con = ds.getConnection();
con.setAutoCommit(false);
stmt = con.prepareStatement("SHOW client_encoding");
ResultSet rs = stmt.executeQuery();
 while(rs.next()){
  System.out.println(rs.getString(1)); 
  //Here is the output "UNICODE"
 }
 stmt.close();
 stmt = con.prepareStatement("SET client_encoding='UTF8'");
 stmt.execute();
 stmt.close();
 stmt = con.prepareStatement("SHOW client_encoding");
 rs = stmt.executeQuery();
 while(rs.next()){
  System.out.println(rs.getString(1)); 
  //Here is the output "UTF8"
 }
 stmt.close();
 stmt = con.prepareStatement(sql);
 ResultSet res = stmt.executeQuery();

 while(res.next()) {
  String s = res.getString("mycolumn");
  System.out.println(s);
  //The text has "?" instead of special chars
 }

Configuration:

<bean id="mybean" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> 
 <constructor-arg><value>[myurl]</value></constructor-arg>
 <constructor-arg>
  <props>
   <prop key="charSet">SQL_ASCII</prop>
   <prop key="client_encoding">UTF-8</prop> 
   <prop key="timezone">UTC</prop>
   <prop key="user">[user]</prop>
   <prop key="password">[password]</prop>
   <prop key="allowEncodingChanges">true</prop>
  </props>
 </constructor-arg>
 <property name="driverClassName"><value>org.postgresql.Driver</value></property>
</bean>

Used PostgreSQL-Driver is postgresql-8.4-701.jdbc4.jar

The input in the PostgreSQL is LATIN1, also I tried it by setting encoding to LATIN1 and ISO88591 - the same error happens.

Is it really possible now to convert this encoding to any standards?

Thank you in advice!

Answer 1

Solution

I found allready the solution. You should avoid conversion and get directly the bytes:

private String getSqlAscii(ResultSet res, String column) throws SQLException    {
    byte[] b = res.getBytes(column);

    if(b != null)   {
        try {
            return new String(b, "[input-encoding]");
        } catch (UnsupportedEncodingException e) {
            log.error("Wrong encoding configured", e);
        }
    }

    return null;
}

One important requirement: you should know, what a Charset was used on the data input into the PostgeSQL. This is the weak point of SQL_ASCII