Data stored as blob but the length is different before and after storing it

Question

I have been stuck with this problem for days and have no solutions to this problem so would need to seek higher power for help. For sending data to apache tomcat servlet, i have this:

  temp = items.get(4);
  Blob b = connection.createBlob();
  byte [] byteArray = null;
  byteArray = temp.get();
  b.setBytes(1, byteArray);
  sql = "insert into userdata (data)+"values ('"+b+"')";
  s = connection.createStatement();
  s.execute(sql);
  out.println("Data successfully uploaded!");

and for receiving data back from servlet, i have this:

      Blob b = connection.createBlob();
  //String data = null;
  byte [] buffer;

  temp = items.get(1);
  String comments = temp.getString();
  System.out.println(comments);
  sql = "select sounddata from userdata where comments = '"+comments+"'";
  s = connection.createStatement();
  s.executeQuery(sql);
  rs = s.getResultSet();      
  while (rs.next ())  
  {
      b = rs.getBlob("data");

  }
  buffer = b.getBytes(1, (int)b.length());
  System.out.println("AFTER:"+b.length());
  System.out.println("FILE:"+b);

However, my blob length is different before and after i inserted into mysql database. I have a blob length of 34912 but when i retrieve it, it is only 27! What could be the problem here?

I traced the error to a difference in blob. When inserting blob into the database, blob value is "com.mysql.jdbc.Blob@2db19d" but when i retrieve it becomes "com.mysql.jdbc.Blob@14d7745" instead. Why is that so?

score 1 · Accepted Answer · answered Jul 15 '12 at 16:34

1

This is your problem:

sql = "insert into userdata (data)+"values ('"+b+"')";

This will not escape b properly. For instance, b might have a quote character in it! You need to use a PreparedStatement to build your sql so that b gets escaped properly.

Also, "data" != "sounddata".

answered Jul 15 '12 at 16:34

Keith Randall

22,985
2
35
54

Thank you kind sir! that is the problem and forgive me if i didnt smack myself coz i notice a similar post regarding the same problem. http://stackoverflow.com/questions/9430008/inserting-blob-data-in-java-using-preparedstatement – Sheep Jul 16 '12 at 09:36

Data stored as blob but the length is different before and after storing it

1 Answers1