Hello everyone,

[everything following on SuSE 10 with included MySQL but self-grabbed Connector/J]

moving from MySQL 4.0 to 4.1 I discovered (using Connector/J 3.1.10) that storing Strings into BLOB field does not seem to work any longer as soon as it comes to non-7-Bit-ASCII chars. Look at this slightly shortened pseudo-code:

--- conn=DriverManager.getConnection(...); dbstmt=conn.createStatement(); pstmt=conn.prepareStatement("insert into datatable (thevarchar,thetext,theblob,theblobasbytes) values (?,?,?,?)");

dbstmt.executeUpdate("create table datatable (thevarchar varchar(255) not null default ''," + "thetext text not null default '',theblob blob not null default '',theblobasbytes blob not null default '')");

String insertstring="Höhöhö";

dbstmt.executeUpdate("insert into datatable (thevarchar,thetext,theblob,theblobasbytes) values" + " ('"+DBAbstractor.escBinary(insertstring)+"','"+DBAbstractor.escBinary(insertstring)+"'," + "'"+DBAbstractor.escBinary(insertstring)+"','x')");

pstmt.setString(1,insertstring); pstmt.setString(2,insertstring); pstmt.setString(3,insertstring); pstmt.setBytes(4,insertstring.getBytes("UTF-8")); pstmt.execute();

ResultSet rs=dbstmt.executeQuery("select thevarchar,thetext,theblob,theblobasbytes from datatable"); while (rs.next()) { System.out..println("varchar: "+rs.getString(1)+", text: "+rs.getString(2)+ ", blob: "+rs.getString(3)+", blobasbytes: "+new String(rs.getBytes(4),"UTF-8"));

---

Output will look like this:

--- varchar: Höhöhö, text: Höhöhö, blob: H�h�h�, blobasbytes: x varchar: Höhöhö, text: Höhöhö, blob: H�h�h�, blobasbytes: Höhöhö

---

It doesn't matter neither whether the table is in latin1 or in utf8 encoding, nor whether the general system setup is de_DE@euro or de_DE.UTF-8.

I understand that the driver (or MySQL) changed encoding behaviour between MySQL 4.0 and 4.1. Is there any way to continue writing Strings to BLOB fields other than using explicit byte conversion? And if not, is there any way to perform such byte conversion when writing through a non-PreparedStatement? As you see, my example code just writes "x" to the database in this case.

Or am I doing something else wrong?

Best regards, Dirk

P.S.: As I wrote, the system's encoding does not make a difference. However, if the databases environment says "de_DE@euro", but Java's says "de_DE.UTF-8", the driver blocks on every write attempt. Bug?