Bug #1071 Retrival of String from ResultSet after updateRow becomes Escaped
Submitted: 18 Aug 2003 2:37 Modified: 28 Mar 2014 11:58
Reporter: Alan Tam Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:3.0.8 and 3.1.0 OS:Linux (Linux)
Assigned to: Alexander Soklakov CPU Architecture:Any

[18 Aug 2003 2:37] Alan Tam
Description:
See how to repeat for the code.

The string I set is " \ '
and the string printed is \" \\ \' ,
which is the string to be sent to the DB, not the string expected.

How to repeat:
Connection conn = ...;
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery(...);
rs.next();
rs.updateString(1, "\" \\ '");
rs.updateRow();
System.out.println(rs.getString(1));

Suggested fix:
In 3.0.8,

UpdatableResultSet.updateString calls:
PreparedStatement.setString which escaped the string

UpdatableResultSet.updateRow calls:
UpdatableResultSet.syncUpdate which calls:
PreparedStatement.setBytes which escapted the string once more!

Seems that we should leave the escape in updateRow() instead of updateString(), because we may want to retrieve it back w/o contacting the server.
[18 Aug 2003 7:53] Mark Matthews
What encoding is your database set to? If I use the default (latin1), I don't see the error you describe. This error might be related to a bug (currently being fixed) in the escaping that is done for GBK, SJIS and UJIS charsets.
[18 Aug 2003 7:58] Alan Tam
Oh, yes it is in big5, i.e. a DBCS. So it is a known problem?
Any targetted milestone for it to be fixed? It sounds strange that it is a DBCS problem because my string does not have any DBC at all! And escaping should be done in one place rather than two, shan't it?
[18 Aug 2003 8:08] Mark Matthews
The issue is that certain double-wide charsets have the '\' character as the 'high byte' (this only seems to occur in the asian or 'eastern' charsets), so that byte has to be escaped _once_ the string has been converted to bytes. To do this in any other form would require the entire processing path of the driver to be re-written, which is not an acceptable fix.

The fix will be released in version 3.0.9 and 3.1.1 which will be released when MySQL-4.1.1 is released.
[18 Aug 2003 8:15] Alan Tam
I see. I quite know the problem of escaping in char and in byte form (because I am also somehow heavily debugging the ByteFX.Data.MySQL C# Data Provider.

But will I need mysql-server 4.1.x if I need to get it work? Any timeline for this release? Will it be like the old 4.0.2 that I have to wait 1 year (i.e. next April) to get 4.1.1 released?

Btw, I think it has the '\' character in low byte, instead of high byte ;-)
[25 Aug 2003 10:27] Mark Matthews
Are you sure this happens with the current snapshot build of 3.0.8? I can't reproduce your issue with the following testcase (using a 'big5' encoding):

    /**
     * Test fixes for BUG#1071
     * 
     * @throws Exception if the test fails.
     */
    public void testUpdatabilityAndEscaping() throws Exception {
    	
    	Properties props = new Properties();
    	props.setProperty("useUnicode", "true");
    	props.setProperty("characterEncoding", "big5");
    	
    	Connection updConn = getConnectionWithProps(props);
		Statement updStmt = updConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
		ResultSet.CONCUR_UPDATABLE);
			
    	try {
			updStmt.executeUpdate("DROP TABLE IF EXISTS testUpdatesWithEscaping");
			updStmt.executeUpdate("CREATE TABLE testUpdatesWithEscaping (field1 INT PRIMARY KEY, field2 VARCHAR(64))");
			updStmt.executeUpdate("INSERT INTO testUpdatesWithEscaping VALUES (1, null)");
    		
			String stringToUpdate = "\" \\ '";
			
			this.rs = updStmt.executeQuery("SELECT * from testUpdatesWithEscaping");
			
			this.rs.next();
			this.rs.updateString(2, stringToUpdate);
			this.rs.updateRow();
			
			assertTrue(stringToUpdate.equals(rs.getString(2)));
    	} finally {
			updStmt.executeUpdate("DROP TABLE IF EXISTS testUpdatesWithEscaping");
			updStmt.close();
			updConn.close();
    	}
    }

Do you have a _full_ testcase (similar to the above) that reliably reproduces the issue? Can you try the nightly snapshot build of 3.0.8 from http://mmmysql.sourceforge.net/snapshots/stable/ ???
[19 Nov 2003 11:32] Mark Matthews
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Open". Thank you.
[26 Oct 2005 11:37] Dietmar Zoerner
Hi Mark,

I can reproduce this bug with 3.1.7 and 3.1.11 (mysql-connector-java-3.1.11-bin.jar)
using DataSource with setUseUnicode(true) and setCharacterEncoding("big5").

Database charset ist latin1.
OS: linux & window - same bug.

Code fragment creating my connections:

    private static DataSource getDataSource(String Server, String db,
            String port) {
        if (ds == null) {
            com.mysql.jdbc.jdbc2.optional.MysqlDataSource mds = new com.mysql.jdbc.jdbc2.optional.MysqlDataSource();
            mds.setUseUnicode(true);
            mds.setCharacterEncoding("big5");
            mds.setServerName(Server);
            mds.setPortNumber(Integer.parseInt(port));
            mds.setDatabaseName(db);
            ds = mds;
        }
        return ds;
    }

....

  Connection c = getDataSource(server, db, port).getConnection(user, password);

....

Greetings
Dietmar
[28 Mar 2014 11:58] Alexander Soklakov
I close this report as "Can't repeat" because there is no feedback for a long time and codebase is too old. Please, feel free to reopen it if the problem still exists in current driver.