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]
