Can't insert byte[] into MySQL using java

Question

Following is the code I have used:

byte[] bkey = key.getEncoded();
String query = "INSERT INTO keytable (name, key) VALUES (?,?)";
PreparedStatement pstmt = (PreparedStatement) connection.prepareStatement(query);
pstmt.setString(1, "test");
pstmt.setBytes(2, bkey);
pstmt.execute();

And following is an error I got:

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key) VALUES ('test',_binary'?ʾ??s??u\'?}p?u')' at line 1

I have MySQL 5.0.41 and mysql-connector-java-5.1.7-bin.jar as JDBC library. Is anyone can help me out here? Thanks in advance!

Answer 1

Try using "setBinaryStream()" instead of "setBytes()", and pass it a ByteArrayInputStream constructed on your byte array. This, of course, assumes that the data type assigned to the column can store bytes... Make sure it is a BLOB, BINARY, or VARBINARY.

Also, use backticks to enclose your objects. "key" is a SQL keyword, and other than that it is just a good habit:

String query = "INSERT INTO `keytable` (`name`, `key`) VALUES (?,?)";

Answer 2

You should add a binary stream. Do you have access to the inputstream ?? like this..

FileInputStream input = new FileInputStream("myfile.gif");
String query = "INSERT INTO `keytable` (`name`, `key`) VALUES (?,?)";
PreparedStatement pstmt = (PreparedStatement) connection.prepareStatement(query);
pstmt.setString(1, "test");
pstmt.setBinaryStream(2, input, input.available());