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:
conn.createStatement().executeUpdate(sql);
PreparedStatements work fine (but are too slow in this case)
The actual String I in the database displays a little differet:
g=÷óF|¸}X£ì[
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
When inserting a single row into the MySQL table, the syntax is as follows: INSERT INTO table_name(column_1,column_2,column_3) VALUES (value_1,value_2,value_3); In the INSERT INTO query, you should specify the following information: table_name : A MySQL table to which you want to add a new row.
A BLOB is a binary large object that can hold a variable amount of data. There are four types of BLOB, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. These differ only in the maximum length of the values they can hold.
The BINARY attribute is a nonstandard MySQL extension that is shorthand for specifying the binary ( _bin ) collation of the column character set (or of the table default character set if no column character set is specified). In this case, comparison and sorting are based on numeric character code values.
In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance. Expand Databases, right-click the database from which to add the files, and then click Properties. In the Database Properties dialog box, select the Files page. To add a data or transaction log file, click Add.
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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With