Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Inserting Binary data into MySQL (without PreparedStatement's)

Tags:

java

mysql

jdbc

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

like image 440
Ro. Avatar asked Apr 13 '12 10:04

Ro.


People also ask

How manually insert data in MySQL table?

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.

What datatype stores binary values MySQL?

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.

What is binary data type in MySQL?

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.

How do you insert a file into a database?

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.


1 Answers

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);
like image 114
Ro. Avatar answered Oct 18 '22 18:10

Ro.