JDBC iso-8859-1 encoding

Question

In my program, I'm connecting to a Microsoft SQL database using JTDS (http://jtds.sourceforge.net/). The encoding of my database is iso-8859-1. However, to send queries in Java, I have to use Strings (which are normally encoded with UTF). Is it possible to send a Query with a different encoding than UTF?

EDIT: The results are displayed wrong after I use INSERT or UPDATE. If I only select from the database, special characters are displayed right.

Answer 1

One outstanding design principle of java is that text, String, is always Unicode. In general you cannot even see that it is in Unicode, as only when getting byte you (should) indicate the encoding the bytes should be converted into. So you would not need to do anything - ideally.

The error can stem from:

• Hard coded literal strings in Java: the encoding of the java source must be the same as the encoding of the java compiler javac. This can be tested by trying \u00FC instead of ü.
• Driver settings
• Connection settings
• Database / table / column definitions
• False placed error: when outputting results, when inputting data

Try an INSERT of "\u00FC + ü" and a SELECT for a byte comparison. Dump the byte codes. (To evade console problems.)

Arrays.toString(string.getBytes("ISO-8859-1"));

Do not try repairs like new String(s.getBytes("ISO-8859-1"), "UTF-8") - or so.

Should this find no problems the cause must be sought elsewhere.

BTW: better use Windows-1252 (Windows Latin-1) instead of ISO-8859 (Latin-1) as that allows some special chars like comma-like quotes (range 0x80 - 0xBF). HTML accepts Windows-1252 as ISO-8859-1 too.

Answer 2

The JDBC driver actually handles the conversion internally, if you are not simply creating inserts by concatenating Strings:

(This is bad, never do this)

String aValue = "äöü";
String insert = "INSERT INTO table VALUES('" + aValue + "')";
Statement s = connection.createStatement();
s.executeUpdate(insert);

Use a prepared statement (that also avoids security holes like SQL injection):

String aValue = "äöü";
String insert = "INSERT INTO table VALUES(?)";
PreparedStatement s = connection.prepareStatement(insert);
s.setString(1, aValue);
s.executeUpdate();

Edit: Also make sure that what you're trying to insert is really what you want to insert. For the german umlauts, there is more than one possible representation in unicode, e.g. ö can be represented as "\u00F6", but it can also (rarely, depending on source) be represented using combining diacritics (e.g "o\u0308" also looks like ö).

Answer 3

When I connect to an Access database (.mdb) which is encoded with ISO-8859-1 I use this syntax:

String dbPath = "fakeDBPath.mdb";
String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" + dbPath + ";DriverID=22;READONLY=false}";

final Properties prop = new Properties();
prop.put("charSet", "ISO-8859-1");
Connection conn = DriverManager.getConnection( database, prop );

After I get the Connection I'm able to use Java strings withouth the need to specify any additional encoding. Maybe JTDS supports a specific propertiy to set encoding.

For example, to insert data in the DB:

String cmd = "INSERT INTO Table (Col1,Col2,Col3,Col4) VALUES (1000,'àèìòù','é®þü','fake data');";
Statement s = DBTable_1.getStatement();
try
{
    int r = s.executeUpdate(cmd);
} catch ( SQLException ex )
{
    Logger.getLogger( Main.class.getName() ).log( Level.SEVERE, null, ex );
}

And to read from the DB:

String cmd = "SELECT * FROM Table WHERE Col2='àèìòù';";
Statement s = DBTable_1.getStatement();
try
{
  ResultSet r = s.executeQuery(cmd);

  while(r.next())
  {          
    System.out.println("Col2: " + r.getString(2) + " Col3:" + r.getString(3));
  }

} catch ( SQLException ex )
{
  Logger.getLogger( Main.class.getName() ).log( Level.SEVERE, null, ex );
}