• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

creating and inserting Blob

 
Babar Qadri
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
  • 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.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34863
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • 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
  • 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
 
Hareesh Ram Chanchali
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
  • 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
Marshal
Posts: 34863
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • 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
  • 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
  • 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
  • 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
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • 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
  • 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
  • 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
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • 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
  • Quote
  • Report post to moderator
Hareesh,
Thanks. finally I did it.
Thanks to all for a great help.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic