Insert/update to sql_ascii encoding postgreSQL

Question

Here is a postgreSQL with server encoding SQL_ASCII. When I get data, I must use function convert_to(column1, 'SQL_ASCII') in select, and then use new String(value1, 'GBK') in java to get the right value.

But, when I send data by insert/update, the value in DB always error. Anyone can tell me how to send SQL including Chinese or other character by Java?

Apache DBCP config:

driverClassName=org.postgresql.Driver
url=jdbc:postgresql://127.0.0.1:5432/fxk_db_sql_ascii
username=test
password=test
initialSize=10
maxTotal=10
maxIdle=10
minIdle=5
maxWaitMillis=1000
removeAbandonedOnMaintenance=true
removeAbandonedOnBorrow=true
removeAbandonedTimeout=1
connectionProperties=useUnicode=true;characterEncoding=SQL_ASCII;allowEncodingChanges=true

SQL query in java:

    String sql = "select user_id, first_name as first_name, convert_to(first_name, 'sql_ascii') as first_name1, last_name as last_name, convert_to(last_name, 'sql_ascii') as last_name1 from public.tbl_users";
    ResultSet rs = stmt.executeQuery(sql);
    List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
    ResultSetMetaData md = rs.getMetaData();
    int columnCount = md.getColumnCount();
    while (rs.next()) {
        Map<String, Object> rowData = new HashMap<String, Object>();
        for (int i = 1; i <= columnCount; i++) {
            rowData.put(md.getColumnName(i), rs.getObject(i)==null?"":new String(rs.getBytes(i),"GBK"));
        }
        list.add(rowData);
    } 
  rs.close();

But how should I do while insert/update?

You start off with "Here is ...", but haven't included any code or representative date ([mcve]). You haven't clearly demonstrated what you may have done to try and solve this problem in the first place. [ask] — AJD, Apr 08 '18 at 03:01
And I use DBCP to connect DB: connectionProperties=useUnicode=true;characterEncoding=GBK;allowEncodingChanges=false..... — Alex, Apr 08 '18 at 03:17
@Alex Provide further detail as edits to your Question, not Comments. — Basil Bourque, Apr 08 '18 at 03:18
Many thanks for you comment. The PostgreSQL version is 8.2.44, the driver is offical JDBC driver 8.4-702.jdbc4, the data source is apache commons-dbcp version 1.4 — Alex, Apr 09 '18 at 07:22

score 0 · Answer 1 · answered Apr 08 '18 at 03:28

Avoid `SQL_ASCII`

You should be using a server encoding of UTF8 rather than SQL_ASCII.

The documentation is quite clear about this matter, and even includes a warning to not do what you are doing. To quote (emphasis mine):

The SQL_ASCII setting behaves considerably differently from the other settings. When the server character set is SQL_ASCII, the server interprets byte values 0-127 according to the ASCII standard, while byte values 128-255 are taken as uninterpreted characters. No encoding conversion will be done when the setting is SQL_ASCII. Thus, this setting is not so much a declaration that a specific encoding is in use, as a declaration of ignorance about the encoding. In most cases, if you are working with any non-ASCII data, it is unwise to use the SQL_ASCII setting because PostgreSQL will be unable to help you by converting or validating non-ASCII characters.

Use `UTF8`

Use an encoding of UTF8, meaning UTF-8. This can handle the characters for any language including Chinese.

And the UTF8 encoding allows Postgres to make use of the new support for International Components for Unicode (ICU) in Postgres 10 and later.

Java also uses Unicode encoding. Just let your JDBC driver handle the marshaling of text between Java and the database.

Anyway, many thanks for your answer, but the DB is not under my control.... — Alex, Apr 08 '18 at 03:34

Insert/update to sql_ascii encoding postgreSQL

1 Answers1

Avoid SQL_ASCII

Use UTF8

Avoid `SQL_ASCII`

Use `UTF8`