Re: working on Oracle LOBs

[Thomas Mahler <th...@apache.org>]

Hi Michael,

Michael Mogley wrote:
 > Before I attempt major surgery, could someone explain why the flow of
 > control for the store operation isn't as follows?
 >
 > PersistenceBroker -> Platform -> JdbcAccess
 >
 > Shouldn't the platform dictate what type of access is being used and
 > HOW that access is used?

We did introduce the Platform interface at a late stage of OJB design. 
In the early days I tried to remain as db indepented as possible.

That's why Platform is a simple callback interface that is only called 
at specific spots, where it can be avoided to do something db specific.

Thus the flow of control may seem a bit strange at first sight.

 > In order to store an object where one or
 > more fields map to an Oracle LOB, I have to perform two separate Sql
 > operations: an INSERT and a SELECT FOR UPDATE.

Why is it so difficult to store a LOB with oracle?

I recently commited the following stuff to PlatformDefaultImpl:
     public void setObjectForStatement(PreparedStatement ps, int index, 
Object value, int sqlType)
             throws SQLException
     {
         if ((value instanceof String) && (sqlType == Types.LONGVARCHAR))
         {
             String s = (String) value;
             ps.setCharacterStream(index, new StringReader(s), s.length());
         }
         else
         {
             ps.setObject(index, value, sqlType);
         }
     }

IMO something similar should be sufficient for a CLOB or BLOB too?


If we flip around the
 > existing dependency order to the above, it would help me a great
 > deal.  In this case, we would have something like:
 >
 > PersistenceBroker.store(classDescriptor, object)
 >
 > -> Platform.executeInsert(classDescriptor, object)
 > [PlatformOracleImpl]
 >
 > -> JdbcAccess.executeInsert(fieldDescriptors, object) [INSERT INTO
 > MyTable IntegerCol1, IntegerCol2, ClobCol VALUES (1, 2,
 > EMPTY_CLOB())]
 >
 > -> JdbcAccess.executeUpdate(lobDescriptors, object) [SELECT ClobCol
 > FROM MyTable FOR UPDATE]
 >
 > Here, JdbcAccess is modified to take an arbitrary array of field
 > descriptors to insert/update.  This would make it easier for the
 > particular Platform implementation to manipulate JdbcAccess to its
 > ends.
 >
 > In addition, we would need a FunctionFieldDescriptor class that would
 > allow modelling of "EMPTY_CLOB()" as a valid field descriptor used
 > during the sql generation.
 >
 > Is anyone adamantly against changing the design in this way?  Any
 > better suggestions on how to approach the problem?
 >

I'm not against changes. But the current design has been stable for some 
time for a variety of DB platforms! Before performing such a "major 
surgery" I'd like to understand if it is unavoidable.

What do the Oracle experts say?

cheers,
Thomas

 > Michael

Re: working on Oracle LOBs

[Thomas Mahler <th...@apache.org>]

Hi Michael,

Michael Mogley wrote:
<snip>
> 
> Understood.  To me, db independence has little to do with doing
> something db-specific and much more to do with presenting an interface
> that is independent of the underlying db mechanism.  In fact, you
> sometimes HAVE to rely on db-specific operations to fulfill the contract
> of your generic interface, since different dbs often accomplish the same
> thing in different ways.  Granted, this usually applies to more advanced
> features. I don't think that means OJB should settle for a
> lowest-common-denominator approach. That would render it useless for
> many applications.

+1. In fact we provide platform specific operations for 10 differnt 
platforms already!

<snip>
> 
>>Object value, int sqlType)
>>             throws SQLException
>>     {
>>         if ((value instanceof String) && (sqlType ==
> 
> Types.LONGVARCHAR))
> 
>>         {
>>             String s = (String) value;
>>             ps.setCharacterStream(index, new StringReader(s),
>>s.length());
>>         }
>>         else
>>         {
>>             ps.setObject(index, value, sqlType);
>>         }
>>     }
>>
>>IMO something similar should be sufficient for a CLOB or BLOB too?
> 
> 
> As I explained in a previous message, what we would consider an obvious
> set call will end up truncating the data to 4K bytes.

Even if using a Stream as in my example?

>  I have seen this
> in my own tests. Oracle may end up change the driver at some point to be
> smarter about this, but there's no telling when or even if this will
> ever happen.
> 
> According to Oracle's documentation on the JDBC LOB issue, the only way
> to do store LOB data is to first reset the LOB via an Oracle function
> and then acquire a lock on it via a SELECT FOR UPDATE call.  I sent some
> pseudocode last week demonstrating this.
> 
> 
>>If we flip around the
>> > existing dependency order to the above, it would help me a great
>> > deal.  In this case, we would have something like:
>> >
>> > PersistenceBroker.store(classDescriptor, object)
>> >
>> > -> Platform.executeInsert(classDescriptor, object)
>> > [PlatformOracleImpl]
>> >
>> > -> JdbcAccess.executeInsert(fieldDescriptors, object) [INSERT INTO
>> > MyTable IntegerCol1, IntegerCol2, ClobCol VALUES (1, 2,
>> > EMPTY_CLOB())]
>> >
>> > -> JdbcAccess.executeUpdate(lobDescriptors, object) [SELECT ClobCol
>> > FROM MyTable FOR UPDATE]
>> >
>> > Here, JdbcAccess is modified to take an arbitrary array of field
>> > descriptors to insert/update.  This would make it easier for the
>> > particular Platform implementation to manipulate JdbcAccess to its
>> > ends.
>> >


I woul prefer to not trigger Platform from the PB. Currently the PB does 
not know anything about platforms. It simply uses JDBCAccess for all 
operations against the db. I'd like to maintain this single interface to 
allow easy plugability.

The sequence could be:
PersistenceBroker.store(classDescriptor, object)
-> JdbcAccess.executeInsert(classDescriptor, object)
-> Platform.executeInsert(classDescriptor, object)
   [PlatformOracleImpl]
etc...

>> > In addition, we would need a FunctionFieldDescriptor class that
> 
> would
> 
>> > allow modelling of "EMPTY_CLOB()" as a valid field descriptor used
>> > during the sql generation.

Why do we need this? Can't this be hidden somewhere in the 
PlatformOracleImpl ?

>> >
>> > Is anyone adamantly against changing the design in this way?  Any
>> > better suggestions on how to approach the problem?
>> >
>>
>>I'm not against changes. But the current design has been stable for
> 
> some
> 
>>time for a variety of DB platforms! Before performing such a "major
>>surgery" I'd like to understand if it is unavoidable.
> 
> 
> I understand that.  However, it really seems like a case of the tail
> wagging the dog.  In supporting a variety of underlying db mechanisms,
> you must allow for vendors who don't always strictly hold to the Jdbc
> interface. Whether or not they should is irrelevant. They are big in the
> marketplace. In this case, the platforms really become providers that
> fulfill the Ojb contracts to perform services -- HOWEVER that is
> accomplished -- whether it be strictly through the java.sql interface or
> straight SQL calls.

Mhh. Of course we should provide support for the Oracle platform. But I 
don't see why we should change our overall design to such a large extend 
only because Oracle provides so poorly designed LOBs.
I don't want to become the tail wagged by Oracle !


> If we are to take this approach -- which I believe gives the best
> decoupling between the Ojb interface and the persistence mechanism -- we
> are forced to reorder the Platform (Provider) as the driving class.  In
> this way, the provider USES the access mechanism exactly how it needs to
> in order to fulfill its end of the contract with Ojb.
> 
> Right now, the access mechanism is using the provider. Which means that
> any time some hunky feature of the db needs to be made available to
> users, a bunch of classes and top-level interface -- including
> JdbcAccess, SqlGenerator, StatementManager, and Platform -- will likely
> have to be changed to accommodate.  IMO, this is not a maintainable
> approach to Ojb's future development.

I don't believe this is true. The current approach has been proven for a 
large variety of platforms. The overall design has remained clean.
We can still implement Oracle specifics by just using Platform as a 
secondary callback interface and not as a driving instance.

Your arguments (as well as my own) provide "soft" (i.e. aesthetic, 
philosophic, etc.) reasons.
I see no "hard" reasons (e.g. some feature can not be implemented due to 
technical restrictions, etc.) why one approach is better than the other.
Giving this, I suggest to keep the design to minimize changes.

cheers,
Thomas

> 
> Thoughts?  Anyone want to kick my ass for such blasphemy?
> 
> Michael
> 
> 
>>What do the Oracle experts say?
>>
>>cheers,
>>Thomas
>>
>> > Michael
>>
>>
>>
>>--
>>To unsubscribe, e-mail:
> 
> <ma...@jakarta.apache.org>
> 
>>For additional commands, e-mail:
> 
> <ma...@jakarta.apache.org>
> 
> 
> 
> --
> To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
> 
> 
> 
> 

RE: working on Oracle LOBs

[Michael Mogley <mi...@verizon.net>]

Hi Thomas,

> Before I attempt major surgery, could someone explain why the flow of
>  > control for the store operation isn't as follows?
>  >
>  > PersistenceBroker -> Platform -> JdbcAccess
>  >
>  > Shouldn't the platform dictate what type of access is being used
and
>  > HOW that access is used?
> 
> We did introduce the Platform interface at a late stage of OJB design.
> In the early days I tried to remain as db indepented as possible.
> 
> That's why Platform is a simple callback interface that is only called
> at specific spots, where it can be avoided to do something db
specific.

Understood.  To me, db independence has little to do with doing
something db-specific and much more to do with presenting an interface
that is independent of the underlying db mechanism.  In fact, you
sometimes HAVE to rely on db-specific operations to fulfill the contract
of your generic interface, since different dbs often accomplish the same
thing in different ways.  Granted, this usually applies to more advanced
features. I don't think that means OJB should settle for a
lowest-common-denominator approach. That would render it useless for
many applications.

> Thus the flow of control may seem a bit strange at first sight.
> 
>  > In order to store an object where one or
>  > more fields map to an Oracle LOB, I have to perform two separate
Sql
>  > operations: an INSERT and a SELECT FOR UPDATE.
> 
> Why is it so difficult to store a LOB with oracle?
> 
> I recently commited the following stuff to PlatformDefaultImpl:
>      public void setObjectForStatement(PreparedStatement ps, int
index,
> Object value, int sqlType)
>              throws SQLException
>      {
>          if ((value instanceof String) && (sqlType ==
Types.LONGVARCHAR))
>          {
>              String s = (String) value;
>              ps.setCharacterStream(index, new StringReader(s),
> s.length());
>          }
>          else
>          {
>              ps.setObject(index, value, sqlType);
>          }
>      }
> 
> IMO something similar should be sufficient for a CLOB or BLOB too?

As I explained in a previous message, what we would consider an obvious
set call will end up truncating the data to 4K bytes.  I have seen this
in my own tests. Oracle may end up change the driver at some point to be
smarter about this, but there's no telling when or even if this will
ever happen.

According to Oracle's documentation on the JDBC LOB issue, the only way
to do store LOB data is to first reset the LOB via an Oracle function
and then acquire a lock on it via a SELECT FOR UPDATE call.  I sent some
pseudocode last week demonstrating this.

> If we flip around the
>  > existing dependency order to the above, it would help me a great
>  > deal.  In this case, we would have something like:
>  >
>  > PersistenceBroker.store(classDescriptor, object)
>  >
>  > -> Platform.executeInsert(classDescriptor, object)
>  > [PlatformOracleImpl]
>  >
>  > -> JdbcAccess.executeInsert(fieldDescriptors, object) [INSERT INTO
>  > MyTable IntegerCol1, IntegerCol2, ClobCol VALUES (1, 2,
>  > EMPTY_CLOB())]
>  >
>  > -> JdbcAccess.executeUpdate(lobDescriptors, object) [SELECT ClobCol
>  > FROM MyTable FOR UPDATE]
>  >
>  > Here, JdbcAccess is modified to take an arbitrary array of field
>  > descriptors to insert/update.  This would make it easier for the
>  > particular Platform implementation to manipulate JdbcAccess to its
>  > ends.
>  >
>  > In addition, we would need a FunctionFieldDescriptor class that
would
>  > allow modelling of "EMPTY_CLOB()" as a valid field descriptor used
>  > during the sql generation.
>  >
>  > Is anyone adamantly against changing the design in this way?  Any
>  > better suggestions on how to approach the problem?
>  >
> 
> I'm not against changes. But the current design has been stable for
some
> time for a variety of DB platforms! Before performing such a "major
> surgery" I'd like to understand if it is unavoidable.

I understand that.  However, it really seems like a case of the tail
wagging the dog.  In supporting a variety of underlying db mechanisms,
you must allow for vendors who don't always strictly hold to the Jdbc
interface. Whether or not they should is irrelevant. They are big in the
marketplace. In this case, the platforms really become providers that
fulfill the Ojb contracts to perform services -- HOWEVER that is
accomplished -- whether it be strictly through the java.sql interface or
straight SQL calls.

If we are to take this approach -- which I believe gives the best
decoupling between the Ojb interface and the persistence mechanism -- we
are forced to reorder the Platform (Provider) as the driving class.  In
this way, the provider USES the access mechanism exactly how it needs to
in order to fulfill its end of the contract with Ojb.

Right now, the access mechanism is using the provider. Which means that
any time some hunky feature of the db needs to be made available to
users, a bunch of classes and top-level interface -- including
JdbcAccess, SqlGenerator, StatementManager, and Platform -- will likely
have to be changed to accommodate.  IMO, this is not a maintainable
approach to Ojb's future development.

Thoughts?  Anyone want to kick my ass for such blasphemy?

Michael

> 
> What do the Oracle experts say?
> 
> cheers,
> Thomas
> 
>  > Michael
> 
> 
> 
> --
> To unsubscribe, e-mail:
<ma...@jakarta.apache.org>
> For additional commands, e-mail:
<ma...@jakarta.apache.org>