Re: How to create a BLOB field with JDBC using thin driver

Thanks

> -----Original Message-----
> From: Nimmons, Buster [mailto:[email protected]]
> Sent: Monday, December 22, 2003 7:40 AM
> To: 'Chester Chen'; [email protected]
> Subject: RE: How to create a BLOB field with JDBC using thin driver
>
>
> 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".