I am developing a document repository on a Solaris w/8.1.6. The initial load is a fixed set of documents which are currently in a v7.3.4.5 as a LONG RAW. Export, Import, TO_LOB and I have my BLOBS. The tricky part is moving new documents created in the remote v7 LONG RAWs. I want the 8.1.6 to retrieve the LONG RAWs via link(s) (up to 4 remote sites) and load into a BLOB.
The docs are 500k on average, but can get up to 8M. I've seen a lot on streaming to and from OS files, but I prefer to cut out the file and move it stream to stream (assuming a stream is necessary - big server.)
Thoughts, opinions, and please some code!!!
We can do this but we'll need 2 connections -- one to read the LONG RAW as a stream and one to write the BLOB as a stream. The reason we need 2 connections is:
<quote>
Streaming Data Precautions
This section describes some of the precautions you must take to ensure that you do not accidentally discard or lose your stream data. The drivers automatically discard stream data if you perform any JDBC operation that communicates with the
database, other than reading the current stream. Two common precautions are described in the following sections:
...
</quote>
see
</code>
http://docs.oracle.com/cd/A81042_01/DOC/java.816/a81354/basic4.htm#1007114 <code>
for more info. Ok, so since we know we need 2 connections -- one to read and one to write, it can look like this. I have a table
IMAGE( id number, image long raw );
In the 7.3 instance. I have a table
BLOB_DEMO( x number, y blob )
in the 8.1 instance. I have a database link from 8.1 to 7.3 set up. The java code could look like this:
import java.io.*;
import java.lang.*;
import java.sql.*;
import java.util.*;
import oracle.jdbc.driver.*;
public class movelr
{
public static void main(String[] args) throws Exception
{
int length;
DriverManager.registerDriver (new OracleDriver());
Connection conn73 = DriverManager.getConnection
("jdbc:oracle:oci8:@ora8idev","scott","tiger");
Connection conn81 = DriverManager.getConnection
("jdbc:oracle:oci8:@ora8idev","scott","tiger");
conn73.setAutoCommit (false);
conn81.setAutoCommit (false);
System.out.println( "connected..." );
// Create statements
PreparedStatement pstmt = conn73.prepareStatement
("select image from image@slack" );
System.out.println( "prepared statement for v7.3..." );
PreparedStatement insertStmt = conn81.prepareStatement
("insert into blob_demo ( x, y ) " +
"values ( ?, empty_blob() )");
System.out.println
( "prepared statement for v8.x BLOB insert..." );
PreparedStatement getStmt = conn81.prepareStatement
("select y from blob_demo where x = ? for update");
System.out.println
( "prepared statement for v8.x BLOB select..." );
ResultSet rset73 = pstmt.executeQuery();
System.out.println
( "executed statement for v7.3 select..." );
for( int i = 0; rset73.next(); i++ )
{
System.out.println( "looping..." );
InputStream is = rset73.getBinaryStream(1);
System.out.println( "Got v7.3 LONG RAW stream...");
// Insert a new row and get select
// back the BLOB column
insertStmt.setInt(1, i);
insertStmt.executeUpdate();
getStmt.setInt(1, i);
OracleResultSet rset81 =
(OracleResultSet) getStmt.executeQuery();
rset81.next();
OutputStream os =
rset81.getBLOB(1).getBinaryOutputStream();
System.out.println( "Got v8.x BLOB stream..." );
System.out.println( "Moving data from IS to OS...");
byte buffer[] = new byte[1024];
while ((length = is.read (buffer,0,1024)) != -1)
{
os.write( buffer, 0, length );
}
is.close();
os.close();
rset81.close();
}
rset73.close();
insertStmt.close();
getStmt.close();
pstmt.close();
conn81.commit();
conn73.close();
conn81.close();
}
}