Ro. Ro. - 1 year ago 200
Java Question

Inserting Binary data into MySQL (without PreparedStatement's)

I'm trying to insert some Binary data into a MySQL database without using prepared statements. The reason for this is that I concatenate thousands of statements into a single insert an run that once. (Exactly how the MySQL dump & import works)

I have tried the following statements, but the are all failing:

INSERT INTO my_table VALUES (1,'g=���F|�}X���',2);

INSERT INTO my_table VALUES (1,CAST( 'g=���F|�}X���' AS BINARY),2);

INSERT INTO my_table VALUES (1,CONVERT( 'g=���F|�}X���', BINARY),2);

INSERT INTO my_table VALUES (1,BINARY 'g=���F|�}X���',2)

The error I get is:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'binary_data' at row 1

The code I use to execute the statement is simply:


PreparedStatements work fine (but are too slow in this case)

The actual String I in the database displays a little differet:


Binary View: 67 3d 81 f7 19 f3 46 7c b8 7d 58 8c 10 a3 ec 5b

Java Bytes: 103, 61, -127, -9, 25, -13, 70, 124, -72, 125, 88, -116, 16, -93, -20, 91

Could this be something to do with Encoding ?

Any hints much apprecaited,

Ro. Ro.
Answer Source

Found the solution .... Although not something I saw documented anywhere .....

You can insert Binary data directly by writing the bytes converted to HEX and preceeded by 0x

For example:

INSERT INTO my_table VALUES (1,0x19c0300dc90e7cedf64703ed8ae8683b,2);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download