I have a file which I want to store into oracle database.  I'm using the Blob object.  I want to know how to convert this text file (can also be other types) into a blob object and store it.  I did not find any examples on the net.  Kindly send me a sample file which does the same!  Thanks.

Jyothi

Guess this is the code you require.

Insert

The following code snippet reads a file and stores it into the database:

// variables
String strStatement;
Statement stmt = null;
ResultSet resSet = null;
InputStream sampleFileStream = null;
OutputStream blobOutputStream = null;
try {
  // create a statement (connection is already existent)
  stmt = dbConn.createStatement();
  // get new id
  strStatement = " SELECT lobsamp_seq.nextval"
               + " FROM   dual";
  resSet = stmt.executeQuery(strStatement);
  if (resSet.next()) {
    lngId = new Long(resSet.getLong(1));
  }
  // insert new row
  // The LOB column value is initialized to empty in this step,
  // and will be loaded in the steps below.
  stmt.execute(" INSERT INTO lobsamp "
             + " (id"
             + " ,blob_col"
             + " ) VALUES "
             + " (" + lngId
             + " ,empty_blob()"
             + " )");
  // Retrieve BLOB locator
  strStatement = " SELECT blob_col"
               + " FROM   lobsamp"
               + " WHERE  id = " + lngId
               + " FOR UPDATE";  resSet = stmt.executeQuery(strStatement);
  if (resSet.next()) {
    // Get the BLOB locator and open output stream for the BLOB
    BLOB bCol = ((OracleResultSet)resSet).getBLOB(1);
    blobOutputStream = bCol.getBinaryOutputStream();
    // Open the sample file as a stream for insertion
    // into the BLOB column
    File file2Load = new File(strDirectory + strFile);
    sampleFileStream = new FileInputStream(file2Load);
    // Buffer to hold chunks of data to being written to the BLOB.
    byte[] bBuffer = new byte[bCol.getBufferSize()*NUMCHUNKS];
    // Read a chunk of data from the sample file input stream,
    // and write the chunk to the BLOB column output stream.
    // Repeat till file has been fully read.
    int intBytesRead = 0;
    // read from file until done
    while ((intBytesRead = sampleFileStream.read(bBuffer)) != -1) {
      // write to BLOB
      blobOutputStream.write(bBuffer,0,intBytesRead);
    }
    // closing the streams and committing
    sampleFileStream.close();
    blobOutputStream.close();
    dbConn.commit();
  }
} catch (Exception ex) {
  try { dbConn.rollback(); } catch (Exception e) {}
} finally {
  try { sampleFileStream.close(); } catch (Exception e) {}
  try { blobOutputStream.close(); } catch (Exception e) {}
  try { stmt.close(); } catch (Exception ! e) {}
}

-------------------------

Here is the sample:

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

public class ExonCLOB
{
  Connection con;
  Statement st,st1;
oracle.jdbc.driver.OraclePreparedStatement pstmt;
  ResultSet rs;
   public ExonCLOB()
   {
     try{
          Class.forName("oracle.jdbc.driver.OracleDriver");
          oracle.jdbc.driver.OracleConnection  con=
(oracle.jdbc.driver.OracleConnection)DriverManager.getConnection
("jdbc:oracle:thin:@a1:1521:informa","scott","tiger");
   
            File f=new File("ExonCLOB.java");
            DataInputStream dis=new DataInputStream(new 
FileInputStream(f));
            byte b1[]=new byte[(int)f.length()];
            oracle.sql.CLOB clob=new oracle.sql.CLOB(con);
            System.out.println(clob);
            OracleClobWriter writer=new OracleClobWriter
(clob,b1.length);

            System.out.println("clob Inserting .........");
            String str=dis.readLine();
            while(str!=null)
             {
                 System.out.println(str);
                writer.write(str.toCharArray(), 0, str.length());
                 str=dis.readLine();
            }
            System.out.println("Inserting .........");
            System.out.println(clob);
            System.out.println(clob.getCharacterStream());

            pstmt=(oracle.jdbc.driver.OraclePreparedStatement)
con.prepareStatement("update exclob set info=? where empid=?");
        System.out.println(pstmt);
                 pstmt.setObject(1,clob,2005);
                 pstmt.setInt(2,10);
        System.out.println(" After setting pstmt");

        int i=pstmt.executeUpdate();
        System.out.println(" After execute update");

        System.out.println(i+" records updated");      

        System.out.println("Retriving.......");
                  oracle.jdbc.driver.OracleResultSetImpl rs=
(oracle.jdbc.driver.OracleResultSetImpl)st1.executeQuery("select info 
from exclob where empid=10");
        System.out.println(rs);

                  if(rs.next())
                 {
                   System.out.println("in if");                     
                   oracle.sql.CLOB b=rs.getCLOB(1);
         Reader is=b.getCharacterStream();
                   f=new File("read.java");
                   FileOutputStream fos=new FileOutputStream(f);
                   i=is.read();
                   System.out.println("i  ="+i);
         while(i!=-1)
         {
            fos.write(i);
            i=is.read();
                      System.out.print(i);
           }// while()
                     System.out.println("retrived");
        }// if
             }catch(Exception e){ e.printStackTrace();  }

   }
   public static void main(String s[])
    {
            ExonCLOB e=new ExonCLOB();
     }
}

Try the sample and let me know the one  you have done :)

Shyam Kris

See also

• Questions with Answers #1
• Questions with Answers #2
• Questions with Answers #3
• Questions with Answers #4
• Questions with Answers #5
• Questions with Answers #6

Java Books
Java Certification, Programming, JavaBean and Object Oriented Reference Books

Return to : Java Programming Hints and Tips