Re: ClobTypeHandlerCallback For Oracle 9i

[Ronn <ro...@gmail.com>]

Hi,

I am trying to insert clobs into Oracle 9i with Oracle 10g JDBC drivers.
The problems I'm facing are:

1) Tried using iBatis ClobTypeHandlerCallback, but encountered error
of unable to setString with > 32K

2) Override ClobTypeHandlerCallback's setParameter to remove the last line
//setter.setString((String)parameter);
so that I can bypass the error. It works if I am inserting 1 clob
only. If I'm inserting 2 clobs,
e.g. (key, clob1, clob2), it will work fine if the clobs are small. If
i'm inserting 2 very large clobs, the order of the clobs in the
database is reversed, i.e. it becomes (key,clob2,clob1). My fellow
developers and I have totally no idea why this actually happened.

So what I did was to do an insert for the first clob, then follow by
an update on the 2nd clob.

3) Once in a while, I am getting "io exception: software caused
connection abort: socket write error" or "No more data to read" when I
do an insertion of clob. This really kills my App. Does anyone has the
solution to this?
 
4) I thought the above problem may be caused by the removal of 
setter.setString((String)parameter), hence I tried the alternative of
using Spring's ClobStringTypeHandler instead. However, I got a
ClassCastException when it is parsing the SqlMap's Parameter.

Does anyone has solution for inserting clobs which are > 32K and will
not encounter the socket write error?

Is anyone using the oracle.sql.CLOB solution? Ever hit the socket
write error or no more data to read error?

I will appreciate some help here. Thanks!

Regards,
Ronn

>Can you use the Oracle 10g driver?

>Cheers,
>Clinton


On Wed, 9 Feb 2005 17:42:45 -0000, Peter Nunn <[EMAIL PROTECTED]> wrote:
> I've been trying (for a while now) to implement a TypeHandlerCallback that 
> can WRITE clobs under Oracle 9i. It appears that this isn't possible. If 
> anyone has a solution to this then I'd be grateful for your assistance. Up to 
> now I haven't seen a workable solution.
> 
> So far I have tried the following:
> 
> 1) Call setter.setString(s).
> 
> This fails because setString(x) has an upper limit of 4K.
> 
> 2) Call setter.setCharacterStream(reader, s.length()). i.e. use same approach 
> as 2.0.9 ClobTypeHandlerCallback implementation.
> 
> This doesn't work either.
> 
> 3) Attempt to work with Oracle extension, namely oracle.sql.CLOB. The 
> preferred solution.
> 
> This isn't possible because you cannot create an instance of oracle.sql.CLOB 
> directly. You need to either:
> 
> a) create a temporary CLOB via a call to CLOB.createTemporary(...). This 
> requires a Connection object. Since ParameterSetter doesn't expose the 
> PreparedStatement we cannot get hold of the Connection object. So this 
> approach is a non-starter. Even if we did have the connection things aren't 
> straightforward because we need an OracleConnection... which you won't have 
> if you are using a DataSource to obtain your connections.
> 
> b) execute a SELECT statement to obtain a ResultSet then obtain the CLOB 
> instance by calling ResultSet.getClob("field"). This clearly isn't an option 
> from within a TypeHandlerCallback.
> 
> I'd really appreciate some help here... I've hit a brick wall. As of now I've 
> come to the conclusion that iBatis cannot handle CLOBS in Oracle prior to 
> 10g. This is clearly an issue.
> 
> So the only remaining option is to write a JDBC Dao implementation and bypass 
> SQLMAPS...any improvements upon this?
> 
> Peter Nunn
> Senior Java Developer
> EMAP UK IT
> Telephone: 020 7017 3601
> Mobile: 07866 670 530
> 
> ** For Emap magazine subscriptions & gift offers visit 
> http://www.greatmagazines.co.uk/emap **
> 
> --------------------------------------------------------------------------------------------------------------
> The information in this email is intended only for the addressee(s) named 
> above.  Access to this email by anyone else is unauthorised.
> 
> If you are not the intended recipient of this message any disclosure, 
> copying, distribution or any action taken in reliance on it is prohibited and 
> may be unlawful.
> 
> Emap plc and or its subsidiaries do not warrant that any attachments are free 
> from viruses or other defects and accept no liability for any losses 
> resulting from infected email transmissions.
> 
> Please note that any views expressed in this email may be those of the 
> originator and do not necessarily reflect those of this organisation.
> --------------------------------------------------------------------------------------------------------------
> 
>

Re: ClobTypeHandlerCallback For Oracle 9i

[Brandon Goodin <br...@gmail.com>]

from what i've heard around here. you should be using the 10g driver.
It works with 9i and functions better.

Brandon

On 5/24/05, Ronn <ro...@gmail.com> wrote:
> Hi,
> 
> I am trying to insert clobs into Oracle 9i with Oracle 10g JDBC drivers.
> The problems I'm facing are:
> 
> 1) Tried using iBatis ClobTypeHandlerCallback, but encountered error
> of unable to setString with > 32K
> 
> 2) Override ClobTypeHandlerCallback's setParameter to remove the last line
> //setter.setString((String)parameter);
> so that I can bypass the error. It works if I am inserting 1 clob
> only. If I'm inserting 2 clobs,
> e.g. (key, clob1, clob2), it will work fine if the clobs are small. If
> i'm inserting 2 very large clobs, the order of the clobs in the
> database is reversed, i.e. it becomes (key,clob2,clob1). My fellow
> developers and I have totally no idea why this actually happened.
> 
> So what I did was to do an insert for the first clob, then follow by
> an update on the 2nd clob.
> 
> 3) Once in a while, I am getting "io exception: software caused
> connection abort: socket write error" or "No more data to read" when I
> do an insertion of clob. This really kills my App. Does anyone has the
> solution to this?
> 
> 4) I thought the above problem may be caused by the removal of
> setter.setString((String)parameter), hence I tried the alternative of
> using Spring's ClobStringTypeHandler instead. However, I got a
> ClassCastException when it is parsing the SqlMap's Parameter.
> 
> Does anyone has solution for inserting clobs which are > 32K and will
> not encounter the socket write error?
> 
> Is anyone using the oracle.sql.CLOB solution? Ever hit the socket
> write error or no more data to read error?
> 
> I will appreciate some help here. Thanks!
> 
> Regards,
> Ronn
> 
> >Can you use the Oracle 10g driver?
> 
> >Cheers,
> >Clinton
> 
> 
> On Wed, 9 Feb 2005 17:42:45 -0000, Peter Nunn <[EMAIL PROTECTED]> wrote:
> > I've been trying (for a while now) to implement a TypeHandlerCallback that
> > can WRITE clobs under Oracle 9i. It appears that this isn't possible. If
> > anyone has a solution to this then I'd be grateful for your assistance. Up to
> > now I haven't seen a workable solution.
> >
> > So far I have tried the following:
> >
> > 1) Call setter.setString(s).
> >
> > This fails because setString(x) has an upper limit of 4K.
> >
> > 2) Call setter.setCharacterStream(reader, s.length()). i.e. use same approach
> > as 2.0.9 ClobTypeHandlerCallback implementation.
> >
> > This doesn't work either.
> >
> > 3) Attempt to work with Oracle extension, namely oracle.sql.CLOB. The
> > preferred solution.
> >
> > This isn't possible because you cannot create an instance of oracle.sql.CLOB
> > directly. You need to either:
> >
> > a) create a temporary CLOB via a call to CLOB.createTemporary(...). This
> > requires a Connection object. Since ParameterSetter doesn't expose the
> > PreparedStatement we cannot get hold of the Connection object. So this
> > approach is a non-starter. Even if we did have the connection things aren't
> > straightforward because we need an OracleConnection... which you won't have
> > if you are using a DataSource to obtain your connections.
> >
> > b) execute a SELECT statement to obtain a ResultSet then obtain the CLOB
> > instance by calling ResultSet.getClob("field"). This clearly isn't an option
> > from within a TypeHandlerCallback.
> >
> > I'd really appreciate some help here... I've hit a brick wall. As of now I've
> > come to the conclusion that iBatis cannot handle CLOBS in Oracle prior to
> > 10g. This is clearly an issue.
> >
> > So the only remaining option is to write a JDBC Dao implementation and bypass
> > SQLMAPS...any improvements upon this?
> >
> > Peter Nunn
> > Senior Java Developer
> > EMAP UK IT
> > Telephone: 020 7017 3601
> > Mobile: 07866 670 530
> >
> > ** For Emap magazine subscriptions & gift offers visit
> > http://www.greatmagazines.co.uk/emap **
> >
> > --------------------------------------------------------------------------------------------------------------
> > The information in this email is intended only for the addressee(s) named
> > above.  Access to this email by anyone else is unauthorised.
> >
> > If you are not the intended recipient of this message any disclosure,
> > copying, distribution or any action taken in reliance on it is prohibited and
> > may be unlawful.
> >
> > Emap plc and or its subsidiaries do not warrant that any attachments are free
> > from viruses or other defects and accept no liability for any losses
> > resulting from infected email transmissions.
> >
> > Please note that any views expressed in this email may be those of the
> > originator and do not necessarily reflect those of this organisation.
> > --------------------------------------------------------------------------------------------------------------
> >
> >
>

Re: ClobTypeHandlerCallback For Oracle 9i

[Geoff Chiang <ge...@yahoo.com>]

Ronn, are you using iBatis in combination with the
Spring framework?  I'm currently doing that
successfully whilst still using the 9i drivers.  Just
now I tested the insertion of three clobs of over 32k
characters and didn't see any reordering or
exceptions.

Geoff

--- Ronn <ro...@gmail.com> wrote:

> Hi,
> 
> I am trying to insert clobs into Oracle 9i with
> Oracle 10g JDBC drivers.
> The problems I'm facing are:
> 
> 1) Tried using iBatis ClobTypeHandlerCallback, but
> encountered error
> of unable to setString with > 32K
> 
> 2) Override ClobTypeHandlerCallback's setParameter
> to remove the last line
> //setter.setString((String)parameter);
> so that I can bypass the error. It works if I am
> inserting 1 clob
> only. If I'm inserting 2 clobs,
> e.g. (key, clob1, clob2), it will work fine if the
> clobs are small. If
> i'm inserting 2 very large clobs, the order of the
> clobs in the
> database is reversed, i.e. it becomes
> (key,clob2,clob1). My fellow
> developers and I have totally no idea why this
> actually happened.
> 
> So what I did was to do an insert for the first
> clob, then follow by
> an update on the 2nd clob.
> 
> 3) Once in a while, I am getting "io exception:
> software caused
> connection abort: socket write error" or "No more
> data to read" when I
> do an insertion of clob. This really kills my App.
> Does anyone has the
> solution to this?
>  
> 4) I thought the above problem may be caused by the
> removal of 
> setter.setString((String)parameter), hence I tried
> the alternative of
> using Spring's ClobStringTypeHandler instead.
> However, I got a
> ClassCastException when it is parsing the SqlMap's
> Parameter.
> 
> Does anyone has solution for inserting clobs which
> are > 32K and will
> not encounter the socket write error?
> 
> Is anyone using the oracle.sql.CLOB solution? Ever
> hit the socket
> write error or no more data to read error?
> 
> I will appreciate some help here. Thanks!
> 
> Regards,
> Ronn
> 
> >Can you use the Oracle 10g driver?
> 
> >Cheers,
> >Clinton
> 
> 
> On Wed, 9 Feb 2005 17:42:45 -0000, Peter Nunn
> <[EMAIL PROTECTED]> wrote:
> > I've been trying (for a while now) to implement a
> TypeHandlerCallback that 
> > can WRITE clobs under Oracle 9i. It appears that
> this isn't possible. If 
> > anyone has a solution to this then I'd be grateful
> for your assistance. Up to 
> > now I haven't seen a workable solution.
> > 
> > So far I have tried the following:
> > 
> > 1) Call setter.setString(s).
> > 
> > This fails because setString(x) has an upper limit
> of 4K.
> > 
> > 2) Call setter.setCharacterStream(reader,
> s.length()). i.e. use same approach 
> > as 2.0.9 ClobTypeHandlerCallback implementation.
> > 
> > This doesn't work either.
> > 
> > 3) Attempt to work with Oracle extension, namely
> oracle.sql.CLOB. The 
> > preferred solution.
> > 
> > This isn't possible because you cannot create an
> instance of oracle.sql.CLOB 
> > directly. You need to either:
> > 
> > a) create a temporary CLOB via a call to
> CLOB.createTemporary(...). This 
> > requires a Connection object. Since
> ParameterSetter doesn't expose the 
> > PreparedStatement we cannot get hold of the
> Connection object. So this 
> > approach is a non-starter. Even if we did have the
> connection things aren't 
> > straightforward because we need an
> OracleConnection... which you won't have 
> > if you are using a DataSource to obtain your
> connections.
> > 
> > b) execute a SELECT statement to obtain a
> ResultSet then obtain the CLOB 
> > instance by calling ResultSet.getClob("field").
> This clearly isn't an option 
> > from within a TypeHandlerCallback.
> > 
> > I'd really appreciate some help here... I've hit a
> brick wall. As of now I've 
> > come to the conclusion that iBatis cannot handle
> CLOBS in Oracle prior to 
> > 10g. This is clearly an issue.
> > 
> > So the only remaining option is to write a JDBC
> Dao implementation and bypass 
> > SQLMAPS...any improvements upon this?
> > 
> > Peter Nunn
> > Senior Java Developer
> > EMAP UK IT
> > Telephone: 020 7017 3601
> > Mobile: 07866 670 530
> > 
> > ** For Emap magazine subscriptions & gift offers
> visit 
> > http://www.greatmagazines.co.uk/emap **
> > 
> >
>
--------------------------------------------------------------------------------------------------------------
> > The information in this email is intended only for
> the addressee(s) named 
> > above.  Access to this email by anyone else is
> unauthorised.
> > 
> > If you are not the intended recipient of this
> message any disclosure, 
> > copying, distribution or any action taken in
> reliance on it is prohibited and 
> > may be unlawful.
> > 
> > Emap plc and or its subsidiaries do not warrant
> that any attachments are free 
> > from viruses or other defects and accept no
> liability for any losses 
> > resulting from infected email transmissions.
> > 
> > Please note that any views expressed in this email
> may be those of the 
> > originator and do not necessarily reflect those of
> this organisation.
> >
>
--------------------------------------------------------------------------------------------------------------
> > 
> >
> 


Find local movie times and trailers on Yahoo! Movies.
http://au.movies.yahoo.com