You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Mike Jackson <mj...@cdi-hq.com> on 2005/04/15 23:44:50 UTC
Sequence managers and insert triggers
I'm running on an Oracle database (8i) with insert triggers to create
artifical keys for my table records. The howtos seemed to indicate that
SequenceManagerNativeImpl might be the proper sequence manager to use but
the keys are coming out goofy, so clearly I'm not understanding or
something. Which of the sequence managers should I be using? I've got
other applications using the same database, so removing the triggers and
using the native sequences via OJB isn't an option at this time.
--mikej
-=-----
mike jackson
mjackson@cdi-hq.com
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Sequence managers and insert triggers
Posted by Martin Kalén <mk...@apache.org>.
Mike Jackson wrote:
> I'm running on an Oracle database (8i) with insert triggers to create
> artifical keys for my table records. The howtos seemed to indicate that
> SequenceManagerNativeImpl might be the proper sequence manager to use but
> the keys are coming out goofy, so clearly I'm not understanding or
> something.
Like Armin pointed out, you can use the "NextVal" SequenceManager with Oracle.
However, that setup is meant to be used with DB-sequences, not triggers.
If you have a table T with a simple integer PK field T_ID for which you want
autoincement in OJB, you can use a DB sequence (let's call it SEQ_T) and
the NextVal SequenceManager. In your OJB connection descriptor you would then
configure something like this:
<jdbc-connection-descriptor jcd-alias="<your alias>" ...
...
<sequence-manager className="org.apache.ojb.broker.util.sequence.SequenceManagerNextValImpl">
<attribute attribute-name="autoNaming" attribute-value="false"/>
</sequence-manager>
In your OJB class descriptor you would map the autoincrement for T.PK to SEQ_T with:
<class-descriptor class="<package.class>" table="T">
<field-descriptor column="T_ID" name="id" ... primarykey="true" autoincrement="true" sequence-name="SEQ_T"
If you want to use a stored procedure for the insert operations,
Ron Gallagher's method is the way to go.
If you end up somewhere "in between", eg you have a trigger assigning some values on
insert and your only problem is that OJB won't "see" this change and Java objects
in memory are out of synch with DB, the afterStore hook that Armin pointed to is a good
place to put programmatic calls for refreshing objects in memory.
Or if you have a centralised point for storing your objects in a back-end you can
add your own after-store calls there.
Refreshing a field in memory after storage can look eg like this (using PB API):
public static final String[] ATTRIBUTES = new String[]{"fieldName"};
...
broker.removeFromCache(object);
Criteria crit = ... construct a criteria for readback of object ...
query = QueryFactory.newReportQuery(object.getClass(), ATTRIBUTES, crit, false);
Iterator iter = broker.getReportQueryIteratorByQuery(query);
if (iter.hasNext()) {
Object[] columns = (Object[]) iter.next();
ClassDescriptor cld = broker.getClassDescriptor(clazz);
PersistentField ojbField = cld.getFieldDescriptorByName("fieldName").getPersistentField();
ojbField.set(object, columns[0]);
if (iterator instanceof OJBIterator) {
((OJBIterator) iterator).releaseDbResources();
}
}
Might not be the most beautiful programming pattern, but it's a starting point.
Regards,
Martin
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Sequence managers and insert triggers
Posted by Armin Waibel <ar...@apache.org>.
Mike Jackson wrote:
> That's basically how it works. We have triggers which upon insert will
> populate the "autonumber" fields. The triggers didn't look to see if the
> autonumber was already populated so if I had used the sequence based
> implementation I'd have ended up incrementing the sequence twice and none of
> my fk's would have the proper information in them. This would probably
> cause integrity constraint violations, but we've since changed the triggers
> to detect if the autonumber is populated. The dba isn't completely happy
> with this solution (ojb gets the sequence numbers and populates, anything
> else doesn't), but he's ok with it. The "normal" route for records is to be
> inserted for the master record with a stored procedure, but the "child"
> tables are all direct inserts with triggers to take care of things.
>
sounds complex, the only alternative I know is to implement your own
SequenceManager:
public Object getUniqueValue(FieldDescriptor field)
this method was called by OJB to obtain a new sequence key *before* the
object is inserted. In your case return a dummy value, see
SequenceManagerNativeImpl implementation (e.g. return a negative count
number)
public void afterStore(JdbcAccess dbAccess, ClassDescriptor cld, Object obj)
this method was called after the insert statement was executed. Here you
can start working and e.g. call a stored procedure to lookup the
sequence key and set the PK field in the object, see
SequenceManagerNativeImpl implementation.
public void setReferenceFKs(Object obj, ClassDescriptor cld)
was no longer used in OJB, make a noop.
Don't know if this will help you, but a bird in the hand is worth two in
the bush ;-)
regards,
Armin
> --mikej
> -=-----
> mike jackson
> mjackson@cdi-hq.com
>
>
>
>>-----Original Message-----
>>From: Armin Waibel [mailto:arminw@apache.org]
>>Sent: Friday, April 15, 2005 3:42 PM
>>To: OJB Users List
>>Subject: Re: Sequence managers and insert triggers
>>
>>
>>Hi Mike,
>>
>>think I don't understand your problem (never used insert
>>trigger), so be
>>patience. If you are using a insert trigger I assume the
>>trigger set the
>>PK value on insert of new objects? Or do you using a stored
>>procedure to
>>get the key or to insert the whole object?
>>
>>OJB supports database generated sequences. A sequence manager
>>supporting
>>this kind of key generation can be found here:
>>http://db.apache.org/ojb/docu/guides/sequencemanager.html#Data
>>base+sequences+based+implementation
>>
>>The SequenceManagerNativeImpl supports database identity
>>columns, don't
>>know if Oracle supports this (the exception indicate it doesn't).
>>
>>regards,
>>Armin
>>
>>Mike Jackson wrote:
>>
>>>Actually I take it back, I was getting a -2, but when I got the
>>>attributes on the identity column set properly I'm getting a
>>>"java.lang.UnsupportedOperationException: This feature is not
>>>supported by this implementation". Perhaps this is the
>>
>>wrong sequence
>>
>>>manager to use on oracle?
>>>
>>>--mikej
>>>-=-----
>>>mike jackson
>>>mjackson@cdi-hq.com
>>>
>>>
>>>
>>>
>>>>-----Original Message-----
>>>>From: Mike Jackson [mailto:mjackson@cdi-hq.com]
>>>>Sent: Friday, April 15, 2005 2:45 PM
>>>>To: 'OJB Users List'
>>>>Subject: Sequence managers and insert triggers
>>>>
>>>>
>>>>I'm running on an Oracle database (8i) with insert triggers
>>>>to create artifical keys for my table records. The howtos
>>>>seemed to indicate that SequenceManagerNativeImpl might be
>>>>the proper sequence manager to use but the keys are coming
>>>>out goofy, so clearly I'm not understanding or something.
>>>>Which of the sequence managers should I be using? I've got
>>>>other applications using the same database, so removing the
>>>>triggers and using the native sequences via OJB isn't an
>>>>option at this time.
>>>>
>>>>--mikej
>>>>-=-----
>>>>mike jackson
>>>>mjackson@cdi-hq.com
>>>>
>>>>
>>>>
>>>>------------------------------------------------------------
>>
>>---------
>>
>>>>To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>>>For additional commands, e-mail: ojb-user-help@db.apache.org
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>---------------------------------------------------------------------
>>
>>>To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>>For additional commands, e-mail: ojb-user-help@db.apache.org
>>>
>>>
>>>
>>
>>---------------------------------------------------------------------
>>To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
RE: Sequence managers and insert triggers
Posted by Ron Gallagher <ro...@bellsouth.net>.
Mike --
Have you considered having OJB use a stored procedure to handle the insert
operations? If you do so, you could have the stored procedure return to ojb
the 'auto-number' value that was assigned by the trigger. This is what I
had to do on a prior project, and that effort lead to the introduction of
stored procedure support for insert, update and delete operations. It's
been almost a year since I used Oracle in that manner, but I believe the
phrase that the SP would use to return the trigger-assigned auto-number to
it's 'caller' is "RETURNING". Check out
http://db.apache.org/ojb/docu/howtos/howto-work-with-stored-procedures.html,
and you'll see the examples that I posted when we implemented the SP support
for IUD operations. The example referenced above acquires the next value
from a sequence, uses it in the insert operation and returns it to the
caller. I believe you could do the same sort of thing with a value that's
assigned via the insert trigger.
Ron Gallagher
Atlanta, GA
-----Original Message-----
From: Mike Jackson [mailto:mjackson@cdi-hq.com]
Sent: Friday, April 15, 2005 7:10 PM
To: 'OJB Users List'
Subject: RE: Sequence managers and insert triggers
That's basically how it works. We have triggers which upon insert will
populate the "autonumber" fields. The triggers didn't look to see if the
autonumber was already populated so if I had used the sequence based
implementation I'd have ended up incrementing the sequence twice and none of
my fk's would have the proper information in them. This would probably
cause integrity constraint violations, but we've since changed the triggers
to detect if the autonumber is populated. The dba isn't completely happy
with this solution (ojb gets the sequence numbers and populates, anything
else doesn't), but he's ok with it. The "normal" route for records is to be
inserted for the master record with a stored procedure, but the "child"
tables are all direct inserts with triggers to take care of things.
--mikej
-=-----
mike jackson
mjackson@cdi-hq.com
> -----Original Message-----
> From: Armin Waibel [mailto:arminw@apache.org]
> Sent: Friday, April 15, 2005 3:42 PM
> To: OJB Users List
> Subject: Re: Sequence managers and insert triggers
>
>
> Hi Mike,
>
> think I don't understand your problem (never used insert trigger), so
> be patience. If you are using a insert trigger I assume the trigger
> set the PK value on insert of new objects? Or do you using a stored
> procedure to get the key or to insert the whole object?
>
> OJB supports database generated sequences. A sequence manager
> supporting this kind of key generation can be found here:
> http://db.apache.org/ojb/docu/guides/sequencemanager.html#Data
> base+sequences+based+implementation
>
> The SequenceManagerNativeImpl supports database identity columns,
> don't know if Oracle supports this (the exception indicate it
> doesn't).
>
> regards,
> Armin
>
> Mike Jackson wrote:
> > Actually I take it back, I was getting a -2, but when I got the
> > attributes on the identity column set properly I'm getting a
> > "java.lang.UnsupportedOperationException: This feature is not
> > supported by this implementation". Perhaps this is the
> wrong sequence
> > manager to use on oracle?
> >
> > --mikej
> > -=-----
> > mike jackson
> > mjackson@cdi-hq.com
> >
> >
> >
> >>-----Original Message-----
> >>From: Mike Jackson [mailto:mjackson@cdi-hq.com]
> >>Sent: Friday, April 15, 2005 2:45 PM
> >>To: 'OJB Users List'
> >>Subject: Sequence managers and insert triggers
> >>
> >>
> >>I'm running on an Oracle database (8i) with insert triggers to
> >>create artifical keys for my table records. The howtos seemed to
> >>indicate that SequenceManagerNativeImpl might be the proper sequence
> >>manager to use but the keys are coming out goofy, so clearly I'm not
> >>understanding or something.
> >>Which of the sequence managers should I be using? I've got other
> >>applications using the same database, so removing the triggers and
> >>using the native sequences via OJB isn't an option at this time.
> >>
> >>--mikej
> >>-=-----
> >>mike jackson
> >>mjackson@cdi-hq.com
> >>
> >>
> >>
> >>------------------------------------------------------------
> ---------
> >>To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> >>For additional commands, e-mail: ojb-user-help@db.apache.org
> >>
> >>
> >
> >
> >
> >
> >
> ---------------------------------------------------------------------
> > To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: ojb-user-help@db.apache.org
> >
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
RE: Sequence managers and insert triggers
Posted by Mike Jackson <mj...@cdi-hq.com>.
That's basically how it works. We have triggers which upon insert will
populate the "autonumber" fields. The triggers didn't look to see if the
autonumber was already populated so if I had used the sequence based
implementation I'd have ended up incrementing the sequence twice and none of
my fk's would have the proper information in them. This would probably
cause integrity constraint violations, but we've since changed the triggers
to detect if the autonumber is populated. The dba isn't completely happy
with this solution (ojb gets the sequence numbers and populates, anything
else doesn't), but he's ok with it. The "normal" route for records is to be
inserted for the master record with a stored procedure, but the "child"
tables are all direct inserts with triggers to take care of things.
--mikej
-=-----
mike jackson
mjackson@cdi-hq.com
> -----Original Message-----
> From: Armin Waibel [mailto:arminw@apache.org]
> Sent: Friday, April 15, 2005 3:42 PM
> To: OJB Users List
> Subject: Re: Sequence managers and insert triggers
>
>
> Hi Mike,
>
> think I don't understand your problem (never used insert
> trigger), so be
> patience. If you are using a insert trigger I assume the
> trigger set the
> PK value on insert of new objects? Or do you using a stored
> procedure to
> get the key or to insert the whole object?
>
> OJB supports database generated sequences. A sequence manager
> supporting
> this kind of key generation can be found here:
> http://db.apache.org/ojb/docu/guides/sequencemanager.html#Data
> base+sequences+based+implementation
>
> The SequenceManagerNativeImpl supports database identity
> columns, don't
> know if Oracle supports this (the exception indicate it doesn't).
>
> regards,
> Armin
>
> Mike Jackson wrote:
> > Actually I take it back, I was getting a -2, but when I got the
> > attributes on the identity column set properly I'm getting a
> > "java.lang.UnsupportedOperationException: This feature is not
> > supported by this implementation". Perhaps this is the
> wrong sequence
> > manager to use on oracle?
> >
> > --mikej
> > -=-----
> > mike jackson
> > mjackson@cdi-hq.com
> >
> >
> >
> >>-----Original Message-----
> >>From: Mike Jackson [mailto:mjackson@cdi-hq.com]
> >>Sent: Friday, April 15, 2005 2:45 PM
> >>To: 'OJB Users List'
> >>Subject: Sequence managers and insert triggers
> >>
> >>
> >>I'm running on an Oracle database (8i) with insert triggers
> >>to create artifical keys for my table records. The howtos
> >>seemed to indicate that SequenceManagerNativeImpl might be
> >>the proper sequence manager to use but the keys are coming
> >>out goofy, so clearly I'm not understanding or something.
> >>Which of the sequence managers should I be using? I've got
> >>other applications using the same database, so removing the
> >>triggers and using the native sequences via OJB isn't an
> >>option at this time.
> >>
> >>--mikej
> >>-=-----
> >>mike jackson
> >>mjackson@cdi-hq.com
> >>
> >>
> >>
> >>------------------------------------------------------------
> ---------
> >>To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> >>For additional commands, e-mail: ojb-user-help@db.apache.org
> >>
> >>
> >
> >
> >
> >
> >
> ---------------------------------------------------------------------
> > To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: ojb-user-help@db.apache.org
> >
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Sequence managers and insert triggers
Posted by Armin Waibel <ar...@apache.org>.
Hi Mike,
think I don't understand your problem (never used insert trigger), so be
patience. If you are using a insert trigger I assume the trigger set the
PK value on insert of new objects? Or do you using a stored procedure to
get the key or to insert the whole object?
OJB supports database generated sequences. A sequence manager supporting
this kind of key generation can be found here:
http://db.apache.org/ojb/docu/guides/sequencemanager.html#Database+sequences+based+implementation
The SequenceManagerNativeImpl supports database identity columns, don't
know if Oracle supports this (the exception indicate it doesn't).
regards,
Armin
Mike Jackson wrote:
> Actually I take it back, I was getting a -2, but when I got the attributes
> on the identity column set properly I'm getting a
> "java.lang.UnsupportedOperationException: This feature is not supported by
> this implementation". Perhaps this is the wrong sequence manager to use on
> oracle?
>
> --mikej
> -=-----
> mike jackson
> mjackson@cdi-hq.com
>
>
>
>>-----Original Message-----
>>From: Mike Jackson [mailto:mjackson@cdi-hq.com]
>>Sent: Friday, April 15, 2005 2:45 PM
>>To: 'OJB Users List'
>>Subject: Sequence managers and insert triggers
>>
>>
>>I'm running on an Oracle database (8i) with insert triggers
>>to create artifical keys for my table records. The howtos
>>seemed to indicate that SequenceManagerNativeImpl might be
>>the proper sequence manager to use but the keys are coming
>>out goofy, so clearly I'm not understanding or something.
>>Which of the sequence managers should I be using? I've got
>>other applications using the same database, so removing the
>>triggers and using the native sequences via OJB isn't an
>>option at this time.
>>
>>--mikej
>>-=-----
>>mike jackson
>>mjackson@cdi-hq.com
>>
>>
>>
>>---------------------------------------------------------------------
>>To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
RE: Sequence managers and insert triggers
Posted by Mike Jackson <mj...@cdi-hq.com>.
Actually I take it back, I was getting a -2, but when I got the attributes
on the identity column set properly I'm getting a
"java.lang.UnsupportedOperationException: This feature is not supported by
this implementation". Perhaps this is the wrong sequence manager to use on
oracle?
--mikej
-=-----
mike jackson
mjackson@cdi-hq.com
> -----Original Message-----
> From: Mike Jackson [mailto:mjackson@cdi-hq.com]
> Sent: Friday, April 15, 2005 2:45 PM
> To: 'OJB Users List'
> Subject: Sequence managers and insert triggers
>
>
> I'm running on an Oracle database (8i) with insert triggers
> to create artifical keys for my table records. The howtos
> seemed to indicate that SequenceManagerNativeImpl might be
> the proper sequence manager to use but the keys are coming
> out goofy, so clearly I'm not understanding or something.
> Which of the sequence managers should I be using? I've got
> other applications using the same database, so removing the
> triggers and using the native sequences via OJB isn't an
> option at this time.
>
> --mikej
> -=-----
> mike jackson
> mjackson@cdi-hq.com
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org