CODEWITHSUNDEEP
javamysqlblobsinglestore
Spierki
Spierki

Reputation: 241

Inserting and reading blob memsql

I'm trying to insert blob in my MemSQL database.

This is where I get my source to insert, so I convert it into a blob :

byte[] bytes = rs.getString(2).getBytes("utf-8");
Blob blob = insert.insertTableBinary(bytes);

This is my insertTableBinary function : 

Blob blob = conn.createBlob();
blob.setBytes(1, bytes);
System.out.println(blob.length());
String sql = "INSERT INTO binaire(receipt) VALUES(\"" + blob + "\")";
executeSQL(sql);    
return blob;

At this moment blob.lenght equals 1555 but when I read it thanks to :

String sql2 = "SELECT * FROM binaire";
Statement st = conn.createStatement();
ResultSet rs2 = st.executeQuery(sql2);
while(rs2.next()) { 
    Blob blob = rs2.getBlob(1);
    System.out.println(blob.length());
}

The length is 25. The strange thing is that when I look the data in the table I got : com.mysql.jdbc.Blob@6a8bc5d9

So it's doesn't store the good thing no ? This is the reference to the object but not the blob I think.

But I don't know what I'm doing wrong, I see lot's of example with prepared statement but this features isn't available in MemSQL

Upvotes: 2

Views: 1184

Answers (2)

Joseph Victor
Joseph Victor

Reputation: 829

edit: leave blob as a byte[] if you can.

First off I strongly recommend using a prepareStatement.

Something like

PreparedStatement stmt = con.preparedStatement("INSERT INTO binaire(receipt) VALUES(?)");
stmt.setObject(1, blob)
stmt.executeUpdate()

But if you hate the idea of a prepared statement, you can just do 

String sql = "INSERT INTO binaire(receipt) VALUES(\"" + new String(blob) + "\")";

best, -JoYo

Upvotes: 1

Ishamael
Ishamael

Reputation: 12795

As Joseph suggested, the best way to issue the query is to use JDBC prepared statements. They are supported by MemSQL, and are a preferred way of executing parametrized queries, because they do all the escaping for you.

The reason you get your 25-byte string instead of the string you are inserting is because Blob's toString method is not overridden in Java, and when it is implicitly casted to string, it just returns some kind of Java pointer instead of the content. Based on my limited knowledge of Java the alternative that Joseph suggested in the second half of his answer (explicitly casting to a string) would result in the same behavior. One way to address it would be to get the byte array from the blob (blob.getBytes), and passing that as a string

String sql = "INSERT INTO binaire(receipt) VALUES(\"" + new String(blob.getBytes()) + "\")";

This, however, is very prone to SQL-injection issues (if the blob contains a double quote, the query will either fail, or result in an undesirable result), so the solution with the prepared statement from Joseph's answer should be used instead.

Upvotes: 1

Related Questions