Solved

Unable to insert big blob data into table

Posted on 2003-10-30
11
3,803 Views
Last Modified: 2008-03-03
Dear Experts,
I'm trying to select a list of record from one table and trying to insert them into another table.
During the process, all big blob size can not be inserted, and the following error came out :
Data size bigger than max size for this type: 1653752

The following is my java code.

/*
 * blob.java
 *
 * Created on October 22, 2003, 4:06 PM
 */

/**
 *
 * @author  ffn6dq
 */
import java.sql.*;
import java.io.*;
import java.lang.*;
import oracle.sql.*;

public class blob {
    Connection conn;
    Connection connActionRe;
    String oracleConnString;
    int i = 0;
    byte[] allBytesInBlob;
    /** Creates a new instance of blob */
    public blob() {
        byte[] BytesInBlob = runGetBLOB();
    }
   
    public byte[] runGetBLOB()
    {
         try
         {  
             byte[] allBytesInBlob;
             dbConnect();
             // Prepare a Statement:
             PreparedStatement stmnt = conn.prepareStatement("select attachment_file,ATTACHMENT_ID from qa_attachment");

             // Execute
             ResultSet rs = stmnt.executeQuery();
             System.out.println("test");
             while(rs.next())
             {
                try
                {
                   i = i + 1;
                   // Get as a BLOB
                   String s = rs.getString(2);
                   System.out.println("AtthID : " + s);
                   Blob aBlob = rs.getBlob(1);
                   
                   System.out.println("length : " + aBlob.length());
                   allBytesInBlob = aBlob.getBytes(1, (int) aBlob.length());
                   
                   System.out.println("bytes length : " + allBytesInBlob);
                   insertBlob(allBytesInBlob);
                   
                }
                catch(Exception ex)
                {
                   // The driver could not handle this as a BLOB...
                   // Fallback to default (and slower) byte[] handling
                   byte[] bytes = rs.getBytes(1);
                }
             }

           // Close resources
           rs.close();
           stmnt.close();
           dbClose();
           
         }
         catch(Exception ex)
         {
           //this.log("Error when trying to read BLOB: " + ex.getMessage());
             System.out.println("Error when trying to read BLOB: " + ex.getMessage());
         }
         return allBytesInBlob;
    }
   
    public void insertBlob(byte[] my_byte_array){
        try{
           
            String strSQL = "";
            strSQL = "INSERT INTO QA_ATTACHMENT (ATTACHMENT_ID, ATTACHMENT_FILE, ACTION_NUMBER) " +
                     "VALUES (?,?,?)";
            dbConnectARe();
            PreparedStatement pstmt = connActionRe.prepareStatement(strSQL);
            String stri = Integer.toString(i);
            pstmt.setString(1,stri);
            pstmt.setBytes(2,my_byte_array);
            pstmt.setString(3,"1");
            pstmt.executeUpdate();
            dbCloseARe();
        }catch(Exception ex){
            System.out.println("Error : " + ex.getMessage());
        }
    }

   
    public void dbConnect() throws SQLException, IOException {
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        conn = DriverManager.getConnection("jdbc:oracle:thin:@onimqs2.onsemi.com:1521:dbonimqs","actionre","actionre");
    }
   
    public void dbConnectARe() throws SQLException, IOException {
        oracleConnString = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.242.65.222)(PORT=1521))(CONNECT_DATA=(SID=myondb1)))";
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        connActionRe = DriverManager.getConnection(oracleConnString ,"actionre", "actionre");
    }
   
    public void dbClose() throws SQLException, IOException {
        conn.close();
    }
   
     public void dbCloseARe() throws SQLException, IOException {
        connActionRe.close();
    }
 
    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) throws SQLException, IOException {
        blob myBlob = new blob();
    }
   
}


I hope experts could help me.
Thanks,
Nasa
0
Comment
Question by:nasarudin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 9

Expert Comment

by:yongsing
ID: 9655219
>> Data size bigger than max size for this type: 1653752

That's the problem. The data that you are trying to insert is bigger than the maximum size of the column. You will have to alter the column to make it bigger.
0
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 9655535
I don't know what are the different blob objects in oracle are available. But you need to use object similar to longblob(MySQL) for your column.

blob and and shortblob of MySQL has limitations. and i think the same situation may be3 there for other database softwares also. so check the oracle help and identify the object similar to longbob
0
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 9655573
And also by default blob,clob and bfile of oracle supports upto 4 gb. please check your column type in your second table first. 4Gb means it is more than exception value.

Regards
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 35

Expert Comment

by:TimYates
ID: 9656531
This is the age old problem with Oracle...

Blobs and JDBC aren't really transparent...

You have to follow these steps:

http://www.wamoz.com/JDBC_and_Oracle_LOB.asp

basically, you do 2 calls, one to set your non-blob data, and set the blob to empty_blob()

then another one to get a handle back from this empty blob

you then stream your data into this handle...

Hope this gets you in the right direction :-)

Tim.
0
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 9656546
Hi Tim,
I think the article is removed from this http://www.wamoz.com/JDBC_and_Oracle_LOB.asp link. Raising page not found error


do you have info related to that

Regards,
Sudha
0
 
LVL 35

Accepted Solution

by:
TimYates earned 100 total points
ID: 9656561
0
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 9656569
Thank You:)
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9656571
:-)
0
 

Author Comment

by:nasarudin
ID: 9668508
Tim,
I change my code and follow the artikel like what you said, but I got :
ORA-22920 : row containing the LOB value is not locked

Below is my code :

/*
 * blob.java
 *
 * Created on October 22, 2003, 4:06 PM
 */

/**
 *
 * @author  ffn6dq
 */
import java.sql.*;
import java.io.*;
import java.lang.*;
import oracle.sql.*;

public class blob {
    Connection conn;
    Connection connActionRe;
    String oracleConnString;
    int i = 0;
    byte[] allBytesInBlob;
    /** Creates a new instance of blob */
    public blob() {
        byte[] BytesInBlob = runGetBLOB();
    }
   
    public byte[] runGetBLOB()
    {
         try
         {  
             byte[] allBytesInBlob;
             dbConnect();
             // Prepare a Statement:
             PreparedStatement stmnt = conn.prepareStatement("select attachment_file,ATTACHMENT_ID from qa_attachment1 where rownum=1");

             // Execute
             ResultSet rs = stmnt.executeQuery();
             System.out.println("test");
             while(rs.next())
             {
                try
                {
                   i = i + 1;
                   // Get as a BLOB
                   String s = rs.getString(2);
                   System.out.println("AtthID : " + s);
                   Blob aBlob = rs.getBlob(1);
                   
                   System.out.println("length : " + aBlob.length());
                   allBytesInBlob = aBlob.getBytes(1, (int) aBlob.length());
                   
                   System.out.println("bytes length : " + allBytesInBlob);
                   //insertBlob(allBytesInBlob);
                   OracleBlob(aBlob);
                   
                }
                catch(Exception ex)
                {
                   // The driver could not handle this as a BLOB...
                   // Fallback to default (and slower) byte[] handling
                   byte[] bytes = rs.getBytes(1);
                }
             }

           // Close resources
           rs.close();
           stmnt.close();
           dbClose();
           
         }
         catch(Exception ex)
         {
           //this.log("Error when trying to read BLOB: " + ex.getMessage());
             System.out.println("Error when trying to read BLOB: " + ex.getMessage());
         }
         return allBytesInBlob;
    }
   
    public void insertBlob(byte[] my_byte_array){
        try{
           
            String strSQL = "";
            strSQL = "INSERT INTO QA_ATTACHMENT1 (ATTACHMENT_ID, ATTACHMENT_FILE, ACTION_NUMBER) " +
                     "VALUES (?,?,?)";
            dbConnectARe();
            PreparedStatement pstmt = connActionRe.prepareStatement(strSQL);
            String stri = Integer.toString(i);
            pstmt.setString(1,stri);
            pstmt.setBytes(2,my_byte_array);
            pstmt.setString(3,"1");
            pstmt.executeUpdate();
            dbCloseARe();
        }catch(Exception ex){
            System.out.println("Error : " + ex.getMessage());
        }
    }
   
    public void OracleBlob(Blob myBlob){
        try{
        PreparedStatement ps;
           
            String
        sqlNewRow = "INSERT INTO QA_ATTACHMENT1 (ATTACHMENT_ID,ATTACHMENT_FILE, ACTION_NUMBER) VALUES (?,EMPTY_BLOB(),?)",
        sqlLockRow = "SELECT ATTACHMENT_FILE FROM QA_ATTACHMENT1 WHERE ATTACHMENT_ID = ? FOR UPDATE",
        sqlSetBlob = "UPDATE QA_ATTACHMENT1 SET ATTACHMENT_FILE = ? WHERE ATTACHMENT_ID = ?";
       
           
        dbConnectARe();
       
        connActionRe.setAutoCommit(false);
      //make new row
      String stri = Integer.toString(i);
      String acno = "nasa_" + stri;
      ps = connActionRe.prepareStatement(sqlNewRow);
      ps.setString(1,stri);
      ps.setString(2,acno);
      ps.executeUpdate();
      //lock new row
      ps = connActionRe.prepareStatement(sqlLockRow);
      ps.setString(1,"1");
      ResultSet rs = ps.executeQuery();
      rs.next();
     
      oracle.sql.BLOB dbBlob = (oracle.sql.BLOB)myBlob;
     
       //update blob
      ps = connActionRe.prepareStatement(sqlSetBlob);
      ps.setString(2,stri);
      dbBlob.putBytes(1,myBlob.getBytes(1,(int) myBlob.length()));

      ps.setBlob(1,dbBlob);
      connActionRe.commit();
      System.out.print("Rows affected:");
      System.out.println(ps.executeUpdate());
      dbCloseARe();  
 
        }catch(Exception ex){
            System.out.println("Error : " + ex.getMessage());
        }
    }
   
   

   
    public void dbConnect() throws SQLException, IOException {
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        conn = DriverManager.getConnection("jdbc:oracle:thin:@onimqs2.onsemi.com:1521:dbonimqs","actionre","actionre");
    }
   
    public void dbConnectARe() throws SQLException, IOException {
        oracleConnString = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.242.65.222)(PORT=1521))(CONNECT_DATA=(SID=myondb1)))";
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        connActionRe = DriverManager.getConnection(oracleConnString ,"actionre", "actionre");
    }
   
    public void dbClose() throws SQLException, IOException {
        conn.close();
    }
   
     public void dbCloseARe() throws SQLException, IOException {
        connActionRe.close();
    }
 
    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) throws SQLException, IOException {
        blob myBlob = new blob();
    }
   
}



Please help.
Nasa.
0
 

Author Comment

by:nasarudin
ID: 9669693
Thanks everybody for helping me.
I found a code from metalink that solve my problems.
Below is the after some chages.

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

public class test {
    public static void main (String args[]) {
        Connection conn = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            System.out.println("Connecting to database");
            conn = DriverManager.getConnection(dburl,user,pass);
            //byte[] data = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9};

            ResultSet rs;
            BLOB blob;
            Statement st;

            /*** Select blob record ***/
            st = conn.createStatement();
            ResultSet rs2 = st.executeQuery("select attachment_file from qa_attachment1 where attachment_id = 396");
            rs2.next();
            // Get as a BLOB
            Blob aBlob = rs2.getBlob(1);
            byte[] data = aBlob.getBytes(1, (int) aBlob.length());
            st.close();
            /*** End select blob ***/
           
            st = conn.createStatement();
            st.executeUpdate("insert into TT_DATA values ('1', empty_blob())");
            st.close();

            System.out.println("Setting autocommit to false");
            conn.setAutoCommit(false);
            st = conn.createStatement();
            System.out.println("Executing query");
            rs = st.executeQuery("SELECT * FROM TT_DATA WHERE ID='1' FOR UPDATE OF DATA");
            System.out.println("Setting autocommit to true");
            conn.setAutoCommit(true);
            rs.next();
            System.out.println("Getting BLOB");
            blob = ((OracleResultSet)rs).getBLOB(2);
            System.out.println("Getting Stream");
            OutputStream outstream = blob.getBinaryOutputStream();
            System.out.println("Writing to stream");
            outstream.write(data);
            System.out.println("Closing stream");
            outstream.close();
            System.out.println("Closing statement");
            st.close();


            System.out.println("Closing connection");
            conn.close();
            System.out.println("Done");
        } catch (Exception e) {
            System.out.println(e.toString());
        }
    }
}


I hope this could help others.
I'll accept tims answer because it helps me a lot.
Hope others don't mind.
;)
 nasa
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9669762
Cool!  You got it working!! :-)

Good luck with it!

Tim.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question