Below is the code I used to update a clob field in our oracle 9i Database.
It doesn't use Oracle APIs per say but you do have to cast the ResultSet to
an Oracle resultset. Other than that it's straight jdbc with Oracle specific
SQL.
***** You can not directly insert into a blob field you must
first create the record then get a handle to the blob or clob field
pstmt2 = con.prepareStatement("INSERT INTO IPIMS_EMAIL_INFO
(EMAIL_ID,ENTERED_BY,PROJECT_ID,BODY,SUBJECT,ENTER_DATE) VALUES
(?,?,?,EMPTY_CLOB(),?,?)");
***** After the record insert use this select to get a handle to
the lob field
pstmt21 = con.prepareStatement("SELECT BODY FROM
IPIMS_EMAIL_INFO WHERE EMAIL_ID = ? FOR UPDATE");
***** this statement actually updates the field
pstmt22 = con.prepareStatement("UPDATE IPIMS_EMAIL_INFO SET BODY
= ? WHERE EMAIL_ID = ?");
pstmt2.setLong(1, nseq);
//pstmt2.setString(2,new Date().toString());
pstmt2.setString(2, from);
pstmt2.setLong(3, projectId);
//pstmt2.setString(4, body);
pstmt2.setString(4, subject);
pstmt2.setDate(5, new java.sql.Date(new
java.util.Date().getTime()));
pstmt2.execute();
pstmt21.setLong(1,nseq);
**** execute statement to get handle to lob field
rs = pstmt21.executeQuery();
rs.next();
***** This is the only spot you use Oracle specific "APIs"
CLOB clob = ((OracleResultSet)rs).getCLOB(1);
StringReader sr = new StringReader(body);
Writer oss = clob.getCharacterOutputStream();
int length = clob.getBufferSize();
char[] buffer = new char[length];
while (sr.read(buffer) != -1) oss.write(buffer);
sr.close();
oss.close();
pstmt22.setClob(1,clob);
pstmt22.setLong(2,nseq);
pstmt22.execute();
-----Original Message-----
From: Chester Chen [mailto:[EMAIL PROTECTED]
Sent: Friday, December 19, 2003 4:23 PM
To: [EMAIL PROTECTED]
Subject: Re: How to create a BLOB field with JDBC using thin driver
Mark,
I was hoping not to use Oracle APIs. rathter just JDBC APIs (No Oracle
Specific import).
> >usage info can be found in the bible of JDBC: White, et al, "JDBC API
> >Tutorial and Reference, 2d Ed." (Sun/Addison-Wesley 1999):
> 282-288, and
The book only give example of how to read BLOB, or read BLOB and then call
setBLOB() after got the BLOB
, no example is given in the book for how to Store an BLOB.
Joe sam showed an examnple on Sun's website using PrepareStatement with
setBinaryStream() method and it works on DB2.
My collegue and myself tried, it does not seems to work on Oracle (as oracle
has a special way of doing it).
> -----Original Message-----
> From: Mark Galbreath [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, December 16, 2003 3:51 PM
> To: [EMAIL PROTECTED]
> Subject: Re: How to create a BLOB field with JDBC using thin driver
>
>
> Correct you are, but I wrote a class that manipulates BLOBs
> in an Oracle 9i
> database using the Oracle API discussed in the last citation
> in my previous
> email below. You can get the Oracle classes and documentation at
> http://otn.oracle.com/software/index.html.
>
> Mark
>
> -----Original Message-----
> From: thorick [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, December 16, 2003 6:42 PM
> To: Mark Galbreath; [EMAIL PROTECTED]
> Subject: Re: How to create a BLOB field with JDBC using thin driver
>
>
>
> FWIW, JDBC2.0 has no provisions for updating Blobs. I think JDBC3.0
> finally addresses this shortcoming. Don't know how available 3.0
> Drivers are yet..
>
>
> At 06:20 PM 12/16/2003 -0500, Mark Galbreath wrote:
> >Have you tried the API? java.util.sql.Blob should get you
> there. More
> >usage info can be found in the bible of JDBC: White, et al, "JDBC API
> >Tutorial and Reference, 2d Ed." (Sun/Addison-Wesley 1999):
> 282-288, and
> >Bales, "Java Programming with JDBC" (O'Reilly 2002): 246-272.
> >
> >Mark
> >
> >-----Original Message-----
> >From: Chester Chen
> >Sent: Tuesday, December 16, 2003 11:40 AM
> >
> > I am trying to use a BLOB to store some binary data and
> wonder how to
> do
> >this in JDBC.
> > I have search the java.sun.com site, oracle and google
> sites and did
> not
> >find any thing useful.
> >
> >
> > The JDBC API tutorial reference book only give examples
> to update BLOB,
> >i.e. you read BLOB from database
> >and then setBLOB to update it.
> >
> > I have found any example to show me how to create an new
> BLOB record,
> has
> >any done this before ?
> >
> >Chester
> >
> >=============================================================
> ==============
> >To unsubscribe, send email to [EMAIL PROTECTED] and
> include in the body
> >of the message "signoff EJB-INTEREST". For general help,
> send email to
> >[EMAIL PROTECTED] and include in the body of the message "help".
> >
> >=============================================================
> ==============
> >To unsubscribe, send email to [EMAIL PROTECTED] and
> include in the body
> >of the message "signoff EJB-INTEREST". For general help,
> send email to
> >[EMAIL PROTECTED] and include in the body of the message "help".
>
> ==============================================================
> =============
> To unsubscribe, send email to [EMAIL PROTECTED] and
> include in the body
> of the message "signoff EJB-INTEREST". For general help,
> send email to
> [EMAIL PROTECTED] and include in the body of the message "help".
>
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
