How To: Oracle Java Stored Procs get and set BLOBs to disk

From: Melroy <melroy_drego_at_mlc.com.au>
Date: 8 May 2003 23:22:25 -0700
Message-ID: <d5485443.0305082222.528a8c38_at_posting.google.com>

Isn't it wonderful: so many ask for solutions, but after heaps of suggestions the final solution/s is/are never posted..... To make up the deficiency....

Create Java Stored Proc:
import java.io.*;
import java.sql.*;

public class OraBlobSP {

	public static oracle.sql.BLOB getBlob( String id) {
		String  sqlNewRow = "INSERT INTO VILARGEOBJECTS (LOB_ID, BINOBJ,
TIME) VALUES (?,EMPTY_BLOB(),?)";
        	String sqlGetBlob = "SELECT BINOBJ FROM VILARGEOBJECTS WHERE
LOB_ID = ? FOR UPDATE";
      		boolean dbAutoCommitStatus = false;  
      		Connection cxn;
      		PreparedStatement ps;
      		oracle.sql.BLOB dbBlob = null;
      		try {
      			cxn = DriverManager.getConnection("jdbc:default:connection:");

      			dbAutoCommitStatus = cxn.getAutoCommit();
      			cxn.setAutoCommit(false);
      


      			//make new row


      			ps = cxn.prepareStatement(sqlNewRow);
      			ps.setString(1,id); 
      			ps.setString(2,id);
      			ps.executeUpdate();      
	
      			ps = cxn.prepareStatement(sqlGetBlob);
      			ps.setString(1,id);
      			ResultSet rs = ps.executeQuery();
      			rs.next();      		


      			//Oracle JDBC uses the method putBytes(int,byte[])


      			dbBlob = (oracle.sql.BLOB)rs.getBlob(1);
      			cxn.setAutoCommit(dbAutoCommitStatus);
      			
      		}
      		catch (SQLException sqle) {
      			sqle.printStackTrace();
      			throw new RuntimeException(sqle.getMessage());
      		}
      		catch (Exception ex) {
      			ex.printStackTrace();
      			throw new RuntimeException(ex.getMessage());
      		}
      		
      		return dbBlob;
		
		
	}// end savelob


	public static String updateBlob(oracle.sql.BLOB incomingBlob, String
id){
		String sqlLockRow = "SELECT BINOBJ FROM VILARGEOBJECTS WHERE LOB_ID
= ? FOR UPDATE",
			sqlSetBlob = "UPDATE VILARGEOBJECTS SET BINOBJ = ? WHERE LOB_ID =

?";         

		Connection cxn;
      		PreparedStatement ps;
      		boolean dbAutoCommitStatus = false;
      		String retVal = "FAILURE";
      		try {
      			cxn = DriverManager.getConnection("jdbc:default:connection:");
      			dbAutoCommitStatus = cxn.getAutoCommit();
      			cxn.setAutoCommit(false);
	  
      			ps = cxn.prepareStatement(sqlLockRow);
      			ps.setString(1,id);
      			ResultSet rs = ps.executeQuery();
      			rs.next();


      			//Use oracle.sql.BLOB because the java.sql.Blob interface


lacks setBytes()


      			//JDBC3 java.sql.Blob adds the method setBytes(int,byte[])


      			//Oracle JDBC uses the method putBytes(int,byte[])


      			oracle.sql.BLOB dbBlob = (oracle.sql.BLOB)rs.getBlob(1);


      			//update blob


      			ps = cxn.prepareStatement(sqlSetBlob);
      			ps.setString(2,id);      	


      			//dbBlob.putBytes(1,fileBytes);


      			ps.setBlob(1,incomingBlob);
      			retVal = "SUCCESS";
     			cxn.setAutoCommit(dbAutoCommitStatus);
     		}
     		catch (SQLException sqle) {
      			sqle.printStackTrace();
      			throw new RuntimeException(sqle.getMessage());
      		}
      		catch (Exception ex) {
      			ex.printStackTrace();
      			throw new RuntimeException(ex.getMessage());
      		}
      		return retVal;
	}// end updateBlob

}

---

Create call spec:
SQL> create or replace package orablobsp as   2 function getblob(x varchar2) return blob;   3 function updateblob(x blob, y varchar2) return varchar2;   4 end;
  5 /

Package created.

SQL> create or replace package body orablobsp as   2 function getblob(x varchar2 )return blob   3 as language java
  4 name 'OraBlobSP.getBlob(java.lang.String) return oracle.sql.BLOB';
  5 function updateblob( x blob, y varchar2) return varchar2   6 as language java
  7 name 'OraBlobSP.updateBlob(oracle.sql.BLOB, java.lang.String) return java.lang.String';
end orablobsp;

---

Create caller:

import javax.ejb.EJBException;

import java.util.Calendar;
import java.util.TimeZone;
import java.text.SimpleDateFormat;
import java.util.HashMap;

import javax.naming.InitialContext;

import javax.naming.Context;
import javax.naming.NamingException;

import java.sql.SQLException;
import java.util.Hashtable;
import java.io.InputStream;
import java.io.FileInputStream;
import java.io.File;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;

import java.io.IOException;
import java.lang.ClassNotFoundException; import oracle.jdbc.driver.OracleConnection;

import java.sql.Types;
import javax.sql.DataSource;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.CallableStatement;
import java.sql.Statement;

//import java.sql.ResultSet;                          

 public class OraLobSPCaller {

	public static final SimpleDateFormat ISO8601_TIMESTAMP_FORMATTER = 
					new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");


	public static void main(String [] args ){
		Calendar cal = Calendar.getInstance(TimeZone.getDefault());
		String insertTime = (String)

ISO8601_TIMESTAMP_FORMATTER.format(cal.getTime());

		String url = "jdbc:oracle:thin:_at_panther:1524:shapedev";
		String username = "melroy";
		String password = "password";
		CallableStatement cstmt = null, cstmt1 = null;	
		Connection conn  = null;
		FileInputStream stream = null;
		try {
			DriverManager.registerDriver( new

oracle.jdbc.driver.OracleDriver());

                        File binaryFile = new File("c:/temp" + System.getProperty("file.separator") + args[0]);

			System.out.println("Binary file size: " + binaryFile.length());
			stream = new FileInputStream(binaryFile);
			System.out.println("Got InputStream from File:" + args[0]);
			conn = DriverManager.getConnection(url, username, password);
			conn.setAutoCommit(false);
			System.out.println("Got Connection ");				
			cstmt = conn.prepareCall("{? = call orablobsp.getblob(?)}");
			System.out.println("Creating CallableStatement ");
			cstmt.registerOutParameter(1, Types.BLOB);
			cstmt.setString(2, insertTime); 
			cstmt.executeQuery();
			System.out.println("Executed GetBLOB Query");
			oracle.sql.BLOB theBlob = (oracle.sql.BLOB)cstmt.getBlob(1);
			System.out.println("Got Blob Back");
			cstmt.close();

			System.out.println("LOB_ID: " + insertTime);

			int available = 0;
			// While more bytes in stream.....		
			System.out.println("Starting loop: Stream size: " +

stream.available());

				//////////////////////////////////////////////
		
			int bufferSize = theBlob.getBufferSize();
		//	int blobLength = new Long(theBlob.length()).intValue();
			int blobLength = 0;

		//	sbRetVal.append(" Blob Length Before Append: " + blobLength);
			byte[] ioBuffer= new byte[bufferSize];
			int bytesRead = -1;
			int cTotal = 0;
		//	sbRetVal.append(" Starting Append....");
            	while((bytesRead = stream.read(ioBuffer, 0,
ioBuffer.length)) != -1){
				if(bytesRead < ioBuffer.length) {
					byte[] dynBytes = new byte[bytesRead];
					for(int i = 0 ; i<bytesRead; i++) {
						dynBytes[i] = ioBuffer[i];
					}
					theBlob.putBytes(blobLength+1, dynBytes);
					cTotal = cTotal + bytesRead;
					System.out.println("Breaking out of last loop set");
					break;
					
				}

					
                		theBlob.putBytes(blobLength+1, ioBuffer);
				cTotal = cTotal + bytesRead;
				blobLength = blobLength + bytesRead;
				System.out.println(" In Append -Blob Length: " + blobLength);
			}
			System.out.println(" TotalSize of blob appended: " + cTotal);
            	stream.close();
			
				cstmt1 = conn.prepareCall("{? = call orablobsp.updateblob(?,?)}");
				System.out.println("Creating CallableStatement ");
				cstmt1.registerOutParameter(1, Types.VARCHAR);
				cstmt1.setBlob(2, theBlob); 
				cstmt1.setString(3,insertTime);
				cstmt1.executeQuery();
				System.out.println("Executed GetBLOB Query");
				String retVal = cstmt1.getString(1);
				cstmt.close();
			
				System.out.println("Return VALUE: " + retVal );			
				System.out.println("LOB_ID: " + insertTime);
			conn.setAutoCommit(true);
			
		
		}
		catch (SQLException sqle ) {
			sqle.printStackTrace();
			System.out.println("ERROR MESSAGE: " + sqle.getMessage());
		}
		catch(IOException ioe) {
			ioe.printStackTrace();
			System.out.println("ERROR MESSAGE: " + ioe.getMessage());

		}
		catch (Exception ex) {
			ex.printStackTrace();
		}//end try-catch


		finally {
			try {
				cstmt.close();
				conn.close();
				stream.close();
			}
			catch (IOException ex) {
				ex.printStackTrace();
			}
			catch (SQLException e) {
				e.printStackTrace();
				System.out.println("ERROR MESSAGE: " + e.getMessage());

			}// end try
		}// end finally
	}//end main
 

}//end dbcall

---

Create a getter:

import javax.ejb.EJBException;

import java.util.Calendar;
import java.util.TimeZone;
import java.text.SimpleDateFormat;
import java.util.HashMap;

import javax.naming.InitialContext;

import javax.naming.Context;
import javax.naming.NamingException;

import java.sql.SQLException;
import java.util.Hashtable;
import java.io.InputStream;
import java.io.FileInputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;

import java.io.IOException;
import java.lang.ClassNotFoundException; import oracle.jdbc.driver.OracleConnection; import oracle.sql.BLOB;
import java.sql.Blob;

import java.sql.Types;
import javax.sql.DataSource;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.CallableStatement;
import java.sql.Statement;

//import java.sql.ResultSet;                          

 public class dbgetplsql {

	public static final SimpleDateFormat ISO8601_TIMESTAMP_FORMATTER = 
					new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");


	public static void main(String [] args ){
		Calendar cal = Calendar.getInstance(TimeZone.getDefault());
		String insertTime = (String)

ISO8601_TIMESTAMP_FORMATTER.format(cal.getTime());

		String url = "jdbc:oracle:thin:_at_panther:1524:shapedev";
		String username = "melroy";
		String password = "password";
		CallableStatement cstmt = null;	
		Connection conn  = null;
		InputStream blobStream = null;
		FileOutputStream fileOutStream = null;
		
		try {
			DriverManager.registerDriver( new

oracle.jdbc.driver.OracleDriver());

                        File binaryFile = new File("c:/temp" + System.getProperty("file.separator") + args[1]);                         

                        conn = DriverManager.getConnection(url, username, password);

                        conn.setAutoCommit(false);

                        System.out.println("Got Connection ");

			cstmt = conn.prepareCall("{? = call LOBget(?)}");
			System.out.println("Creating CallableStatement ");
			cstmt.registerOutParameter(1, Types.BLOB);			
			cstmt.setString(2, args[0]); 			
			cstmt.executeQuery();
			System.out.println("Executed CallableStatement");
			BLOB inBlob = (oracle.sql.BLOB)cstmt.getBlob(1);
			//Blob inBlob = cstmt.getBlob(1);
			System.out.println("got BLOB from cstmt");

			// Open a stream to read the Blob data
      			blobStream = inBlob.getBinaryStream();
			System.out.println("got BLOBStream from blob");



      			// Open a file stream to save the Blob data


      			fileOutStream = new FileOutputStream(binaryFile);



      			// Read from the Blob data input stream, and write to the


file output


      			// stream


      			byte[] buffer = new byte[inBlob.getBufferSize()]; // buffer
holding bytes to be transferred


      			//byte[] buffer = new byte[24]; // buffer holding bytes to be


transferred
      			int nbytes = 0;  // Number of bytes read
      			while( (nbytes = blobStream.read(buffer)) != -1 ){ // Read
from Blob stream
        			fileOutStream.write(buffer, 0, nbytes);     // Write to
file stream
        			fileOutStream.flush();
				System.out.println("Bytes: " + nbytes);
        		}
			


      			// Flush and close the streams      			


      			fileOutStream.close();
      			blobStream.close();
			
		}
		/*
		catch (SQLException sqle ) {
			sqle.printStackTrace();
			System.out.println("ERROR MESSAGE: " + sqle.getMessage());
		}
		*/		
		/*catch(ClassNotFoundException cnfe) {
      			cnfe.printStackTrace();
      		}*/
		catch(IOException ioe) {
			ioe.printStackTrace();
			System.out.println("ERROR MESSAGE: " + ioe.getMessage());

		}
		catch (Exception ex) {
			ex.printStackTrace();
		}//end try-catch


		finally {
			try {
				fileOutStream.flush();
      				fileOutStream.close();
      				blobStream.close();
			}
			catch (IOException ex) {
				ex.printStackTrace();
			}
			/*
			catch (SQLException e) {
				e.printStackTrace();
				System.out.println("ERROR MESSAGE: " + e.getMessage());

			}// end try
 */
		}// end finally
	}//end main
 

}//end dbcall

All the best. Received on Fri May 09 2003 - 08:22:25 CEST