Retrieving image stored as BLOB object in oracle

[venkateshcoolmoon]

hi friend..i had stored an image as blob object..but i was getting excetion while retrieving...

here is my code....

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.io.*;

import java.sql.*;



public class BlobTest {

 public void insertBlob(String imageId, String fileName) {
   Connection conn = null;
   try 
   {
	    conn = getConnection();
	    System.out.println("(browse.java) connection string is"+conn);
	    PreparedStatement ps = conn.prepareStatement("INSERT INTO images VALUES(?,?)");
		System.out.println("(browse.java) prepare statement object is"+ps);
		File file =new File("Z:/codes/correct/EndUser/"+fileName);
		FileInputStream fs = new FileInputStream(file);
		byte blob[]=new byte[(byte)file.length()];
		fs.read(blob);
		ps.setString(1,imageId);
		ps.setBytes(2, blob);
		System.out.println("(browse.java)length of picture is"+fs.available());
		int i = ps.executeUpdate();
		System.out.println("image inserted successfully"+i);
   } 
   catch (Exception e) 
   {
     e.printStackTrace();
   }
 }



 private Connection getConnection() throws ClassNotFoundException, SQLException
 {
	 Connection conn=null;
	 try
	 {
	   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
       conn = DriverManager.getConnection("jdbc:odbc:proj","o6it37","anenth");
  
   return conn;
	 }
	 catch(Exception e)
	 {
		 System.out.println("erer "+e);
	 }
	 return conn;
 }

 
 public void getBlob()
 {

     String sql1 = "select imageid,image from images where imageid='1'";
     System.out.println("Sdsdsdsd");
     ResultSet rs;
     try
     {
    	 System.out.println("Sdsdsdsd");
    	 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
    	 System.out.println("Sdsdsdsd");
    	 Connection con = DriverManager.getConnection("jdbc:odbc:proj","o6it37","anenth");
    	 System.out.println("Sdsdsdsd4");
    	 PreparedStatement pstmt = con.prepareStatement("select image from images");
    	 System.out.println("Sdsdsdsd5");
    // pstmt.setInt(1,70);
    	 rs = pstmt.executeQuery();
     Blob blob=null;
     while(rs.next())
     {
         blob = (rs).getBlob(1);
     }
     rs.close();
     InputStream blobStream = blob.getBinaryStream();
     System.out.println("blob length : " + blob.length());
     FileOutputStream fileOutStream = new FileOutputStream("z:\\abc.jpg");
     byte[] buffer = new byte[10];
     int nbytes = 0;
     while ((nbytes = blobStream.read(buffer))!= -1)
     fileOutStream.write(buffer,0,nbytes);
     fileOutStream.flush();
     fileOutStream.close();
     blobStream.close();
     pstmt.close();
     con.commit();
     con.close();
     }
     catch(Exception e)
     {
    	 System.out.println("Exception :  : "+e);
     }
 }
 
 public static void main(String[] args)
 {
   BlobTest blobTest = new BlobTest();
   blobTest.insertBlob("1", "VOTE.jpg");
  blobTest.getBlob();

 }
}

plz ..
i am getting o/p as

OUTPUT-----


(browse.java) connection string issun.jdbc.odbc.JdbcOdbcConnection@156ee8e
(browse.java) prepare statement object issun.jdbc.odbc.JdbcOdbcPreparedStatement@6ca1c
(browse.java)length of picture is173056
image inserted successfully1
Sdsdsdsd
Sdsdsdsd
Sdsdsdsd
Sdsdsdsd4
Exception : : java.sql.SQLException: General error


plz any one help me....

[r035198x]

Do

e.printStackTrace();

in the catch blocks and post the results.

[travishein]

Right, the jdbc odbc bridge driver is not very useful for anything other than basic SQL selects and simple updates of standard sql types. the BLOB has always been kind of thing that is handled differently in the low-level details of each database driver.

Perhaps, try to use the (in this case, oracle) database specific JDBC connection URL, instead of the jdbc odbc one.

for example,

jdbc:oracle:thin:@.....

[Tolls]

Also use the setBinaryStream for saving, and getBinaryStream for getting it back.

[raqman]

Assuming :

you are using the Microsoft client (System.Data.OracleClient).
you have a proper OracleConnection instance (connection).
you have an OracleCommand ready (command, based on SELECT my_blob FROM my_table WHERE id=xx).
This should go like this :

using (OracleDataReader odr=command.ExecuteReader())
{
reader.Read();

if (!dr.IsDBNull(0))
using (Stream s=(Stream)dr.GetOracleValue(0))
using (Image image=Image.FromStream(s))
return Copy(image);
}
where Copy is

public static Image Copy(Image original)
{
Image ret=new Bitmap(original.Width, original.Height);
using (Graphics g=Graphics.FromImage(ret))
{
g.DrawImageUnscaled(original, 0, 0);
g.Save();
}

return ret;
}

[r035198x]

Assuming :

you are using the Microsoft client (System.Data.OracleClient).
...

Now please go and wash your mouth for posting C# code in a Java forum.

[Tolls]

Oh, is *that* what that is!
I was wondering...

[abollm]

Below you find a very rudimentary example. You have to substitute some values (file name, login parameters and so on). This example should work. Before executing the Java program you have to create the database table _and_ the sequence in your Oracle database.

package jdbc.ora;

import java.sql.*;
import java.io.*;

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

/*
 *   A quick & dirty, rudimentary example of inserting a file into a BLOB column, reading the BLOB and
 *   writing it into a file
 *   
 *   Oracle version 10.2.0.4
 *   Author: abollm
 */

public class TestInsertingWritingBlob {
	
	final static int bBufLen = 4 * 8192;
	
	static Connection conn = null;
	static String outFile = "c:/Temp/luftbild-ipw.jpg_new.jpg";
	
	public static void main(String args[]) throws SQLException,
			FileNotFoundException, IOException {
		DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
		conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@host:1521:db", "scott", "tiger");
		conn.setAutoCommit(false);
		Statement stmt = conn.createStatement();
		stmt.execute("delete from demo");
		System.out.println("Deleted from demo ...");

		int seq = 0;
		ResultSet rset0 = stmt.executeQuery("select demo_seq.nextval from dual");
        System.out.println("Executed Query 0 ...");
        if (rset0.next()) {
			seq = ((OracleResultSet) rset0).getInt(1);
			System.out.println("Fetched sequence value: "+seq);
        }
		
		stmt.execute("insert into demo (id,theBlob) values ("+seq+",empty_blob())");
		System.out.println( "Inserted into demo" );

		conn.commit();
		System.out.println("committed");
        
		ResultSet rset1 = stmt
				.executeQuery("SELECT theBlob FROM  demo where id ="+seq+" FOR UPDATE");
		System.out.println("Executed Query 1 ...");

		if (rset1.next()) {
			System.out.println("Fetched row ...");

			BLOB l_mapBLOB = ((OracleResultSet) rset1).getBLOB(1);

			File binaryFile = new File("c:\\Temp\\luftbild-ipw.jpg");
			FileInputStream instream = new FileInputStream(binaryFile);

			int chunk = 32000;

			System.out.println("Chunk = " + chunk);

			byte[] l_buffer = new byte[chunk];

			int l_nread = 0;

			OracleCallableStatement cstmt = (OracleCallableStatement) conn
					.prepareCall("begin dbms_lob.writeappend( :1, :2, :3 ); end;");

			cstmt.registerOutParameter(1, OracleTypes.BLOB);
			while ((l_nread = instream.read(l_buffer)) != -1) {
				cstmt.setBLOB(1, l_mapBLOB);
				cstmt.setInt(2, l_nread);
				cstmt.setBytes(3, l_buffer);

				cstmt.executeUpdate();

				l_mapBLOB = cstmt.getBLOB(1);
			}
			instream.close();
			
			FileOutputStream fileOutStream = new FileOutputStream(outFile);
			//
			readFromBlob(l_mapBLOB, fileOutStream);
			
			//

			conn.commit();
			rset1.close();
			stmt.close();

		}
	}
	//
    public static long readFromBlob(oracle.sql.BLOB blob, OutputStream out)
    throws SQLException, IOException {
      InputStream in = blob.getBinaryStream();
      int length = -1;
      long read = 0;
      byte[] buf = new byte[bBufLen];
      while ((length = in.read(buf)) != -1) {
          out.write(buf, 0, length);
          read += length;
      }
      in.close();
      return read;
  }

/*    public long writeBlobToFile(oracle.sql.BLOB blob)
    throws IOException, SQLException {
      long wrote = 0;
      OutputStream fwriter = new FileOutputStream(outFile);
      wrote = readFromBlob(blob, fwriter);
      fwriter.close();
      conn.commit();
      //conn.close();
      return wrote;
  }*/
	
}

Good luck!

[r035198x]

1.) The point of using JDBC is so that you don't code to specific implementation classes. Keep it flexible and code to the interface.
2.) If an exception is thrown your connections are never closed. That is A Bad Thing.

[Tolls]

Which is why the usual method is to use the BinaryStream methods. Someone might have mentioned this before...;)

[abollm]

1.) The point of using JDBC is so that you don't code to specific implementation classes. Keep it flexible and code to the interface.
2.) If an exception is thrown your connections are never closed. That is A Bad Thing.

#1: Not always. E.g., if you code for Oracle RDBMS you need sometimes "specific implementation classes" for reasons of better performance, incompatibilities and so on.
#2: Right, but -> Q&D example

[r035198x]

#1: Not always. E.g., if you code for Oracle RDBMS you need sometimes "specific implementation classes" for reasons of better performance, incompatibilities and so on.
...

You would abstract that into some lower level layer. The OP's problem does not require coupling those classes and doing so will not help the OP become a better programmer.

..
#2: Right, but -> Q&D example

I don't know what you mean by that by if it means quick and dirty example then
1.) Please write full words so people don't have to guess what you mean.
2.) Quick and dirty examples are the reason why there is a lot of bad code being written.