Copy LONGRAW with Java

From: Michael Dimitriadis <michael.dimitriadis_at_infonova.at>
Date: Thu, 13 Dec 2001 10:04:04 +0100
Message-ID: <3c186e98$0$31972$5039e797@newsreader01.highway.telekom.at>

Hi,

I try to copy a LONGRAW-column to another LONGRAW-column in Java being inspired by Tom Kyte, but I don't get my example running because his examples copied a LONGRAW to BLOB by using Streams and I also need the other direction.
The OutputStream for the BLOB is aquired by ResultSet.GetBLOB(x).getBinaryOutputStream but there is no similar Method for a LONGRAW. To use the method UpdateBytes with a ByteArray worked in 8.1.6.2 but failed in 8.1.7.2 when the array contains more than 4000 Bytes with the message

 ORA-01461: can bind a LONG value only for insert into a LONG column

This is the code which fails in the 8.1.7.2-database:

create or replace and compile
java source named "copylong" as

import java.io.*;
import java.lang.*;
import java.sql.*;
import java.util.*;

import oracle.jdbc.driver.*;

public class copylong
{

    static String C_USER = "onoper";
    static String C_PASSWORD = "daylight";
    static String C_NET8_CONNECT_STRING =

"(description=(address=(host=127.0.0.1)(protocol=tcp)(port=1521))(connect_da ta=(sid=buck01)))";

  private static void copy_longdata(String base_tname, String target_tname, java.math.BigDecimal p_rep_id) throws Exception

{

        DriverManager.registerDriver (new OracleDriver());

        java.util.Properties info = new java.util.Properties();
        info.put ("user", C_USER);
        info.put ("password", C_PASSWORD);
        info.put ("database", C_NET8_CONNECT_STRING);

        Connection conn = DriverManager.getConnection
              ("jdbc:oracle:thin:@", info);

        conn.setAutoCommit (false);

        // Create statements
        PreparedStatement pstmt = conn.prepareStatement
          ("select length, data from "+base_tname+" where data is not null

and rep_id = "+p_rep_id);

        OracleResultSet rset_read = (OracleResultSet) pstmt.executeQuery();

        if (rset_read.next())
        {
          long new_length = rset_read.getLong(1);

          if (target_tname.equals("data"))
          {
            byte read_buffer[] = rset_read.getBLOB(2).getBytes(1, 1024000);
            rset_read.close();
            pstmt.close();

            Statement getStmt =

conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDA TABLE);
            OracleResultSet rset_write = (OracleResultSet) getStmt.executeQuery("select length, data from "+target_tname+" where rep_id = "+p_rep_id);

            if (rset_write.next())
            {
              rset_write.updateLong(1, new_length);
              rset_write.updateBytes(2, read_buffer);
              rset_write.updateRow();
            }
            rset_write.close();
            getStmt.close();
          }
          else
          {
            byte read_buffer[] = rset_read.getBytes(2);
            rset_read.close();
            pstmt.close();

            PreparedStatement setLengthStmt = conn.prepareStatement("update

"+target_tname+" set length = ?, data = empty_blob() where rep_id = "+p_rep_id);

            setLengthStmt.setLong(1, new_length);
            ResultSet rset_length = setLengthStmt.executeQuery();
            setLengthStmt.close();

            PreparedStatement getStmt = conn.prepareStatement("select data
from "+target_tname+" where rep_id = "+p_rep_id+" for update");
            OracleResultSet rset_write = (OracleResultSet)
getStmt.executeQuery();
            if (rset_write.next())
            {
              OutputStream os =
rset_write.getBLOB(1).getBinaryOutputStream();
              os.write( read_buffer, 0, read_buffer.length);
              os.close();
            }
            rset_write.close();
            getStmt.close();
          }
        }
        else
        {
          rset_read.close();
          pstmt.close();
        }
        conn.close();

    }

    public static void copy_longraw(java.math.BigDecimal p_rep_id) throws Exception

{

        copy_longdata("data", "data_raw", p_rep_id);     }

    public static void copy_blob(java.math.BigDecimal p_rep_id) throws Exception

{

        copy_longdata("data_raw", "data", p_rep_id);     }
}

Please help,
Michael Dimitriadis Received on Thu Dec 13 2001 - 03:04:04 CST