4 Replies Latest reply on Jan 4, 2003 9:33 AM by 807549

    Inserting BLOB objects / Images into the db

    807549
      hi,
      I am trying to extract images from a zip file and put them into the database but its giving the below runtime error:

      ************************************************************************
      Max Count :1260
      assetId is : 421
      ava.sql.SQLException: No more data to read from socket
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:114)
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:156)
      at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:803)
      at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java, Compiled Code)
      at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java, Compiled Code)
      at oracle.jdbc.ttc7.Oclose.receive(Oclose.java, Compiled Code)
      at oracle.jdbc.ttc7.TTC7Protocol.close(TTC7Protocol.java:493)
      at oracle.jdbc.driver.OracleStatement.close(OracleStatement.java, Compiled Code)
      at oracle.jdbc.driver.OraclePreparedStatement.close(OraclePreparedStatement.java, Compil
      d Code)
      ************************************************************************

      This is the piece of code of interest:
      PreparedStatement pstmt

      ...

      InputStream fin = zfp.getInputStream(zipEnt)

      ZipFile zfp;

      ZipEntry zipEnt

      ...


      pstmt = conn.prepareStatement("INSERT INTO TEMP (itemimageID, assetid,imagetypecode,image) VALUES (?, ?, ?,?)");
      pstmt.setInt (1,intMaxItemImageID);
      pstmt.setInt (2,intAssetIdValue);
      pstmt.setString (3, imageType);
      pstmt.setBinaryStream (4, fin, (int)zipEnt.getSize());
      pstmt.execute();
      pstmt.close();
      fin.close();

      itemimageID,assetid are integers
      imagetypecode is String
      image is BLOB type in the database.

      dbdriver=oracle.jdbc.driver.OracleDriver

      Pls let me know if any other info you might need to solve this.

      Pls help !

      Thanks


        • 1. Re: Inserting BLOB objects / Images into the db
          807549
          Can you let me know how you resolved this? Thanks. david.ward@dotech.com
          • 2. Re: Inserting BLOB objects / Images into the db
            807549
            Search the forums for Blob/clob/oracle and you'll see plenty of posts.
            • 3. Re: Inserting BLOB objects / Images into the db
              807549
              pstmt.setBinaryStream (4, fin, (int)zipEnt.getSize());
              No can do, with Oracle. You need to read the Oracle JDBC documentation on their recommended solution. In short, the solution is completely Oracle-specific (i.e. not portable JDBC). You have to insert an empty blob into the row first, and then re-select the row you have just inserted, and update the blob into place.

              See http://technet.oracle.com, and search for the Oracle 9i JDBC documentation.
              • 4. Re: Inserting BLOB objects / Images into the db
                807549
                hi,
                I am trying to extract images from a zip file and put
                them into the database but its giving the below
                runtime error:

                *******************************************************
                ****************
                Max Count :1260
                assetId is : 421
                ava.sql.SQLException: No more data to read from
                socket
                at
                at
                at
                oracle.jdbc.dbaccess.DBError.throwSqlException(DBError
                java:114)
                at
                at
                at
                oracle.jdbc.dbaccess.DBError.throwSqlException(DBError
                java:156)
                at
                at
                at
                oracle.jdbc.dbaccess.DBError.check_error(DBError.java:
                03)
                at
                at
                at
                oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java
                Compiled Code)
                at
                at
                at
                oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java
                Compiled Code)
                at oracle.jdbc.ttc7.Oclose.receive(Oclose.java,
                .java, Compiled Code)
                at
                at
                at
                oracle.jdbc.ttc7.TTC7Protocol.close(TTC7Protocol.java:
                93)
                at
                at
                at
                oracle.jdbc.driver.OracleStatement.close(OracleStateme
                t.java, Compiled Code)
                at
                at
                at
                oracle.jdbc.driver.OraclePreparedStatement.close(Oracl
                PreparedStatement.java, Compil
                d Code)
                *******************************************************
                ****************

                This is the piece of code of interest:
                PreparedStatement pstmt

                ...

                InputStream fin = zfp.getInputStream(zipEnt)

                ZipFile zfp;

                ZipEntry zipEnt

                ...


                pstmt = conn.prepareStatement("INSERT INTO TEMP
                (itemimageID, assetid,imagetypecode,image) VALUES (?,
                ?, ?,?)");
                pstmt.setInt (1,intMaxItemImageID);
                pstmt.setInt (2,intAssetIdValue);
                pstmt.setString (3, imageType);
                pstmt.setBinaryStream (4, fin,
                (int)zipEnt.getSize());
                pstmt.execute();
                pstmt.close();
                fin.close();

                itemimageID,assetid are integers
                imagetypecode is String
                image is BLOB type in the database.

                dbdriver=oracle.jdbc.driver.OracleDriver

                Pls let me know if any other info you might need to
                solve this.

                Pls help !

                Thanks

                Hi,
                To insert into Oracle BLOB field; you have to first insert empty_blob() and then update the field.But before doing the process you have to set off the auto commit mechanism.

                Code sample:
                // set auto commit to false; for blob locking mechanism
                conn.setAutoCommit(false);
                pstmt = conn.prepareStatement("INSERT INTO TEMP
                 (itemimageID, assetid,imagetypecode,image) VALUES (?,
                 ?, ?,?)");
                pstmt.setInt (1,intMaxItemImageID);
                pstmt.setInt (2,intAssetIdValue);
                pstmt.setString (3, imageType);
                pstmt.setBinaryStream (4, fin,
                (int)zipEnt.getSize());
                pstmt.executeUpdate();
                
                // now select the image from database
                Statement stm = conn.createStatement("Select image from Temp where itemiamageID = '"++intMaxItemImageID"'");
                ResultSet rs = stm.executeQuery();
                
                // go the actual record
                rs.next();
                
                // read the image file byte by byte
                // and assign it to FileOutputStream
                // close the fileoutput stream
                
                // commit changes
                conn.commit();
                I think this will help you

                trr.

                Rana