Copy image to BLOB from client pc aka Java function in Oracle

Question

Hi guys, I've been stuck with this for past two days. I've go java function stored in Oracle system which is supposed to copy image from local drive do remote database and store it in BLOB - it's called CopyBLOB and looks like this:

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

public class CopyBLOB 
{
  static int id;
  static String  fileName = null;
  static Connection conn = null;  

  public CopyBLOB(int idz, String f) 
  {
    id       = idz;
    fileName   = f;
  }

   public static void copy(int ident, String path) throws SQLException, FileNotFoundException 
   {
       CopyBLOB cpB = new CopyBLOB(ident, path);
       cpB.getConnection();
       cpB.callUpdate(id, fileName);
   }

    public void getConnection() throws SQLException
    {    
     DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
     try 
     {     
       conn = DriverManager.getConnection("jdbc:oracle:thin:@oraserv.ms.mff.cuni.cz:1521:db", "xxx", "xxx");
     } 
     catch (SQLException sqlex) 
     {
         System.out.println("SQLException while getting db connection: "+sqlex);
         if (conn != null)  conn.close();
     } 
     catch (Exception ex) 
     {
         System.out.println("Exception while getting db connection: "+ex);
         if (conn != null) conn.close();
     }
    }

    public void callUpdate(int id, String file ) throws SQLException, FileNotFoundException 
    {
      CallableStatement cs = null;
      try 
      {  
        conn.setAutoCommit(false);
        File f = new File(file);
        FileInputStream fin = new FileInputStream(f);
        cs = (CallableStatement) conn.prepareCall( "begin add_image(?,?); end;" );
        cs.setInt(1, id );
        cs.setBinaryStream(2, fin, (int) f.length());
        cs.execute();
        conn.setAutoCommit(true);        
      } 
      catch ( SQLException sqlex ) 
      {
            System.out.println("SQLException in callUpdateUsingStream method of given status : " + sqlex.getMessage() );
      } 
      catch ( FileNotFoundException fnex ) 
      {
            System.out.println("FileNotFoundException in callUpdateUsingStream method of given status : " + fnex.getMessage() );
      } 
      finally 
      {
          try 
          {          
            if (cs != null)  cs.close();
            if (conn != null) conn.close();
          } 
          catch ( Exception ex ) 
          {
            System.out.println("Some exception in callUpdateUsingStream method of given status : " + ex.getMessage(  ) );
          }
      }
    }
}

The wrapper function is defined in package "MyPackage" as folows:

  procedure image_adder( id varchar2, path varchar2 )  
  AS
    language java name 'CopyBLOB.copy(java.lang.String, java.lang.String)';

And the inserting function called image_add is as simple as this:

procedure add_image( id numeric(10), pic blob)
  AS 

  BEGIN
    insert into pictures values (seq_pic.nextval, id, pic);
  END add_image;

Now the problem: When I type

call MyPackage.image_adder(1, 'd:\samples\img.jpg');

I get the ORA-29531 Error: No method copy in class CopyBLOB. Can you help me, please?

Answer 1

The method in your class has this signature:

public static void copy(int ident, String path)

But in your Java Stored Procedure you have specified this signature:

'CopyBLOB.copy(java.lang.String, java.lang.String)'

I think if you change the first argument to java,lang.Integer your problem should resolve itself. You should probably change the datatype of the ID parameter in the IMAGE_ADDER() procedure as well.

edit

"Any ideas how to upload local files?"

Not unreasonably, the database can only interact with files which are visible to its server. Generally that limits matters to files and directories which are physically on the same box, unless the network admin has mapped some remote drives.

Transferring files from a local PC drive to a server is really a client i.e. application issue, it isn't the sort of thing the database should really get involved with.

I know that isn't what you were hoping to hear. If you really want to drive the file uploading from teh database, then the mechanism remains the same whenever we want to transfer files across a network: FTP. Tim Hall has published a PL/SQL implementation for FTP on his Oracle-Base site. Find out more.

"long as the file is smaler than 2000B (WTF?)"

That is suspiciously close to the BINARY CHAR limit (2000). In older versions of Oracle we had to use a two-step process: insert a placeholder and then issue an update. Something like this:

  procedure add_image( id numeric(10), pic blob) 
  AS       
  BEGIN 
      insert into pictures 
          values (seq_pic.nextval, id, empty_blob()); 
      update pictures 
      set col_pic = pic
      where id = seq_pic.currval;
  END add_image;