2 Replies Latest reply: Sep 13, 2007 11:28 AM by 807600 RSS

    Problem while inserting a Blob in to oracle database

    807600
      This was my code for inserting a File in to Oracle database in a Blob Datatype

      File file = new File(AppsConfig.getAppsInstalledPath() + File.separator + "temp"
                               + File.separator + req.getSession().getAttribute("user")+File.separator+fileName);
                     
                     FileInputStream fileInputStream = new FileInputStream(file);
                     
                     pstmt = conn.prepareStatement("INSERT INTO ICD9DISCHARGEFILE (ICD9DISCHARGEFILEID, ICD9, FILENAME, CREATE_USER,"
                     + "STATUS, ICD9DISCHARGEFILEVALUE, CREATE_DATE) VALUES (?,?,?,?,?,?,sysdate)");
                     pstmt.setInt(1, Integer.parseInt(nextID));
                     pstmt.setString(2, ICD9);
                     pstmt.setString(3, fileName);
                     pstmt.setString(4, (String)req.getSession().getAttribute("user").toString());
                     pstmt.setInt(5, Status.ACTIVE);
                     pstmt.setBinaryStream(6, fileInputStream, (int)file.length());
                     pstmt.executeQuery();
                     pstmt.close();
                     fileInputStream.close();


      I am getting an Expection at pstmt.executeQuery() line,
      The log is as follows
      java.sql.SQLException: ORA-01460: unimplemented or unreasonable conversion requested

           at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
           at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
           at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
           at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
           at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
           at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
           at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
           at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
           at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527)
           at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
        • 1. Re: Problem while inserting a Blob in to oracle database
          791266
          Please don't cross-post:

          http://forum.java.sun.com/thread.jspa?threadID=5216043
          • 2. Re: Problem while inserting a Blob in to oracle database
            807600
            Snot really how you load a Blob into the DB.

            You need to:
            1. String sqlInsert = "insert into blob_table"
                                 +"  (blob_table.blob_id, blob_table.blob_blob)"
                                 +"  values (?),          EMPTY_BLOB())";
             
            2. prepareStatement pstmt = conn.prepareStatement(sqlInsert);
             
            3. File blobFile = new File(myID); // Where myID is, obviously, the Blob file name.
               pstmt.setString(1, _myID);      // myID name (or whatever you want here.
             
            4. /** Insert initial row with empty Blob */
               int count = pstmt.executeUpdate();
             
            5. String sqlReSelect  = "select blob_table.blob_blob"
                                   + "  from blob_table"
                                   + " where blob_table.blob_id = "+myID
                                   + "   for update";
             
            6. Statement stmt = conn.createStatement();
               ResultSet rset = stmt.executeQuery(sqlReSelect);
             
            7. Blob blob = rset.getBlob(1);
               OutputStream os = ((oracle.sql.BLOB)blob).getBinaryOutputStream();
               FileInputStream fis = new FileInputStream( blobFile );
               byte[] temBlob = new byte[50000], // Or whatever max size you need.
               int size = fis.read(tempBlob);
               byte[] pixels = new byte[size];
               for (int idx = 0; idx < pixels.length; idx++)
                 pixels[idx] = tempBlob[idx];
               os.write(pixels);
               fis.close();
               os.close();