nasarudin
asked on
Unable to insert big blob data into table
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("sel ect 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.prepareStatem ent(strSQL );
String stri = Integer.toString(i);
pstmt.setString(1,stri);
pstmt.setBytes(2,my_byte_a rray);
pstmt.setString(3,"1");
pstmt.executeUpdate();
dbCloseARe();
}catch(Exception ex){
System.out.println("Error : " + ex.getMessage());
}
}
public void dbConnect() throws SQLException, IOException {
DriverManager.registerDriv er(new oracle.jdbc.driver.OracleD river());
conn = DriverManager.getConnectio n("jdbc:or acle:thin: @onimqs2.o nsemi.com: 1521:dboni mqs","acti onre","act ionre");
}
public void dbConnectARe() throws SQLException, IOException {
oracleConnString = "jdbc:oracle:thin:@(DESCRI PTION=(ADD RESS=(PROT OCOL=TCP)( HOST=10.24 2.65.222)( PORT=1521) )(CONNECT_ DATA=(SID= myondb1))) ";
DriverManager.registerDriv er(new oracle.jdbc.driver.OracleD river());
connActionRe = DriverManager.getConnectio n(oracleCo nnString ,"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
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("sel
// 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
Blob aBlob = rs.getBlob(1);
System.out.println("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.prepareStatem
String stri = Integer.toString(i);
pstmt.setString(1,stri);
pstmt.setBytes(2,my_byte_a
pstmt.setString(3,"1");
pstmt.executeUpdate();
dbCloseARe();
}catch(Exception ex){
System.out.println("Error : " + ex.getMessage());
}
}
public void dbConnect() throws SQLException, IOException {
DriverManager.registerDriv
conn = DriverManager.getConnectio
}
public void dbConnectARe() throws SQLException, IOException {
oracleConnString = "jdbc:oracle:thin:@(DESCRI
DriverManager.registerDriv
connActionRe = DriverManager.getConnectio
}
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
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
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
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
Regards
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.
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.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thank You:)
:-)
ASKER
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("sel ect 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(allBytesInBlo b);
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.prepareStatem ent(strSQL );
String stri = Integer.toString(i);
pstmt.setString(1,stri);
pstmt.setBytes(2,my_byte_a rray);
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.prepareStatem ent(sqlNew Row);
ps.setString(1,stri);
ps.setString(2,acno);
ps.executeUpdate();
//lock new row
ps = connActionRe.prepareStatem ent(sqlLoc kRow);
ps.setString(1,"1");
ResultSet rs = ps.executeQuery();
rs.next();
oracle.sql.BLOB dbBlob = (oracle.sql.BLOB)myBlob;
//update blob
ps = connActionRe.prepareStatem ent(sqlSet Blob);
ps.setString(2,stri);
dbBlob.putBytes(1,myBlob.g etBytes(1, (int) myBlob.length()));
ps.setBlob(1,dbBlob);
connActionRe.commit();
System.out.print("Rows affected:");
System.out.println(ps.exec uteUpdate( ));
dbCloseARe();
}catch(Exception ex){
System.out.println("Error : " + ex.getMessage());
}
}
public void dbConnect() throws SQLException, IOException {
DriverManager.registerDriv er(new oracle.jdbc.driver.OracleD river());
conn = DriverManager.getConnectio n("jdbc:or acle:thin: @onimqs2.o nsemi.com: 1521:dboni mqs","acti onre","act ionre");
}
public void dbConnectARe() throws SQLException, IOException {
oracleConnString = "jdbc:oracle:thin:@(DESCRI PTION=(ADD RESS=(PROT OCOL=TCP)( HOST=10.24 2.65.222)( PORT=1521) )(CONNECT_ DATA=(SID= myondb1))) ";
DriverManager.registerDriv er(new oracle.jdbc.driver.OracleD river());
connActionRe = DriverManager.getConnectio n(oracleCo nnString ,"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.
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("sel
// 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
Blob aBlob = rs.getBlob(1);
System.out.println("length
allBytesInBlob = aBlob.getBytes(1, (int) aBlob.length());
System.out.println("bytes length : " + allBytesInBlob);
//insertBlob(allBytesInBlo
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.prepareStatem
String stri = Integer.toString(i);
pstmt.setString(1,stri);
pstmt.setBytes(2,my_byte_a
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_
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
//make new row
String stri = Integer.toString(i);
String acno = "nasa_" + stri;
ps = connActionRe.prepareStatem
ps.setString(1,stri);
ps.setString(2,acno);
ps.executeUpdate();
//lock new row
ps = connActionRe.prepareStatem
ps.setString(1,"1");
ResultSet rs = ps.executeQuery();
rs.next();
oracle.sql.BLOB dbBlob = (oracle.sql.BLOB)myBlob;
//update blob
ps = connActionRe.prepareStatem
ps.setString(2,stri);
dbBlob.putBytes(1,myBlob.g
ps.setBlob(1,dbBlob);
connActionRe.commit();
System.out.print("Rows affected:");
System.out.println(ps.exec
dbCloseARe();
}catch(Exception ex){
System.out.println("Error : " + ex.getMessage());
}
}
public void dbConnect() throws SQLException, IOException {
DriverManager.registerDriv
conn = DriverManager.getConnectio
}
public void dbConnectARe() throws SQLException, IOException {
oracleConnString = "jdbc:oracle:thin:@(DESCRI
DriverManager.registerDriv
connActionRe = DriverManager.getConnectio
}
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.
ASKER
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.Or acleDriver ");
System.out.println("Connec ting to database");
conn = DriverManager.getConnectio n(dburl,us er,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("Settin g autocommit to false");
conn.setAutoCommit(false);
st = conn.createStatement();
System.out.println("Execut ing query");
rs = st.executeQuery("SELECT * FROM TT_DATA WHERE ID='1' FOR UPDATE OF DATA");
System.out.println("Settin g autocommit to true");
conn.setAutoCommit(true);
rs.next();
System.out.println("Gettin g BLOB");
blob = ((OracleResultSet)rs).getB LOB(2);
System.out.println("Gettin g Stream");
OutputStream outstream = blob.getBinaryOutputStream ();
System.out.println("Writin g to stream");
outstream.write(data);
System.out.println("Closin g stream");
outstream.close();
System.out.println("Closin g statement");
st.close();
System.out.println("Closin g connection");
conn.close();
System.out.println("Done") ;
} catch (Exception e) {
System.out.println(e.toStr ing());
}
}
}
I hope this could help others.
I'll accept tims answer because it helps me a lot.
Hope others don't mind.
;)
nasa
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
System.out.println("Connec
conn = DriverManager.getConnectio
//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("Settin
conn.setAutoCommit(false);
st = conn.createStatement();
System.out.println("Execut
rs = st.executeQuery("SELECT * FROM TT_DATA WHERE ID='1' FOR UPDATE OF DATA");
System.out.println("Settin
conn.setAutoCommit(true);
rs.next();
System.out.println("Gettin
blob = ((OracleResultSet)rs).getB
System.out.println("Gettin
OutputStream outstream = blob.getBinaryOutputStream
System.out.println("Writin
outstream.write(data);
System.out.println("Closin
outstream.close();
System.out.println("Closin
st.close();
System.out.println("Closin
conn.close();
System.out.println("Done")
} catch (Exception e) {
System.out.println(e.toStr
}
}
}
I hope this could help others.
I'll accept tims answer because it helps me a lot.
Hope others don't mind.
;)
nasa
Cool! You got it working!! :-)
Good luck with it!
Tim.
Good luck with it!
Tim.
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.