This week's giveaway is in the Cloud/Virtualization forum.
We're giving away four copies of Secure Financial Transactions with Ansible, Terraform, and OpenSCAP and have Lucian Maly on-line!
See this thread for details.
Win a copy of Secure Financial Transactions with Ansible, Terraform, and OpenSCAP this week in the Cloud/Virtualization forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Rob Spoor
  • Henry Wong
  • Liutauras Vilda
Saloon Keepers:
  • Tim Moores
  • Carey Brown
  • Stephan van Hulst
  • Tim Holloway
  • Piet Souris
Bartenders:
  • Frits Walraven
  • Himai Minh
  • Jj Roberts

Inserting Blob data into Oracle DB through JDBC

 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello friends,

I need to insert an image into the oracle db using jdbc.
I'm extracting the image from access db first as getBinaryStream and then inserting the same into oracle Blob data type column.

In access the image is stored in a column with data type OLE Object.

When i did this am geeting an error like

Io exception: InputStream is no longer valid - the Statement has been closed, or the cursor has been moved

Here is the code(for extracting data from Access Db)
public ArrayList<ArrayList<Member>> vSelectAll(ArrayList<String> tablename)
{
ArrayList<ArrayList<Member>> temp = new ArrayList<ArrayList<Member>>();
String temp_tablename =null;
Iterator it = tablename.iterator();
while(it.hasNext())
{
temp_tablename = (String)it.next();
System.out.println("tablename - "+temp_tablename);


String sql = "select * from "+temp_tablename;
try{
rs = st.executeQuery(sql);
while(rs.next())
{
product = rs.getString("PRODUCT");
Integer uwyear1 = rs.getInt("POLYEAR");
uwyear = uwyear1.toString();
memberno = rs.getString("MEMNO");
polserno = rs.getString("POLSRL");
Integer memsince1 = rs.getInt("MEMSINCE");
memsince = memsince1.toString();
picture = rs.getBinaryStream("PICTURE");

MemberDet.add(new Member(product,uwyear,memberno,polserno,memsince,picture));
}
System.out.println("MemberDet - "+MemberDet.size());
}
catch(SQLException e)
{
System.out.println(e.getMessage());
}
temp.add(MemberDet);
}
return temp;
}
Here is the code:(for inserting to the oracle db)


public void vInsertPhoto(String product,String memberno,String polserno,String memsince,InputStream picture)
{
PreparedStatement st1 = null;
String sql = "insert into APP_MEMBER_PHOTO_DETAILS (AMP_PRODUCT_CODE,AMP_MEMBER_NO,AMP_MEM_REFERENCE_NO,AMP_MEMBER_PHOTO) values(?,?,?,?)";

try{
st1 = con.prepareStatement(sql);
if(picture !=null)
{
st1.setString(1, product);
st1.setString(2, memberno);
st1.setString(3, polserno);
st1.setBinaryStream(4, picture,1);
st1.setString(5, memsince);
st1.executeUpdate();
System.out.println("Insertion is happened");
}
else
System.out.println("picture is null");
}
catch(SQLException e)
{
System.out.println(e.getMessage());
}
finally
{
try {

st1.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

Please help me out to solve this problem
 
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Mythily,

You can not just store blob pointer inputstream in memory. Either you should fetch all the bytes and store it in memory in order to use it in vInsertPhoto method (not good), or you should read some bytes and then write it immediately.

You have to merge your methods into one method that will read one row from Access DB then insert it to Oracle DB.

Regards,
Fatih.
 
joke time: What is brown and sticky? ... ... ... A stick! Use it to beat this tiny ad!
SKIP - a book about connecting industrious people with elderly land owners
https://coderanch.com/t/skip-book
reply
    Bookmark Topic Watch Topic
  • New Topic