hibernate-devel

RE: [Hibernate] Using CLOBs

[Gavin K. <Gav...@ex...>]

The name of the type is "clob", and the expected property type
is java.sql.Clob. Note that there are restrictions upon what
you can do with Clobs (they can't be used outside of transaction,
for example).

You should also take notice of Hibernate.createClob().

> -----Original Message-----
> From: Ugo Cei [mailto:u....@cb...]=20
> Sent: Tuesday, 31 December 2002 1:06 AM
> To: hib...@li...
> Subject: [Hibernate] Using CLOBs
>=20
>=20
> Hi people,
>=20
> does anybody have some documentation and/or samples re using CLOBs in=20
> the current CVS version?
>=20
> Do I have to declare 'type=3D"clob"' in my mapping file? Do my class=20
> attributes have to be String's or java.sql.Clob's or something else?
>=20
> Since I need this feature, I'd like to help testing and maybe fixing=20
> them, in particular with Oracle, but I need a couple of hints just to=20
> get me started.
>=20
> 	Thanks in Advance,
>=20
> 		Ugo
>=20
> --=20
> Ugo Cei - http://www.beblogging.com/blog/
>=20
>=20
>=20
> -------------------------------------------------------
> This sf.net email is sponsored by:ThinkGeek
> Welcome to geek heaven.
> http://thinkgeek.com/sf=20
> _______________________________________________
> hibernate-devel mailing list hib...@li...
> https://lists.sourceforge.net/lists/listinfo/hibernate-devel
>=20


********** CAUTION - Disclaimer **********
This message may contain privileged and confidential
information. If you are not the intended recipient of this
message (or responsible for delivery of the message to
such person) you are hereby notified that any use,
dissemination, distribution or reproduction of this message
is prohibited. If you have received this message in error,
you should destroy it and kindly notify the sender by reply
e-mail. Please advise immediately if you or your employer
do not consent to Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this
message that do not relate to the official business of
Expert Information Services Pty Ltd ("The Company")
shall be understood as neither given nor endorsed by it.

The Company advises that this e-mail and any attached
files should be scanned to detect viruses. The Company
accepts no liability for loss or damage (whether caused
by negligence or not) resulting from the use of any
attached files.
**EIS******** End of Disclaimer **********

RE: [Hibernate] Using CLOBs

[Gavin K. <Gav...@ex...>]

Ahhhh the > 4000 characters bit is where you start to need
the "for update" clause. We were wondering about that
earlier. I think you *could* make it work like this:

s =3D sf.openSession();
tx =3D s.beginTransaction();
foo =3D new Foo();
foo.setClob( Hibernate.createClob("") );
s.save(foo);
tx.commit();
s.close();

s =3D sf.openSession();
tx =3D s.beginTransaction();
foo =3D (Foo) s.load( Foo.class, foo.getId(), LockMode.UPGRADE );
oracle.sql.CLOB clob =3D (oracle.sql.CLOB) foo.getClob();
java.io.Writer pw =3D clob.getCharacterOutputStream();
pw.write(content);
pw.close();
tx.commit();
s.close();


Would you try that for me, please?

Gavin

> -----Original Message-----
> From: Ugo Cei [mailto:u....@cb...]=20
> Sent: Wednesday, 1 January 2003 3:22 AM
> To: hib...@li...
> Subject: Re: [Hibernate] Using CLOBs
>=20
>=20
> Gavin King wrote:
> > The name of the type is "clob", and the expected property type is=20
> > java.sql.Clob. Note that there are restrictions upon what=20
> you can do=20
> > with Clobs (they can't be used outside of transaction, for example).
> >=20
> > You should also take notice of Hibernate.createClob().
>=20
> Thanks. Everything works fine ... at least in theory ...
>=20
> If you use Oracle 8i and try to store more than 4000=20
> characters, this is=20
> what you get:
>=20
> java.sql.SQLException: Data size bigger than max size for=20
> this type: 7894
>          at=20
> oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
>          at=20
> oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
>          at oracle.jdbc.ttc7.TTCItem.setArrayData(TTCItem.java:147)
>          at=20
> oracle.jdbc.dbaccess.DBDataSetImpl.setBytesBindItem(DBDataSetI
> mpl.java:2461)
>          at=20
> oracle.jdbc.driver.OraclePreparedStatement.setItem(OraclePrepa
> redStatement.java:1155)
>          at=20
> oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePre
> paredStatement.java:1572)
>          at=20
> org.apache.commons.dbcp.DelegatingPreparedStatement.setString(
DelegatingPreparedStatement.java:217)
>          at=20
> org.apache.commons.dbcp.DelegatingPreparedStatement.setString(
DelegatingPreparedStatement.java:217)
>          at cirrus.hibernate.type.ClobType.set(ClobType.java:14)
>=20
> Actually, having already been through the nightmare that CLOB=20
> support in=20
> Oracle is, I expected nothing more and nothing less.
>=20
> AFAIK there is just one way to store more than 4000 characters in an=20
> Oracle CLOB via JDBC:
>=20
> - start a transaction
> - insert into tablename(id, clob_field) values('X', empty_clob())
> - select clob_field from tablename where id =3D 'X' for update=20
> of clob_field
> - ResultSet rs =3D (oracle.jdbc.driver.OracleResultSet)=20
> ps.executeQuery();
>    if (rs.next()) {
>      oracle.sql.CLOB clob =3D rs.getCLOB(1);
>      java.io.Writer pw =3D clob.getCharacterOutputStream();
>      pw.write(content);
>      pw.close();
>    }
> - commit
>=20
> If this isn't a PITA, I don't know what is :-(
>=20
> Do you think it's going to be difficult to implement this=20
> rigamarole in=20
> Hibernate?
>=20
> 	Ugo
>=20
> --=20
> Ugo Cei - http://www.beblogging.com/blog/
>=20
>=20
>=20
> -------------------------------------------------------
> This sf.net email is sponsored by:ThinkGeek
> Welcome to geek heaven.
> http://thinkgeek.com/sf=20
> _______________________________________________
> hibernate-devel mailing list hib...@li...
> https://lists.sourceforge.net/lists/listinfo/hibernate-devel
>=20


********** CAUTION - Disclaimer **********
This message may contain privileged and confidential
information. If you are not the intended recipient of this
message (or responsible for delivery of the message to
such person) you are hereby notified that any use,
dissemination, distribution or reproduction of this message
is prohibited. If you have received this message in error,
you should destroy it and kindly notify the sender by reply
e-mail. Please advise immediately if you or your employer
do not consent to Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this
message that do not relate to the official business of
Expert Information Services Pty Ltd ("The Company")
shall be understood as neither given nor endorsed by it.

The Company advises that this e-mail and any attached
files should be scanned to detect viruses. The Company
accepts no liability for loss or damage (whether caused
by negligence or not) resulting from the use of any
attached files.
**EIS******** End of Disclaimer **********

Re: [Hibernate] Using CLOBs

[Ugo C. <u....@cb...>]

Gavin King wrote:
> Ahhhh the > 4000 characters bit is where you start to need
> the "for update" clause. We were wondering about that
> earlier. I think you *could* make it work like this:
> 
> s = sf.openSession();
> tx = s.beginTransaction();
> foo = new Foo();
> foo.setClob( Hibernate.createClob("") );
> s.save(foo);
> tx.commit();
> s.close();
> 
> s = sf.openSession();
> tx = s.beginTransaction();
> foo = (Foo) s.load( Foo.class, foo.getId(), LockMode.UPGRADE );
> oracle.sql.CLOB clob = (oracle.sql.CLOB) foo.getClob();
> java.io.Writer pw = clob.getCharacterOutputStream();
> pw.write(content);
> pw.close();
> tx.commit();
> s.close();
> 
> 
> Would you try that for me, please?

Tried it and it *mostly* works. The only problem seems to be that Oracle 
apparently does not distinguish between zero-length strings and NULL, so 
if you initialize the CLOB with Hibernate.createClob(""), the actual 
database column is nullified and the subsequent getClob() call returns 
null, making the clob.getCharacterOutputStream() fail with a NPE.

As a quick&dirty hack, I've changed createClob("") to createClob(" ") 
and everything seems to work ok. However, I was wondering if it is 
really necessary to open a new session. Couldn't we somehow reuse the 
former session?

	Ugo

RE: [Hibernate] Using CLOBs

[Gavin K. <Gav...@ex...>]

> it *mostly* works. The only problem seems to be=20
> that Oracle=20
> apparently does not distinguish between zero-length strings=20
> and NULL, so

I have run into this before with Oracle. Very yucky.

> if you initialize the CLOB with Hibernate.createClob(""), the actual=20
> database column is nullified and the subsequent getClob()=20
> call returns=20
> null, making the clob.getCharacterOutputStream() fail with a NPE.
>=20
> As a quick&dirty hack, I've changed createClob("") to createClob(" ")=20
> and everything seems to work ok.=20

Ugo, would you please produce a page documenting your approach to this
for the Wiki. Other people will also need to know this stuff.

> However, I was wondering if it is=20
> really necessary to open a new session. Couldn't we somehow reuse the=20
> former session?

Well, not currently. Occasionally other people have requested things
like Session.refresh(), which I have stubbornly not implemented because
I never saw a really compelling use case. ( *This* is a good use case
for refresh(). )

For Hibernate 2, should we add one or both of:

Session.evict(foo); //remove foo from the Session-level cache
Session.refresh(foo); //reload foo's state from the database

There *would* be some exotic cases (like this one) where they could
be useful....

Opinions?


********** CAUTION - Disclaimer **********
This message may contain privileged and confidential
information. If you are not the intended recipient of this
message (or responsible for delivery of the message to
such person) you are hereby notified that any use,
dissemination, distribution or reproduction of this message
is prohibited. If you have received this message in error,
you should destroy it and kindly notify the sender by reply
e-mail. Please advise immediately if you or your employer
do not consent to Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this
message that do not relate to the official business of
Expert Information Services Pty Ltd ("The Company")
shall be understood as neither given nor endorsed by it.

The Company advises that this e-mail and any attached
files should be scanned to detect viruses. The Company
accepts no liability for loss or damage (whether caused
by negligence or not) resulting from the use of any
attached files.
**EIS******** End of Disclaimer **********

Re: [Hibernate] Using CLOBs

[Ugo C. <u....@cb...>]

Gavin King wrote:
> Ugo, would you please produce a page documenting your approach to this
> for the Wiki. Other people will also need to know this stuff.

Done. I also hope I can find the time to write some reusable code that 
spares me from having to do all this every single time I want to store a 
Clob. If I succeed, I will donate it, if anyone's interested.

	Ugo

Session eviction (was Re: [Hibernate] Using CLOBs)

[Chris N. <ch...@si...>]

On Fri, Jan 03, 2003 at 03:10:21PM +1100, Gavin King wrote:
> For Hibernate 2, should we add one or both of:
> 
> Session.evict(foo); //remove foo from the Session-level cache
> Session.refresh(foo); //reload foo's state from the database
> 
> There *would* be some exotic cases (like this one) where they could
> be useful....

I brought this up in the forums yesterday, due to my session cache
filling up as the result of large queries. Gavin also suggested
Session.clear().

evict would work fine for me, but it would end up something like this:

for (Iterator it = query.iterate(); it.hasNext();) {
  Cat cat = (Cat)it.next();
  session.evict(cat);
  // do some calculation on cat
}

Maybe it's silly to pull it into the cache via next(), just to evict as
the very next step.

So, how about 
  query.setTransient(true)?

All objects returned by the query would be transient and not take up
space in the cache.

-Chris

Re: Session eviction (was Re: [Hibernate] Using CLOBs)

[Chris N. <ch...@si...>]

On Fri, Jan 24, 2003 at 10:30:08AM -0800, Chris Nokleberg wrote:
> So, how about 
>   query.setTransient(true)?
> 
> All objects returned by the query would be transient and not take up
> space in the cache.

Before I forget, if you have setTransient you could also have
  query.setReuseInstances(true);

When using iterate() Hibernate could reuse the same Cat for each call to
next(). I imagine this could speed up processing quite a bit, especially
if bean reflection could be avoided.

-Chris

Re: [Hibernate] Using CLOBs

[Ugo C. <u....@cb...>]

Gavin King wrote:
> The name of the type is "clob", and the expected property type
> is java.sql.Clob. Note that there are restrictions upon what
> you can do with Clobs (they can't be used outside of transaction,
> for example).
> 
> You should also take notice of Hibernate.createClob().

Thanks. Everything works fine ... at least in theory ...

If you use Oracle 8i and try to store more than 4000 characters, this is 
what you get:

java.sql.SQLException: Data size bigger than max size for this type: 7894
         at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
         at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
         at oracle.jdbc.ttc7.TTCItem.setArrayData(TTCItem.java:147)
         at 
oracle.jdbc.dbaccess.DBDataSetImpl.setBytesBindItem(DBDataSetImpl.java:2461)
         at 
oracle.jdbc.driver.OraclePreparedStatement.setItem(OraclePreparedStatement.java:1155)
         at 
oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:1572)
         at 
org.apache.commons.dbcp.DelegatingPreparedStatement.setString(DelegatingPreparedStatement.java:217)
         at 
org.apache.commons.dbcp.DelegatingPreparedStatement.setString(DelegatingPreparedStatement.java:217)
         at cirrus.hibernate.type.ClobType.set(ClobType.java:14)

Actually, having already been through the nightmare that CLOB support in 
Oracle is, I expected nothing more and nothing less.

AFAIK there is just one way to store more than 4000 characters in an 
Oracle CLOB via JDBC:

- start a transaction
- insert into tablename(id, clob_field) values('X', empty_clob())
- select clob_field from tablename where id = 'X' for update of clob_field
- ResultSet rs = (oracle.jdbc.driver.OracleResultSet) ps.executeQuery();
   if (rs.next()) {
     oracle.sql.CLOB clob = rs.getCLOB(1);
     java.io.Writer pw = clob.getCharacterOutputStream();
     pw.write(content);
     pw.close();
   }
- commit

If this isn't a PITA, I don't know what is :-(

Do you think it's going to be difficult to implement this rigamarole in 
Hibernate?

	Ugo

-- 
Ugo Cei - http://www.beblogging.com/blog/