0 Replies Latest reply on Oct 24, 2003 9:20 AM by deanmas

    BMP problem with Blob

    deanmas Newbie

      Hi,

      I have implemented a simple BMP entity bean that manipulates arbitary files in MySQL database.
      I am representing the file-data as an InputStream on the Bean's side and LARGEBLOB on the SQL side.
      Bellow is the listing of the ejbCreate(), ejbLoaf() and finder method:

      public abstract class ContentBean implements EntityBean {

      protected EntityContext ec;

      private String id;
      private String msgId;
      private String contentId;
      private String contentType;
      private String contentLocation;
      private int contentLength;
      private InputStream content;

      public ContentPK ejbCreate(
      String msgId,
      String contentId,
      String contentLocation,
      String contentType,
      int contentLength,
      InputStream content)
      throws CreateException {
      System.out.println("In ejbFCreate()");
      String id = ContentUtil.generateGUID(this);
      Connection conn = null;
      PreparedStatement ps = null;
      ResultSet rs = null;
      try {
      conn = getConnection();
      String insertString =
      "insert into content_tab values(?, ?, ?, ?, ?, ?)";
      ps = conn.prepareStatement(insertString);
      ps.setString(1, id);
      ps.setString(2, msgId);
      ps.setString(3, contentId);
      ps.setString(4, contentLocation);
      ps.setString(5, contentType);
      ps.setBinaryStream(6, content, contentLength);
      ps.execute();
      System.out.println("InsertString is " + insertString);

      //clean-up
      ps.close();
      conn.close();
      System.out.println("Cleaned up");

      return new ContentPK(id);
      } catch (SQLException e) {
      throw new EJBException("Error inserting into DB " + e);
      } catch (Exception e) {
      System.out.println("Error in ejbCreate() " + e);
      throw new EJBException("Error in ejbCreate() " + e);
      }
      }

      public void ejbLoad() {
      System.out.println("in ejbLoad()");
      String id = ((ContentPK) ec.getPrimaryKey()).id;
      Connection conn = null;
      PreparedStatement ps = null;
      ResultSet rs = null;
      try {
      conn = getConnection();
      String loadString =
      "select id, msg_id, content_id, content_location, "
      + "content_type, content from content_tab "
      + "where id = ?";
      ps = conn.prepareStatement(loadString);
      ps.setString(1, id);
      rs = ps.executeQuery();
      if (!rs.next())
      throw new EJBException("Object not found");
      setMsgId(rs.getString("msg_id"));
      setContentId(rs.getString("content_id"));
      setContentLocation(rs.getString("content_location"));
      setContentType(rs.getString("content_type"));
      Blob blob = rs.getBlob("content");
      setContentLength((int) blob.length());
      setContent(blob.getBinaryStream());


      } catch (SQLException e) {
      throw new EJBException("Error in ejbLoad() " + e);
      } catch (Exception e) {
      System.out.println("Error in ejbLoad() " + e);
      throw new EJBException("Error in ejbLoad " + e);
      } finally {
      // clean-up
      try {
      ps.close();
      conn.close();
      } catch (SQLException e1) {
      // TODO Auto-generated catch block
      e1.printStackTrace();
      }
      System.out.println("Cleaned up");
      }
      }

      public Collection ejbFindByMsgId(String msgId) {
      System.out.println("In ejbFindByMsgId");
      Collection vector = new Vector();
      Connection conn = null;
      PreparedStatement ps = null;
      ResultSet rs = null;
      try {
      conn = getConnection();
      String loadString =
      "select id from content_tab " + "where msg_id = ?";
      ps = conn.prepareStatement(loadString);
      ps.setString(1, msgId);
      rs = ps.executeQuery();
      while (rs.next()) {
      vector.add(new ContentPK(rs.getString("id")));
      }
      System.out.println(vector.size() + " contents found");
      //clean-up
      ps.close();
      conn.close();
      System.out.println("Cleaned up");

      } catch (SQLException e) {
      throw new EJBException("Error in ejbFindByMsgId() " + e);
      } catch (Exception e) {
      System.out.println("Error in ejbFindByMsgId() " + e);
      throw new EJBException("Error in ejbFindByMsgId() " + e);
      }
      return vector;
      }

      I call the find method from a servlet that passes the msgId parameter. First time I run it, data is correctly returned and displayed in browser. The problem is with any subsequent calls, ie. when I try read the contents from the InputStream to a bytearray, the read() method returns -1. All other fields are loaded succesfully (probably cached).
      I don't even know where to start looking, not sure if this is related to MySQL jdbc drivers, jboss entity management etc. Do I maybe need to add transaction support in the bean, or is it just a matter of tweaking the jboss cache. Im using JBOSS 321 and mysql 4.015 with latest j/connector drivers.
      Also, once the container calls ejbPassivate(), I am able to retieve the content once again.

      I appreciate any assistance

      Cheers