creating and inserting Blob

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,
You can use the setBinaryStream() or setBytes() methods on the prepared statement.

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

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

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.)

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 ]

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

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

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.

Hareesh,
I think i should probably go with oracle API.

Avi,
thanks for your links.

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

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 ]

Hareesh,
Thanks. finally I did it.
Thanks to all for a great help.