MySQL Bugs: #7667: Update of LONG BYTE column fails with -8006 "Data types must be compatible"

Bug #7667	Update of LONG BYTE column fails with -8006 "Data types must be compatible"
Submitted:	4 Jan 2005 17:50	Modified:	29 Sep 2008 18:49
Reporter:	Kevin Grittner	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MaxDB	Severity:	S1 (Critical)
Version:	7.5.00.19	OS:	Windows (Windows 2000)
Assigned to:	Ulf Wendel	CPU Architecture:	Any

Description:
An INSERT statement may set a LONG BYTE column to a literal value using an ANSI compliant X'' literal; however, an UPDATE statement which sets a LONG BYTE column using such a literal fails.

How to repeat:
    statement.executeUpdate("CREATE TABLE TESTLONGBYTEUPDATE (F1 INT NOT NULL PRIMARY KEY, F2 LONG BYTE)");
    statement.executeUpdate("INSERT INTO TESTLONGBYTEUPDATE VALUES (1, X'1234567890ABCDEF')");
    try {
        int count = statement.executeUpdate("UPDATE TESTLONGBYTEUPDATE SET F2 = X'1023456789ABCDEF' WHERE F1 = 1");
        System.out.println("count = " + count);
    } catch (SQLException e) {
        e.printStackTrace();
    }

Console should show: count = 1
Console does show: com.sap.dbtech.jdbc.exceptions.DatabaseException: [-8006] (at 38): Data types must be compatible
	at com.sap.dbtech.jdbc.packet.ReplyPacket.createException(ReplyPacket.java:71)
	at com.sap.dbtech.jdbc.ConnectionSapDB.throwSQLError(ConnectionSapDB.java:829)
. . .

Hi Kevin,

I think this is not JDBC related. It's a general issue. I'll keep you informed about the developers answer.

Regards,
Ulf

Hello Kevin,

let me cite the developers: "the problem is that in the syntax mentioned above (doesn't matter if via sqlcli or JDBC) the update is tried with the use of a string literal. The long byte value has to be assigned to a parameter first and then updates are possible."

I'm afraid you'll have to accept this answer for now. An improvement is on our long term TODO but it has a low priority. Sorry, I can't promise a release time for you.

Please use a prepared statement as a workaround.

Regards,
Ulf

MaxDB is no longer under MySQL umbrella.