CODEWITHSUNDEEP
javasqlitejdbcencodingcharacter-encoding
metinkale38
metinkale38

Reputation: 728

Strange encoding in SQLite database: How to retrieve string values with JDBC?

I have

    Class.forName("org.sqlite.JDBC");
    Connection conn = DriverManager.getConnection("jdbc:sqlite:cities.db");
    Statement stat = conn.createStatement();

    ResultSet rs = stat.executeQuery("select * from cities;");+

    while (rs.next()) {
        byte[] bytes = rs.getBytes("country");
        String country = new String(bytes, "utf-8");
        System.out.println(country);
    }

    rs.close();
    conn.close();

My output shows � for characters like ä,ö,ü.

If I run sqlite3 from the command line and query the table from there, the characters are fine...

"pragma encoding;" shows utf-8...

If I debug, the bytes array has "-127" at the place of "ü"...

how to fix that? what could cause that problem?

link to example SQLite file (only one row):

https://www.dropbox.com/s/6nvl737b6picbie/cities.db?dl=0

Upvotes: 1

Views: 4685

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123829

The issue here was that the SQLite database contained characters that were encoded with the old IBM OEM character set (code page 437), so the 'ü' character was encoded as 0x81. That is an unused character in Unicode and all(?) of the ISO-* and Windows-* character sets.

enter image description here

I was able to extract the "Country" value correctly using

public static void main(String[] args) {
    String connectionURL = "jdbc:sqlite:C:/__tmp/cities.db";
    try (Connection conn = DriverManager.getConnection(connectionURL)) {
        String sql = "SELECT Country FROM CITIES";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    String s = new String(rs.getBytes(1), "cp437");
                    System.out.println(s);
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace(System.err);
    }       
}

Upvotes: 1

Boris Pavlović
Boris Pavlović

Reputation: 64642

Could you try with

Class.forName(org.sqlite.JDBC.class.getCanonicalName());
String url = "jdbc:sqlite:";
SQLiteConfig config = new SQLiteConfig();
config.setEncoding(SQLiteConfig.Encoding.UTF8);
Connection conn = DriverManager.getConnection(url, config.toProperties());
Statement stat = conn.createStatement();

ResultSet rs = stat.executeQuery("select * from cities;");+

while (rs.next()) {
    String country = rs.getString("country");
    System.out.println(country);
}

Upvotes: 2

Related Questions