0 Replies Latest reply on Feb 12, 2007 2:12 AM by Rasmus Carlsen

    problem streaming data to a blob on an sqlserver

    Rasmus Carlsen Newbie

      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.