[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - problem streaming data to a blob on an sqlserver

[hugin78]

I have an entity with a lob value:

  |    @Lob
  |     public byte[] getData() {
  |         return data;
  |     }
  | 

I have a large amount of data that Iretrieve in small parts from the database, convert it to xml and stream it to the blobfield.



  |        public void insertData() throws Exception {
  |               Context ctx = new InitialContext();
  |               DataSource ds = (DataSource) ctx.lookup("java:/UdtraekDS");
  |               Connection connection = ds.getConnection();          
  | 
  |               try {
  |                    if (connection != null) {
  |                         String readStatementBlobStreamOracle    = "select data from as_udtraek where name = ?  for update";
  |                        String readStatementBlobStreamSQLServer = "select data from as_udtraek (UPDLOCK) where name = ?";
  |                         // Select sql depends on the database
  |                        String sql = Util.isOracle() ? readStatementBlobStreamOracle : readStatementBlobStreamSQLServer;
  |   
  |                         PreparedStatement ps = connection.prepareStatement(sql);
  |                         ps.setString(1, "allekatalogydelser.xml"); 
  | 
  |                         ResultSet res = ps.executeQuery();
  |                         if (res.next()) {
  |                              Blob val = res.getBlob(1);
  |                              OutputStream outputStream = val.setBinaryStream(1);
  |                              logger.info("Writing data to blob");
  |                              // Lots of data will be generated here and written to the outputstream
  |                           
  |                              // For test
  |                              outputStream.write("testing".getBytes());
  | 
  |                              outputStream.flush();
  |                              outputStream.close();
  |                         }
  | 
  |                         res.close();
  |                         ps.close();
  |                    }
  |               } catch (Exception e) {
  |                    logger.error(1, e);
  |               } finally {
  |                    try {
  |                         connection.close();
  |                    } catch (SQLException e) {
  |                         logger.warn("could not close the connection", e);
  |                    }
  |               }
  |           }
  | 

In the example above I retrieve the existing AS_UDTRAEK entity with the name "allekatalogydelser.xml? and write the text ?testing? to the blobfield. This works perfectly well with an oracle db but with a Microsoft SQL server 9 no data is written to the blob and no exception is thrown. 

Can anyone tell me what I'm doing wrong.

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4014846#4014846

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4014846