• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

inserting blob to oracle

 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am trying to insert blobs for a file to oracle, tried everything nothing working, i found out there are two methods:

one is to get a reference to blob in database by getting a result set and then updating the reference to blob by output stream, its not working i don't know why, also this is long way as we have to first insert data, then get reference to it and then again update it by output streams, so 3 calls to database in place of 1.
Another easy way is to use callableStatement and do setBinaryStream like this:

java.io.ByteArrayInputStream byteStream = new java.io.ByteArrayInputStream(byteData);
int numBytes = byteData.length;
cstmt.setBinaryStream(parmIndex, byteStream, numBytes);

but i am not able to send bigger files, it is not allowing something more than 15-20 Kb, even failing at 31 kb i tried, says Data size bigger than max size for this type: , trying to set it into BLob datatype in oracle, i checked oracle specification, it says 4 GB is allowed for BLOB, and setBinaryStream is ok for BLOB as i am able to add smaller files
I got this piece of code from this link:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.java/bjnkmstr26.htm

Can you please help me on this, i am getting really frustrated by this blob thing, help will be really appreciated
 
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
See Oracle's sample code, a complete working program that inserts and retrieves CLOBs and BLOBS, here:
http://www.oracle.com/technology/sample_code/tech/java/sqlj_jdbc/files/advanced/advanced.html
(The LOB example)
 
shobhar gupta
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have also tried as in this oracle link, but when i try to update by using an output stream i get a error, ORA-00942: table or view does not exist, can u explain why i am getting this error. Here is my full code.

oracle.sql.BLOB bl = null;
statement.setBlob(4,bl);
statement.setDate(5, messageDate);
statement.setDate(6,messageExpiryDate);
statement.executeUpdate();

CallableStatement statement1 = getStatement();
statement1.registerOutParameter(1, OracleTypes.CURSOR);

statement1.execute();

resultSet = (ResultSet) statement1.getObject(1);

// Get the Blob locator and open output stream for the Blob

if( resultSet.next() )
{

bl = (BLOB)resultSet.getBlob("FILE_ATTACHMENT");
OutputStream byteOutputStream = bl.getBinaryOutputStream();
//
java.io.ByteArrayInputStream byteInputStream =newjava.io.ByteArrayInputStream(fileData);
// here file data is byte[] i got from my file i want to save

byteOutputStream.write(fileData, 0, byteInputStream.available());
// Write to Blob
}


Please help urgently !!!
 
Ranch Hand
Posts: 68
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi Shobhar,

try using the schema name along with your table name. For example,

insert into schema_name.table values (?,?);

say your schema name is shobhar and the table name is employee, your query would be:

insert into shobhar.employee values (?,?);

are u converting your blob into byte[]? please try doing this.

try this, if you still face some problems then let us know.
[ April 02, 2006: Message edited by: dnyan ginde ]
 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Example available in:
http://www.terminalxeption.com/index.php?itemid=24
reply
    Bookmark Topic Watch Topic
  • New Topic