aspose file tools*
The moose likes JDBC and the fly likes creating and inserting Blob Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "creating and inserting Blob" Watch "creating and inserting Blob" New topic
Author

creating and inserting Blob

Babar Qadri
Ranch Hand

Joined: Oct 12, 2004
Posts: 51
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.


babar
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30762
    
156

Babar,
You can use the setBinaryStream() or setBytes() methods on the prepared statement.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Babar Qadri
Ranch Hand

Joined: Oct 12, 2004
Posts: 51
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

Joined: Jan 31, 2005
Posts: 110
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


Hareesh Ram Chanchali
SCJP 5.0, IBM Certified Solution Designer
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30762
    
156

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

Joined: Oct 12, 2004
Posts: 51
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

Joined: Jan 31, 2005
Posts: 110
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

Joined: Jan 31, 2005
Posts: 110
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

Joined: Oct 11, 2000
Posts: 1134

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

Joined: Oct 12, 2004
Posts: 51
Hareesh,
I think i should probably go with oracle API.

Avi,
thanks for your links.
Hareesh Ram Chanchali
Ranch Hand

Joined: Jan 31, 2005
Posts: 110
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

Joined: Aug 15, 2004
Posts: 2874
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

Joined: Oct 12, 2004
Posts: 51
Hareesh,
Thanks. finally I did it.
Thanks to all for a great help.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: creating and inserting Blob