error saving Excel document in varbinary(max) field

• stephen cameron

  Grasshopper

  Points: 13

  More actions

  April 12, 2007 at 12:33 am

  #172973

  Hello,

  We have a web site where users can upload and save various documents.

  These are being stored in an SQL Server varbinary(max) field and loaded using a JDBC PreparedStatement.

  I have error where the insert fails for Excel spreadsheets but works for other document types including pdf Word and text files.

  I get a DataTruncation Exception but there seems no relationship to the size of the document being stored.

  The code is as follows:

  StringBuffer sqlInsert = new StringBuffer();

  sqlInsert.append("INSERT INTO licence_change_document ");

  sqlInsert.append("(licence_change_id,name,size,content_type,data) ");

  sqlInsert.append("VALUES ");

  sqlInsert.append("(?,?,?,?,?)");

  PreparedStatement ps = connection.prepareStatement(sqlInsert.toString());

  ps.setInt(1,this.getLicenceChangeId());

  ps.setString(2,this.getName());

  ps.setInt(3,this.getDocument().length);

  ps.setString(4,this.getContentType());

  ps.setBytes(5,this.getDocument());

  ps.execute();

  ps.close();

  this.getDocument() returns a byte array.

  I've tried variations on the above including using the PreparedStatement setByteStream() method instead of setBytes().

  The size of the documents I've tested aren't big, around 50KB.

  The jdbc driver is jtds in the jar jtds-1.2.jar.

  Has anyone seen this kind of problem?

  Thanks

• Matija Lah

  SSCertifiable

  Points: 6429

  More actions

  October 17, 2007 at 5:24 am

  #742662

  Perhaps you should ask this in a Java-related newsgroup/forum.

  ML

  ---
  Matija Lah, SQL Server MVP
  http://milambda.blogspot.com