Exception when trying to insert Bytes into DB (MySQL)

Question

I am trying to insert this record into the database with a column of TEXT NOT NULL (and tried variations with utf8, and unicode and such) but had no luck so far on getting this dang insert in Java to work. I don't seem to be having these issues in Python, but what I need to do is store this data in my java server, and then retrieve it with my Python website. I have provided a small chunk of code and the Java exception below. I spent a bunch of time upgrading MySQL to 5.6 from 5.1 because I read online that Bytes are handled differently, but this did not help.

Happy to provide any additional information that will help.

public static byte[] compress(String str) throws Exception {
    if (str == null || str.length() == 0) {
        return null;
    }

    ByteArrayOutputStream obj = new ByteArrayOutputStream();
    GZIPOutputStream gzip = new GZIPOutputStream(obj);
    gzip.write(str.getBytes("UTF-8"));
    gzip.close();
    return obj.toByteArray();
}

byte [] bytes = null;
        try {
            bytes = compress(test); // test is a huge JSON String
        } catch (Exception e) {
            e.printStackTrace();
        }
try {
            Class.forName("com.mysql.jdbc.Driver");
            connect = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/badlionDev?"
                           + "user=root&password=password&useUnicode=true&characterEncoding=UTF-8");
            preparedStatement = connect.prepareStatement("UPDATE kit_pvp_matches SET data = ?;");
            // "myuser, webpage, datum, summary, COMMENTS from FEEDBACK.COMMENTS");
            // Parameters start with 1
            preparedStatement.setBytes(1, bytes);
            preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
        }


java.sql.SQLException: Incorrect string value: '\x8B\x08\x00\x00\x00\x00...' for column 'data' at row 1
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2825)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2156)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2459)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2376)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2360)
    at TestCompression.main(TestCompression.java:34)

Why are you trying to insert as bytes? Also, where does the "bytes" variable come from in your example code? — chooban
– chooban, Commented Dec 6, 2013 at 17:02
My bad. Let me add a bit more code. Just woke up and forget to explain that I am trying to compress a JSON object with gzip and store it in a DB. — MasterGberry
– MasterGberry, Commented Dec 6, 2013 at 17:21

Jess Balint · Accepted Answer · 2013-12-06 19:11:06Z

1

You should set the character set to BINARY or use the BLOB type. If you set it to UTF-8, MySQL expects valid UTF-8 data. The exception is saying that this is not valid UTF-8 data.

answered Dec 6, 2013 at 19:11

Jess Balint

1,70713 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Exception when trying to insert Bytes into DB (MySQL)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related