The sequence and trigger are defined as follows:
CREATE SEQUENCE label_id_seq
INCREMENT BY 1
START WITH 1
MINVALUE 1
MAXVALUE 999999999999999999999999999
NOCYCLE
NOORDER
NOCACHE
/
CREATE OR REPLACE TRIGGER label_id_bri
BEFORE
INSERT
ON labels
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
SELECT label_id_seq.nextval
INTO :NEW.LABEL_ID
FROM DUAL;
END;
-----Original Message-----
From: Ebersole, Steven [mailto:[EMAIL PROTECTED]]
Sent: 21 February 2003 16:09
To: 'OJB Users List'
Subject: RE: Oracle sequences/triggers
If its an actual TRIGGER, then just do a conditional to see if the id is
already set before you grab it from the sequence...
If its a DEFAULT attribute, then I don't know how you would get around it.
|-----Original Message-----
|From: O'Reilly John [mailto:[EMAIL PROTECTED]]
|Sent: Friday, February 21, 2003 9:55 AM
|To: 'OJB Users List'
|Subject: RE: Oracle sequences/triggers
|
|
|I could drop the trigger and it would probably work.
|However, I need the
|trigger in place as the data can be loaded from other sources.
|
|
|-----Original Message-----
|From: Andrew Gilbert [mailto:[EMAIL PROTECTED]]
|Sent: 21 February 2003 15:51
|To: OJB Users List
|Subject: RE: Oracle sequences/triggers
|
|
|Are you mixing strategies? Drop the trigger in case 2,
|does it work?
|
|
|-----Original Message-----
|From: O'Reilly John [mailto:[EMAIL PROTECTED]]
|Sent: Friday, February 21, 2003 10:23 AM
|To: '[EMAIL PROTECTED]'
|Subject: Oracle sequences/triggers
|
|
|
|Last November a message was posted regarding the use of
|Oracle Sequences. I
|was wondering if somone was able to get this working.
|http://archives.apache.org/eyebrowse/ReadMsg?listName=ojb-u
|[EMAIL PROTECTED]
|e.org&msgId=528894
|
|As posted, I have defined a trigger based oracle sequence
|such that the DB
|will generate a new primary key when inserting a record.
|I've tried two
|approaches with OJB.
|
|
|1. Don't define a sequence and define primary key field as follows:
|
| <field-descriptor name="label_Id" column="LABEL_ID"
|jdbc-type="INTEGER"
| primarykey="true"/>
|
|
|In this case the following sql is generated when I create
|an object. The
|primary key gets created by the DB. However, OJB doesn't
|know what this is
|and therefore cannot create foreign keys correctly.
|
|SELECT LABEL_ID,RECORD_COMPANY_ID,LABEL_NAME FROM labels
|WHERE LABEL_ID =
|'0'
|INSERT INTO labels (LABEL_ID,LABEL_NAME,RECORD_COMPANY_ID)
|VALUES ( '0',
|'some label', '11' )
|
|
|
|2. Define a sequence manager
|
| <sequence-manager
|className="org.apache.ojb.broker.util.sequence.SequenceMana
|gerNextValImpl"/>
|
|and define primary key as follows:
|
| <field-descriptor name="label_Id" column="LABEL_ID"
|jdbc-type="INTEGER"
| primarykey="true"
| autoincrement="true"
| sequence-name="label_id_seq"
| />
|
|In this case the following sql is generated when I create
|an object.
|
|select label_id_seq.nextval from dual
|SELECT LABEL_ID,RECORD_COMPANY_ID,LABEL_NAME FROM labels
|WHERE LABEL_ID =
|'664'
|INSERT INTO labels (LABEL_ID,LABEL_NAME,RECORD_COMPANY_ID)
|VALUES ( '664',
|'some label', '11' )
|
|The problem is that the id created in the DB is actaully
|665 because of the
|trigger on the insert. Again, the foreign key would be incorrect.
|
|
|Are there any other solutions?
|
|We are in the processing of evaluating a move from Castor
|to OJB. In castor
|this is handled using the following sql:
|
|{call INSERT INTO "SALES"
|("SALES_YEAR","SALES_QUARTER","RECORD_COMPANY_ID")
|VALUES ('1999','1','') RETURNING "SALES_ID" INTO null}
|
|
|BTW, some of the posts refer to an Oracle specific
|sequencemanager -
|however, I can't find this in the latest distribution (0.9.9).
|
|Regards,
|John O'Reilly
|
|
|***********************************************************
|*************
|This e-mail and any files transmitted with it are
|confidential and may be
|privileged and are intended solely for the individual
|named/ for the use of
|the individual or entity to whom they are addressed.If you
|are not the
|intended addressee, you should not disseminate, distribute
|or copy this
|e-mail.Please notify the sender immediately if you have
|received this e-mail
|by mistake and delete this e-mail from your system.If you
|are not the
|intended recipient, you are notified that reviewing,
|disclosing, copying,
|distributing or taking any action in reliance on the
|contents of this e-mail
|is strictly prohibited.Please note that any views or
|opinions expressed in
|this e-mail are solely those of the author and do not
|necessarily represent
|those of Traventec Limited.E-mail transmission cannot be
|guaranteed to be
|secure or error-free as information could be intercepted,
|corrupted, lost,
|destroyed, or arrive late or incomplete.Traventec Limited
|therefore does not
|accept liability for any errors or omissions in the
|contents of this
|message, which arise as a result of e-mail
|transmission.The recipient should
|check this e-mail and any attachments for the presence of
|viruses.This
|e-mail has been swept for computer viruses however
|Traventec Limited accepts
|no liability for any damage caused by any virus
|transmitted by this e-mail.
|
|-----------------------------------------------------------
|----------
|To unsubscribe, e-mail: [EMAIL PROTECTED]
|For additional commands, e-mail: [EMAIL PROTECTED]
|
|
|-----------------------------------------------------------
|----------
|To unsubscribe, e-mail: [EMAIL PROTECTED]
|For additional commands, e-mail: [EMAIL PROTECTED]
|***********************************************************
|*************
|This e-mail and any files transmitted with it are
|confidential and may be
|privileged and are intended solely for the individual
|named/ for the use of
|the individual or entity to whom they are addressed.If you
|are not the
|intended addressee, you should not disseminate, distribute
|or copy this
|e-mail.Please notify the sender immediately if you have
|received this e-mail
|by mistake and delete this e-mail from your system.If you
|are not the
|intended recipient, you are notified that reviewing,
|disclosing, copying,
|distributing or taking any action in reliance on the
|contents of this e-mail
|is strictly prohibited.Please note that any views or
|opinions expressed in
|this e-mail are solely those of the author and do not
|necessarily represent
|those of Traventec Limited.E-mail transmission cannot be
|guaranteed to be
|secure or error-free as information could be intercepted,
|corrupted, lost,
|destroyed, or arrive late or incomplete.Traventec Limited
|therefore does not
|accept liability for any errors or omissions in the
|contents of this
|message, which arise as a result of e-mail
|transmission.The recipient should
|check this e-mail and any attachments for the presence of
|viruses.This
|e-mail has been swept for computer viruses however
|Traventec Limited accepts
|no liability for any damage caused by any virus
|transmitted by this e-mail.
|
|-----------------------------------------------------------
|----------
|To unsubscribe, e-mail: [EMAIL PROTECTED]
|For additional commands, e-mail: [EMAIL PROTECTED]
|
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
************************************************************************
This e-mail and any files transmitted with it are confidential and may be
privileged and are intended solely for the individual named/ for the use of
the individual or entity to whom they are addressed.If you are not the
intended addressee, you should not disseminate, distribute or copy this
e-mail.Please notify the sender immediately if you have received this e-mail
by mistake and delete this e-mail from your system.If you are not the
intended recipient, you are notified that reviewing, disclosing, copying,
distributing or taking any action in reliance on the contents of this e-mail
is strictly prohibited.Please note that any views or opinions expressed in
this e-mail are solely those of the author and do not necessarily represent
those of Traventec Limited.E-mail transmission cannot be guaranteed to be
secure or error-free as information could be intercepted, corrupted, lost,
destroyed, or arrive late or incomplete.Traventec Limited therefore does not
accept liability for any errors or omissions in the contents of this
message, which arise as a result of e-mail transmission.The recipient should
check this e-mail and any attachments for the presence of viruses.This
e-mail has been swept for computer viruses however Traventec Limited accepts
no liability for any damage caused by any virus transmitted by this e-mail.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
