You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-dev@db.apache.org by Per-Olof Norén <pe...@alma.nu> on 2003/05/06 14:55:27 UTC
Oracle 9i BLOB malfunction / 4k mystery
Hi all,
In our project we seem to be using all possible features...including
BLOB´s....which is isn´t working. :(
Here´s the setup:
Physical
---------
db-server: Oracle 9i, running on solaris
app-server: running solaris on sun hardware.
jdbc-driver: oracle-9.0.3 thin driver
Logical
---------
Bean containing byte[], which is mapped as blob.
We do not use batch mode operation in this case.
The symptoms
----------------
Any data in bean´s byte[] < 4k gets saved.
Any data in bean´s byte[] > gets *silently* ignored and is not saved in
db. No exceptions, no nothing.
I looked into the PlatfromOracleImpl.java and found out that
there is code to handle this known 4k issue, which obviously is still
there in 9i. This handling involves setting a private member variable
(m_userRsetType) in the prepared statement.
Being a developer, I fired up a debugging session. To my surprise i
found out that the statement already had m_userRsetType = 1 *before* the
call to the magical changePreparedStatementResultSetType....
Am I alone using oracle for storing blobs?
If not, has anyone succeeded, on Oracle 9i?
If succeeded, how?
Really, any pointers are welcome :-)
regards,
Per-Olof Norén
Re: Oracle 9i BLOB malfunction / 4k mystery
Posted by Thomas Poeschmann <t....@exxcellent.de>.
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
Re: Oracle 9i BLOB malfunction / 4k mystery
Posted by Per-Olof Norén <pe...@alma.nu>.
Michael Mogley wrote:
> Thomas and Per-Olof,
>
> I haven't been able to do anything on this yet. Per-Olof, does your
> solution work for updating lobs larger than 4K?
No, it just a small step the sense that < 4k clobs represented as String
or byte[] actually gets saved without exceptions.
The only way I know of to
> do this with the thin driver is to first issue a SELECT FOR UPDATE on the
> required row, then use Oracle JDBC extension API to get an OutputStream to
> the LOB and write to it.
Yes, this what i figured from internet resources as well.
My worry is that implementing this will be a hack.
Given the current architecture, issuing a select for update from within
setObjectForStatement, which is called on a per column basis, one would
have to refactor above the platformImpl to get the hands on the correct
primary keys, right? How are insert´s handeld? This also implies that we
would have a compile-time oracle-driver dependency.
Thomas Poeschmann says on the list that he almost has a solution,
using the above method I presume.
I´d like to consolidate our efforts here since it (at least to me)
seems like there is lot more than just a few lines of code involved
here. Is there a need to change platform interfaces to allow for this.
Are there other dbmses and drivers that exhibit the same irregular
behavior regarding LOB´s.
> If you've hit on another easier way to do the same, all the better, but
> judging from your comments, I'm not sure this is the case.
Unfortunatly, our solution (apart from the the fix submitted) currently
consists of changing to the oci driver. Sad but true.
Regards,
Per-Olof Norén
Re: Oracle 9i BLOB malfunction / 4k mystery
Posted by Michael Mogley <mm...@hotmail.com>.
Thomas and Per-Olof,
I haven't been able to do anything on this yet. Per-Olof, does your
solution work for updating lobs larger than 4K? The only way I know of to
do this with the thin driver is to first issue a SELECT FOR UPDATE on the
required row, then use Oracle JDBC extension API to get an OutputStream to
the LOB and write to it.
If you've hit on another easier way to do the same, all the better, but
judging from your comments, I'm not sure this is the case.
Michael
----- Original Message -----
From: "Thomas Mahler" <th...@web.de>
To: "OJB Developers List" <oj...@db.apache.org>
Sent: Thursday, May 08, 2003 10:52 PM
Subject: Re: Oracle 9i BLOB malfunction / 4k mystery
> Hi again Per-Olof,
>
> Per-Olof Norén wrote:
> > Thomas, Comitters,
> >
> > The reason for my modification (as sketched below) of PlatformOracleImpl
> > was to allow for both byte[] and java.lang.String in beans that is
> > mapped to clob field.
> >
> > This is my last mod that is not handed back into ojb cvs.
> > Is the project interested in me sending a patch for this?
>
> Yes, that sounds great. I'd really apreciate a simple patch to the 4K
> problem. So I'm willing to commit your patch to CVS after reviewing it.
>
> Could you please check if your solution does work for BLOB columns too?
> If so we could put an end to this whole 4K story soon.
>
> Currently Michael Mogley is also working on patch, but from what I saw
> last time his changes had a lot of impacts to many classes apart from
> PlatformOracleImpl. So I think the best thing is to compare both
> approaches and to take the solution with the least impact on the overall
> codebase.
>
> cheers,
> Thomas
>
> > I (and our customer) would really appreciate this, since this
> > would allow us to go with OJB 1.0 rcx releases , which is desired.
> >
> > Thanx in advance,
> >
> > Per-Olof Norén
> >
> >
> > Per-Olof Norén wrote:
> >
> >> Torsten Schlabach wrote:
> >>
> >>> Peer-Olof,
> >>>
> >>>>> Am I alone using oracle for storing blobs?
> >>>
> >>> Well, we have been trying the same for CLOBs. This issue seems to be
the
> >>> same on Oracle 8.x by the way.
> >>
> >>
> >>
> >> I actually made a change to get clobs to work:
> >>
> >> public void setObjectForStatement(PreparedStatement ps, int index,
> >> Object value, int sqlType) throws SQLException
> >> }
> >> .....skipping blob and double stuff.....
> >>
> >> else if (sqlType == Types.CLOB)
> >> {
> >> Reader reader = null;
> >> int length = 0;
> >> if(value instanceof String)
> >> {
> >> reader = new StringReader((String) value);
> >> length = (((String) value)).length();
> >> } else if (value instanceof byte[])
> >> {
> >> byte buf[] = (byte[]) value;
> >> ByteArrayInputStream inputStream = new
> >> ByteArrayInputStream(buf);
> >> reader = new InputStreamReader(inputStream);
> >> }
> >>
> >> ps.setCharacterStream(index, reader, length);
> >> }
> >> else
> >> {
> >> super.setObjectForStatement(ps, index, value, sqlType);
> >> }
> >> }
> >
> >
> >
> >
> >
> > ---------------------------------------------------------------------
> > 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
>
Re: Oracle 9i BLOB malfunction / 4k mystery
Posted by Thomas Pöschmann <t....@exxcellent.de>.
Hi there,
> I know that there is a workaround available for this, but it usually
> involves getting a XLOB object from a resultset, then casting it to a
> OracleXLOB, running OracleBLOB.getOutputStream. This can´t be done in
> setObjectForStatement without actually creating a blob first and so on.
I can easily provide an update on this. Contact me directly (whoever wants
to work on this) and I can send some code that can be easily added into OJB.
Kind regards,
Thomas
Re: Oracle 9i BLOB malfunction / 4k mystery
Posted by Per-Olof Norén <pe...@alma.nu>.
Thomas Mahler wrote:
> Hi again Per-Olof,
>
> Per-Olof Norén wrote:
>
>> Thomas, Comitters,
>>
>> The reason for my modification (as sketched below) of PlatformOracleImpl
>> was to allow for both byte[] and java.lang.String in beans that is
>> mapped to clob field.
>>
>> This is my last mod that is not handed back into ojb cvs.
>> Is the project interested in me sending a patch for this?
>
>
> Yes, that sounds great. I'd really apreciate a simple patch to the 4K
> problem. So I'm willing to commit your patch to CVS after reviewing it.
Actually, this simple patch just allows one to have a java.lang.String
or byte[] field in the bean and get it saved into CLOB columns. I just
tested > 4k Strings with thin driver and it fails just as well.
However without this fix I couldn´t event get clobs < 4k to work.
So i hope this small step in the right direction finds it´s way into cvs
anyways :-)
> Could you please check if your solution does work for BLOB columns too?
> If so we could put an end to this whole 4K story soon.
Since we´re (as always) in a hurry, running on platforms that´s
supported by oracle, we switched to the oci driver and it works well.
I´d be happy to test out any attempts on this using thin driver,
Even exploiting our customers extensive Oracle support account, if
nessecary. I´ll get back to working on this in roughly three weeks.
> Currently Michael Mogley is also working on patch, but from what I saw
> last time his changes had a lot of impacts to many classes apart from
> PlatformOracleImpl. So I think the best thing is to compare both
> approaches and to take the solution with the least impact on the overall
> codebase.
I know that there is a workaround available for this, but it usually
involves getting a XLOB object from a resultset, then casting it to a
OracleXLOB, running OracleBLOB.getOutputStream. This can´t be done in
setObjectForStatement without actually creating a blob first and so on.
I just can´t believe Oracle actually released 9 without fixing this....
regards,
Per-Olof Norén
Re: Oracle 9i BLOB malfunction / 4k mystery
Posted by Thomas Mahler <th...@web.de>.
Hi again Per-Olof,
Per-Olof Norén wrote:
> Thomas, Comitters,
>
> The reason for my modification (as sketched below) of PlatformOracleImpl
> was to allow for both byte[] and java.lang.String in beans that is
> mapped to clob field.
>
> This is my last mod that is not handed back into ojb cvs.
> Is the project interested in me sending a patch for this?
Yes, that sounds great. I'd really apreciate a simple patch to the 4K
problem. So I'm willing to commit your patch to CVS after reviewing it.
Could you please check if your solution does work for BLOB columns too?
If so we could put an end to this whole 4K story soon.
Currently Michael Mogley is also working on patch, but from what I saw
last time his changes had a lot of impacts to many classes apart from
PlatformOracleImpl. So I think the best thing is to compare both
approaches and to take the solution with the least impact on the overall
codebase.
cheers,
Thomas
> I (and our customer) would really appreciate this, since this
> would allow us to go with OJB 1.0 rcx releases , which is desired.
>
> Thanx in advance,
>
> Per-Olof Norén
>
>
> Per-Olof Norén wrote:
>
>> Torsten Schlabach wrote:
>>
>>> Peer-Olof,
>>>
>>>>> Am I alone using oracle for storing blobs?
>>>
>>> Well, we have been trying the same for CLOBs. This issue seems to be the
>>> same on Oracle 8.x by the way.
>>
>>
>>
>> I actually made a change to get clobs to work:
>>
>> public void setObjectForStatement(PreparedStatement ps, int index,
>> Object value, int sqlType) throws SQLException
>> }
>> .....skipping blob and double stuff.....
>>
>> else if (sqlType == Types.CLOB)
>> {
>> Reader reader = null;
>> int length = 0;
>> if(value instanceof String)
>> {
>> reader = new StringReader((String) value);
>> length = (((String) value)).length();
>> } else if (value instanceof byte[])
>> {
>> byte buf[] = (byte[]) value;
>> ByteArrayInputStream inputStream = new
>> ByteArrayInputStream(buf);
>> reader = new InputStreamReader(inputStream);
>> }
>>
>> ps.setCharacterStream(index, reader, length);
>> }
>> else
>> {
>> super.setObjectForStatement(ps, index, value, sqlType);
>> }
>> }
>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
>
>
Re: Oracle 9i BLOB malfunction / 4k mystery
Posted by Per-Olof Norén <pe...@alma.nu>.
Thomas, Comitters,
The reason for my modification (as sketched below) of PlatformOracleImpl
was to allow for both byte[] and java.lang.String in beans that is
mapped to clob field.
This is my last mod that is not handed back into ojb cvs.
Is the project interested in me sending a patch for this?
I (and our customer) would really appreciate this, since this
would allow us to go with OJB 1.0 rcx releases , which is desired.
Thanx in advance,
Per-Olof Norén
Per-Olof Norén wrote:
> Torsten Schlabach wrote:
>
>> Peer-Olof,
>>>> Am I alone using oracle for storing blobs?
>> Well, we have been trying the same for CLOBs. This issue seems to be the
>> same on Oracle 8.x by the way.
>
>
> I actually made a change to get clobs to work:
>
> public void setObjectForStatement(PreparedStatement ps, int index,
> Object value, int sqlType) throws SQLException
> }
> .....skipping blob and double stuff.....
>
> else if (sqlType == Types.CLOB)
> {
> Reader reader = null;
> int length = 0;
> if(value instanceof String)
> {
> reader = new StringReader((String) value);
> length = (((String) value)).length();
> } else if (value instanceof byte[])
> {
> byte buf[] = (byte[]) value;
> ByteArrayInputStream inputStream = new
> ByteArrayInputStream(buf);
> reader = new InputStreamReader(inputStream);
> }
>
> ps.setCharacterStream(index, reader, length);
> }
> else
> {
> super.setObjectForStatement(ps, index, value, sqlType);
> }
> }
Re: Oracle 9i BLOB malfunction / 4k mystery
Posted by Per-Olof Norén <pe...@alma.nu>.
Torsten Schlabach wrote:
> Peer-Olof,
>
>
>>>Am I alone using oracle for storing blobs?
>
>
> Well, we have been trying the same for CLOBs. This issue seems to be the
> same on Oracle 8.x by the way.
I actually made a change to get clobs to work:
public void setObjectForStatement(PreparedStatement ps, int index,
Object value, int sqlType) throws SQLException
}
.....skipping blob and double stuff.....
else if (sqlType == Types.CLOB)
{
Reader reader = null;
int length = 0;
if(value instanceof String)
{
reader = new StringReader((String) value);
length = (((String) value)).length();
} else if (value instanceof byte[])
{
byte buf[] = (byte[]) value;
ByteArrayInputStream inputStream = new
ByteArrayInputStream(buf);
reader = new InputStreamReader(inputStream);
}
ps.setCharacterStream(index, reader, length);
}
else
{
super.setObjectForStatement(ps, index, value, sqlType);
}
}
BTW (OT), I honestly *had* read your excellent primer om LOBs.
This allowed me make Bobby(1) on what LOB stands for :-)
even more off topic:
1. To make a "Bobby". Comes from Bobby Ewing in tv series "dallas".
Famous for his besserwisser remarks "As far as i know.....".
In my circle of friends to Bobby someone is becoming a verb...
regards,
Per-Olof
Re: Oracle 9i BLOB malfunction / 4k mystery
Posted by Torsten Schlabach <TS...@gmx.net>.
Peer-Olof,
>> Am I alone using oracle for storing blobs?
Well, we have been trying the same for CLOBs. This issue seems to be the
same on Oracle 8.x by the way.
>> If not, has anyone succeeded, on Oracle 9i?
No. Not yet.
I am working on this, but I have already arrived at theory 3 what's wrong.
On my way of debugging this I got the notion that a lot of
non-Oracle-DBA-people (I am one of them) get the concept of xLOB and LARGEx in Oracle wrong. The
code appears to me as if this is true for those people who wrote the
OraclePlatformImpl as well. (I have written a document on this:
http://cvs.apache.org/viewcvs/db-ojb/xdocs/howto-use-lobs.xml)
The next piece that I (or someone else: feel free) should add is something
along the lines of:
1. What does that mean for JDBC data types and what's happening behind the
scenes of Oracle?
2. Deciding what you really want
Regarding 2: I think there cannot be *one* solution to the handling of xLOB
objects as in some cases you would probably really want to move all the data
as if it was one field (if it is may 10 k for a pic) whereas in some cases
you might habe to get just the stream handler back and decide in your app when
to read or write what and how many.
Correct me if I am wrong, but last time I was at the issue I could make
neither approach work with rc1.
Torsten
> Hi all,
> In our project we seem to be using all possible features...including
> BLOB´s....which is isn´t working. :(
>
> Here´s the setup:
>
> Physical
> ---------
> db-server: Oracle 9i, running on solaris
> app-server: running solaris on sun hardware.
> jdbc-driver: oracle-9.0.3 thin driver
>
> Logical
> ---------
> Bean containing byte[], which is mapped as blob.
> We do not use batch mode operation in this case.
>
> The symptoms
> ----------------
> Any data in bean´s byte[] < 4k gets saved.
> Any data in bean´s byte[] > gets *silently* ignored and is not saved in
> db. No exceptions, no nothing.
>
> I looked into the PlatfromOracleImpl.java and found out that
> there is code to handle this known 4k issue, which obviously is still
> there in 9i. This handling involves setting a private member variable
> (m_userRsetType) in the prepared statement.
> Being a developer, I fired up a debugging session. To my surprise i
> found out that the statement already had m_userRsetType = 1 *before* the
> call to the magical changePreparedStatementResultSetType....
>
> Am I alone using oracle for storing blobs?
> If not, has anyone succeeded, on Oracle 9i?
> If succeeded, how?
>
> Really, any pointers are welcome :-)
>
> regards,
>
> Per-Olof Norén
>
>
>
>
>
>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
>