Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2009
    Posts
    16
    Rep Power
    0

    Default Retrieving image stored as BLOB object in oracle

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

    here is my code....

    Java 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....
    Last edited by Fubarable; 01-11-2010 at 03:20 PM. Reason: code tags added

  2. #2
    r035198x is offline Senior Member
    Join Date
    Aug 2009
    Posts
    2,388
    Rep Power
    7

    Default

    Do
    Java Code:
    e.printStackTrace();
    in the catch blocks and post the results.

  3. #3
    travishein's Avatar
    travishein is offline Senior Member
    Join Date
    Sep 2009
    Location
    Canada
    Posts
    684
    Rep Power
    5

    Default

    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,

    Java Code:
    jdbc:oracle:thin:@.....

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,817
    Rep Power
    19

    Default

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

  5. #5
    raqman is offline Member
    Join Date
    Sep 2009
    Posts
    37
    Rep Power
    0

    Default

    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;
    }
    RAQ Report: free Java reporting tool.

  6. #6
    r035198x is offline Senior Member
    Join Date
    Aug 2009
    Posts
    2,388
    Rep Power
    7

    Default

    Quote Originally Posted by raqman View Post
    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.

  7. #7
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,817
    Rep Power
    19

    Default

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

  8. #8
    abollm is offline Member
    Join Date
    Jan 2010
    Location
    D-Munich
    Posts
    7
    Rep Power
    0

    Default

    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.

    Java Code:
    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!

  9. #9
    r035198x is offline Senior Member
    Join Date
    Aug 2009
    Posts
    2,388
    Rep Power
    7

    Default

    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.

  10. #10
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,817
    Rep Power
    19

    Default

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

  11. #11
    abollm is offline Member
    Join Date
    Jan 2010
    Location
    D-Munich
    Posts
    7
    Rep Power
    0

    Default

    Quote Originally Posted by r035198x View Post
    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

  12. #12
    r035198x is offline Senior Member
    Join Date
    Aug 2009
    Posts
    2,388
    Rep Power
    7

    Default

    Quote Originally Posted by abollm View Post
    #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.



    Quote Originally Posted by abollm View Post
    ..
    #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.

Similar Threads

  1. Replies: 3
    Last Post: 10-14-2009, 09:32 AM
  2. How to call Oracle stored procedure from Java?
    By parag_narkhede in forum New To Java
    Replies: 4
    Last Post: 10-07-2009, 07:50 AM
  3. How to insert java Object in oracle database
    By Thilkumar82 in forum Advanced Java
    Replies: 9
    Last Post: 08-13-2008, 11:33 AM
  4. JDO - Retrieving a persisted object
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 03-17-2008, 07:46 AM
  5. Problems updating blob columns on Oracle 9
    By ljustiniano in forum JDBC
    Replies: 0
    Last Post: 08-14-2007, 06:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •