Re: Help on Insert and Query Binary Data

Hi Cindy,

    Thanks so much for your kind help. I put in the psmt.close() and in.close() and the problem still exists. The while((len=movData.read(buf)) != 1) is a typo. You mentioned that I should try to check in sqlplus to see whether something is really in the table. I am kind of new to Oracle's sqlplus. After my java application tried to insert the binary video file. I tried the following command at the sqlplus command line, I got the following. Does this say anything?

    SQL> select video from videotable;

    V
    -

    Also, I tried another sql command: "select segment_table, segment_type, bypes, block from user_segments". It showed me the size of the videotable generated originally was 10240 bytes. After insertion with the java program, the size "Did Not Change"??? The strange thing is even when I tested to insert a very large ascii data, the table size stayed with the original 10240 bypes. I am wondering whether you have any good suggestion on this.

    Once again, thanks so much.

    Stephen

    By the way, when I tried the Sybase SQL anywhere, I could clearly see the file size got dramatically increased when I tried the java program to insert the binary data. Do you know which file in Oracle I should look into?

Hi Stephen,
  did an exceptions get thrown? Did you check in sqlplus whether something is really in the table after
your insert?

  after your insert:

     psmt.executeUpdate();
  put a
    psmt.close();
    in.close();

and when you are reading the stream back, change
  while((len = movData.read(buf)) != 1){ to
  while((len = movData.read(buf)) != -1) {

Don't know if that was just a typo on your part.

Hope some/all of this helps!
Cindy

Stephen Wang wrote:

  Hi all,

      I am developing applications and servlets (using Java Web Server 1.1.3

  on a NT machine) to query and update databases containing binary data. I   have tried Sybase SQL anywhere and Oracle8i. In Sybase, the field containing
  the binary data was defined as "long binary" and in Oracle, the field was   defined as "long raw".

      The problem I encountered was that I could not query out the binary file
  inserted. I got no data coming out of the query. Below I have provided the

  code that I used for inserting the binary data and querying the binary data.
  Any suggestions are greatly appreciated!!!

      Thanks in advance!!!

      Stephen

  // Insert binary data into a database

      PreparedStatement psmt = con.prepareStatement(
          "INSERT INTO VIDEOTABLE (PATIENTID, VIDEO) VALUES (?, ?)");

      FileInputStream in = new
  FileInputStream("d:/JavaWebServer1.1.3/public_html/logout.jpg");
      int inBytes = in.available();
      byte inBuf[] = new byte[inBytes];
      int bytesRead = in.read(inBuf, 0, inBytes);
      psmt.clearParameters();
      psmt.setString(1, "31313");
      psmt.setBinaryStream(2, in, inBuf.length);
      psmt.executeUpdate();

      // To get the binary data out

      Statement stmt = con.createStatement();
      sql = new String("SELECT IMAGE FROM IMAGETABLE WHERE
  patientID='31313'");
      ResultSet rs = stmt.executeQuery(sql);
      if(rs.next()){
          BufferedInputStream movData = null;
          movData = new BufferedInputStream(rs.getBinaryStream("VIDEO"));
          byte[] buf = new byte[4*1024];  //4K buffer
          int len;
          while((len = movData.read(buf)) != 1){
            out.write(buf, 0, len);
          }
      }