db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Torsten Schlabach <TSchlab...@gmx.net>
Subject RE: Oracle 9i BLOB malfunction / 4k mystery (issue OJB170)
Date Thu, 15 May 2003 13:09:09 GMT
Just updated myself on what Oracle has to say on this.
I found this quite recent guide
http://otn.oracle.com/tech/java/sqlj_jdbc/pdf/a96654.pdf which declares the method to first
create the table row with an
empty xLOB object and then retrieve it to read / write it as sort of
preferred if not the only valid approach. Here is the remark they have on
setBinaryStream:

Quote >>

Important: The JDBC 2.0 specification states that
PreparedStatement methods setBinaryStream() and
setObject() can be used to input a stream value as a BLOB, and
that the PreparedStatement methods setAsciiStream(),
setUnicodeStream(), setCharacterStream(), and
setObject() can be used to input a stream value as a CLOB. This
bypasses the LOB locator, going directly to the LOB data itself.
In the implementation of the Oracle JDBC drivers, this functionality
is supported only for a configuration using an 8.1.6 database and 8.
1.6 JDBC OCI driver. Do not use this functionality for any other
configuration, as data corruption can result.

<< Quote

So there is little hope they will "fix" this in the Thin Driver, which
pretty much kicks us back to square #1.

I have tested the create - retrieve - read/write stream approach and it
seems to work fine with the Thin Driver. I have testet this with a 211 K text
document; so I guess this should be fine for any supported size.

I just wonder what implication this would have on implementing a solution in
OJB. (Bear with me; I have not been part of the original development and
therefore don't know the internals yet.) Thinking loud:

* Deleteing a row with xLOBs
Nothing special needs to happen here.

* Store some of a Java bean's properties into xLOBs.
Though this looks like a shallow object to OJB it will require N steps to be
saved, i.e. first do the regular INSERT with empty_{x}lob()s. Then do a
SELECT FOR UPDATE on each of the xLOBs and write to the corresponding streams.
Then commit.

* Retrieve some of a Java bean's properties into xLOBs.
Might be easier than you think first: Hopefully the java.sql.{C|B}lob
objects just end up in the corresponding result set and can more or less easily be
read from (maybe in a Conversion).

* Do an Update on a Java bean that has some of its properties stored into
xLOBs.
Need to find out if any of the LOB columns changed or not. If so, write to
their streams (where would they come from / be held?), if not it's irrelevant.

What's causing me some headache in the first place is the INSERT piece. I
would be thankful for some pointers as to where in OJB we would need to cut in
and decide that saving a particular new Object to the database will require
more than one SQL statement (i.e. 1 + one per xLOB) because if contains some
xLOB columns?

Regards,
Torsten

> I'll answer the first question.  It is most definitely an Oracle bug.  
> Regardless it is important that OJB address it in my opinion.  Users 
> using JDBC directly can work around this bug, users using OJB currently 
> cannot.  That creates a decision point when BLOB/CLOB data is required 
> of use Oracle or use OJB and to most people the DBMS is going to be the 
> higher priority.
> 
> And yes the OCI driver does not exhibit this bug but it is not always 
> possible to use the OCI driver.  First it requires an Oracle client 
> installation on each machine and second it is native code and at least 
> for Oracle 8.1.7 is flaky (many, many SEGFAULTs in our recent load 
> testing).
> 
> -----Original Message-----
> From: Torsten Schlabach [mailto:TSchlabach@gmx.net]
> Sent: Thursday, May 15, 2003 6:29 AM
> To: ojb-dev@db.apache.org
> Subject: Re: Oracle 9i BLOB malfunction / 4k mystery (issue OJB170)
> 
> 
> Folks,
> 
> if I get this right, we still don't have a *real* solution to this, do 
> we? I
> found that Per-Olof's CLOB patch for using the thin driver made it to 
> the
> CVS, but I understand it only fixes this for text < 4 KB, right?
> 
> So first of all I thought it was a good idea to enter an issue in the 
> bug
> database at http://scarab.werken.com/scarab/issues/id/OJB170 (which 
> became
> OJB170).
> 
> So to me there are at two questions right now:
> 
> 1. Is this an OJB bug, an Oracle bug or both?
> 2. How do we *want* do handle this at all?
> 
> What I mean is: To what column type would I map a String object that I
> except to grow very large (i.e. some dozends KB of text)?
> 
> I might map it to JDBC type CLOB which would be closest to reality but 
> this
> will break with a class cast exception (you cannot cast a string to a
> java.sql.Clob).
> 
> If you map it to anything else such as LONGVARCHAR Oracle will not care 
> but
> this will probably break other things.
> 
> In fact it might depend on your application what you want back in your 
> bean
> when using a CLOB column: You either might want to get a stream you can 
> read
> >from in some other place or you might want to just get the stuff into a
> String and not care about it any more (which would make your application 
> code much
> less Oracle specific by the way).
> 
> This is essentially two different JDBC types needed for the same type of 
> DB
> column. Does the framework support this at all? I am wrong in any 
> assumption?
> 
> Torsten
> 
> P.S.: I would like to post this as a comment in Scarab, but I did not 
> yet
> find out how to edit the issue. I was able to submit it though. Any help
> appreciated.
> 
> Original Message:
> -----------------
> From: Thomas Poeschmann t.poeschmann@exxcellent.de
> Date: Mon, 12 May 2003 16:05:08 +0200
> To: ojb-dev@db.apache.org
> Subject: Re: Oracle 9i BLOB malfunction / 4k mystery
> 
> 
> Hi there,
> 
> > Michael Mogley wrote:
> > Thomas Poeschmann says on the list that he almost has a solution,
> > using the above method I presume.
> 
> Yes, of course using the SELECT FOR UPDATE. Sorry for promising posting 
> code
> but not doing it, but I will try to find it this evening. It is probably
> just 
> for reference for you, since you already have it.
> 
> > Are there other dbmses and drivers that exhibit the same irregular 
> > behavior regarding LOBĀ“s.
> 
> Not that I know. Sometimes it is different to call one of the methods on 
> a
> statement to bring certain Java objects in. For example passing an array 
> in
> as
> a String. But I have never seen anything hard as Oracle XLOBs ;)
> 
> > Unfortunatly, our solution (apart from the the fix submitted) 
> currently 
> > consists of changing to the oci driver. Sad but true.
> 
> Which has other drawbacks, but well... Other ORM can not handle it 
> either
> with
> thin, by the way ;)
> 
> Kind regards,
> 
> Thomas
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
> 
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
> 


Mime
View raw message