• 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
  • Junilu Lacar
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Rob Spoor
  • Bear Bibeault
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Piet Souris
  • Carey Brown
  • Stephan van Hulst
Bartenders:
  • Frits Walraven
  • fred rosenberger
  • salvin francis

creating and inserting Blob

 
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I need to insert a record into a table having a Blob type column. I am having byte[]. Need to convert into Blob and then insert it into that table.
This is the first time I am doing handling Blob.

http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/blob.html is saying:


8.1.1 Creating a Blob Object

The following code fragment illustrates creating a Blob object, where stmt is a Statement object:

ResultSet rs = stmt.executeQuery("SELECT DATA FROM TABLE1");
rs.first();
Blob data = rs.getBlob("DATA");

The variable blob contains a logical pointer to the BLOB value that is stored in the column DATA in the first row of the result set rs. It does not contain the data in the BLOB value, but as far as JDBC methods are concerned, it is operated on as if it did.



Here if the table "TABLE1" is empty then we will get nothing in ResultSet. Need to have something in the column "DATA". And in my case the table is empty. So, How to insert the Blob. I have the byte[] with me.

thanks.
 
author & internet detective
Posts: 40678
827
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Babar,
You can use the setBinaryStream() or setBytes() methods on the prepared statement.
 
Babar Qadri
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Jeanne.
I need a tool to view the Data of a BLOB field. Because SQL* PLUS is not showing me the data and giving me the message

SP2-0678: Column or attribute type can not be displayed by SQL*Plus

thanks
 
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
First insert empty_blob() into the blob field
commit that

Then u get the same row using select statement with the primary key or other key help (Select blobcolumn from table_name where ..... FOR UPDATE

syntax is " Select ..from...where...FOR UPDATE"

Now u assign that to a resultSet

Now u take an object of Oracle BLOB type(import oracle.sql.BLOB) and get the object with the help of getBLOB ( Do not forget to cast java.sql.ResultSet to oracle resultSet)

Then with the help of streams u can update the blob object
 
Jeanne Boyarsky
author & internet detective
Posts: 40678
827
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Babar Qadri:
Thanks Jeanne.
I need a tool to view the Data of a BLOB field. Because SQL* PLUS is not showing me the data and giving me the message

SP2-0678: Column or attribute type can not be displayed by SQL*Plus

thanks


Babar,
As a BLOB is binary data, it doesn't really make sense to display it on the command line. I think your best bet is to write a small java program to get the blob's data and do whatever with it (or print it if you are debugging.)
 
Babar Qadri
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hareesh,
Actually I dont wanna use oracle.sql.*. Could we do without that??

Hi Jeanne,
Yes you are right I just wanna debug. I have made a method for that. I am getting a new exception, while inserting the blob as you advised, setBytes().

That is: java.sql.SQLException: Data size bigger than max size for this type:20173

My table creation script:

create table TABLE_ONE(
ID VARCHAR2(15),
FILE_NAME VARCHAR2(40),
FILE BLOB,
PRIMARY KEY (ID)
)


please help.
thanks.
[ February 01, 2005: Message edited by: Babar Qadri ]
 
Hareesh Ram Chanchali
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have tried with setBytes.....

I do not think it will give u proper results

I have done it with the oracle BLOB...it is working well
i have tested it... even the clob also
 
Hareesh Ram Chanchali
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Check into the book of JDBC book from oreilly...he has given the code in ur orientation

if u want to use oracle then visit

http://www.stanford.edu/dept/itss/docs/oracle/9i/java.920/a96654/oralob.htm#1043272
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Babar,
Since it appears you are using Oracle, have you visited these two Web pages?

http://www.oracle.com/technology/sample_code/tech/java/codesnippet/jdbc/index.html

http://www.oracle.com/technology/sample_code/tech/java/sqlj_jdbc/index.html

Good Luck,
Avi.
 
Babar Qadri
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hareesh,
I think i should probably go with oracle API.

Avi,
thanks for your links.
 
Hareesh Ram Chanchali
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
probably,

have u referred the book of oreilly which i have suggested to go in ur method

or else confine to oracle..

for further problems mail me
i will give u the code
 
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
TOAD will tell you whether something is there in the Blob or not. If there is something then it shows like

BLOB_COLUMN
------------
(BLOB)

cheers.
[ February 03, 2005: Message edited by: Adeel Ansari ]
 
Babar Qadri
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hareesh,
Thanks. finally I did it.
Thanks to all for a great help.
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic