Re: Blob read / write

From: Jusung Yang <jusungyang_at_yahoo.com>
Date: 29 Aug 2002 14:30:40 -0700
Message-ID: <42ffa8fa.0208291330.6ad15858@posting.google.com>

The following article may help you. It seems getBinaryOutputStream() would solve the 4k limitation. Not sure why you are seeing 2k limitation though. The character set of your German database (correct?) may have something to do with it.

---

This article contains an example of a JDBC program that inserts large binary data to a BLOB column using Oracle extensions to standard JDBC. The example inserts a DVD THX promotion into an Oracle database using chunked-streaming through JDBC.

This article is useful for Java Developers who need to have a working example which eliminates the known 4K direct data insertion limit to write data to BLOBs in an 8.1.5 database.

To avoid the 4K restriction, use getBinaryOutputStream() instead of setBinaryStream() to write data to BLOBs. The LOB locator is Oracle
specific and standard JDBC 2.0 API does not yet support it. LOB locators
should be supported by the forthcoming JDBC 3.0 API.

Program Notes

---

The application uses the Oracle JDBC Thin driver to connect to a local database. To run this program, create the following table:

CREATE TABLE MEDIA_STORE ( MNAME VARCHAR2(25),

                           MTYPE VARCHAR2(15),
                           MDATA BLOB );

This program was created with Oracle JDeveloper 3.0 using JDK 1.1.8.

Program

---

import java.io.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class OracleBLOB extends Object
{
  Connection conn;

  public OracleBLOB()
  { }

  private void openSession() throws SQLException, Exception   {
    try {
//Register the Oracle JDBC Driver.

      Class.forName("oracle.jdbc.driver.OracleDriver");


      //DriverManager.registerDriver(new

oracle.jdbc.driver.OracleDriver());

//Open a session.

      conn = DriverManager.getConnection("jdbc:oracle:thin:@<host machine>:<port>:<SID>","scott","tiger");

    } catch(SQLException e) {
      e.printStackTrace();
    }
  }

  private void insertBLOB() throws SQLException, Exception   {
    BLOB blob;
    File binFile;
    FileInputStream instream;
    OutputStream outstream;

    try {
//Set AutoCommit to OFF - required by BLOB locking mechanism.
      conn.setAutoCommit(false);

//Create a statement.

      Statement stmt = conn.createStatement ();

//Insert an empty BLOB locator.

      stmt.execute("INSERT INTO MEDIA_STORE VALUES('THX','DVD-VOB',empty_blob())");

//Execute the query and lock the BLOB row.
      ResultSet rset = stmt.executeQuery ("SELECT MDATA FROM MEDIA_STORE WHERE MNAME='THX' FOR UPDATE");       rset.next();

//Get the BLOB locator.

      blob = ((OracleResultSet)rset).getBLOB(1);

//Get the large binary media file.

      binFile = new File("D:\\THX.VOB");
      instream = new FileInputStream(binFile);
  


      //Insert to the BLOB from an output stream.

      outstream = blob.getBinaryOutputStream();



      //Read the input stream and write the output stream by chunks.

      byte[] chunk = new byte[blob.getChunkSize()];
      int i=-1;
      System.out.println("Loading");
      while((i = instream.read(chunk))!=-1)
      { outstream.write(chunk,0,i); System.out.print('.'); }



      //Close the input and output stream.

      instream.close();
      outstream.close();



      //Close the statement.

      stmt.close();
    } catch(SQLException e) {
      e.printStackTrace();

    }
  }

  private void closeSession() throws SQLException, Exception   {
    try {
//Close the session.

      conn.close();
    } catch(SQLException e) {
      e.printStackTrace();
    }
  }

  public static void main(String[] args) throws SQLException, Exception
  {
    OracleBLOB app = new OracleBLOB();

    app.openSession();
    app.insertBLOB();
    app.closeSession();

  }
}

Axel Weinberger <Axel.Weinberger_at_FactScience.de> wrote in message news:<3D6E31C5.33699E66_at_FactScience.de>...

> Hello,
> I'm new in Oracle 9i and want to use Blob's.
> The programming language is Java
> 
> I've tried to use
>     statement.setBinaryStream()
> but it worked only, when data was smaller than 2000 bytes.
> Why?
> 
> Trying to write the LOB to file I retrieved files of 0 bytes length.
> Why?
> 
> Can anybody help me ?
> Perhaps in German ?
> Thanks
> Axel

Received on Thu Aug 29 2002 - 16:30:40 CDT