The currval column of a sequence in Oracle will always return the same value as
the last nextval in the current transaction. You should never have problems with
concurrent access by "someone else" unless you are not in a transaction. But I
have no idea how you'd achieve not being in a transaction in Oracle.
There is no need to retrieve the nextval in you bean before the insert. We used
the approach of retrieving currval after an insert for 10 years and have never
experienced a problem.
--Victor
Guy Rouillier wrote:
> This approach will not work reliably. Between your insert of nextval and
> your select of currval, someone else may have inserted another row, such
> that your currval will actuall retrieve the wrong currval. I suppose you
> could address this with synchronized, but a better way (IMO) is to retrieve
> the nextval into your code. You can then use it for the insert and also no
> the value for any further use you may have, and not have to worry about
> multi-user issues.
>
> We always wrap the sequence with a getter function, and this has saved our
> hides many times. For example, let's say you start by using the sequence
> number as your primary key. Then someone comes along and decides that they
> want the IDs to look like "CS00001". You may have to change code in
> several places if you just access the sequence directly, but if you wrap it
> in a getter function, you don't have to change anything but that getter
> function.
>
> ----- Original Message -----
> From: <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Cc: "Sacha Labourey" <[EMAIL PROTECTED]>
> Sent: Monday, May 07, 2001 3:36 PM
> Subject: Re: [JBoss-user] Oracle Sequences PK in Session bean
>
> >
> >
> > (Note that this is Oracle specific - ie. non portable)
> > I'm not sure if this will help, since it is not a Sesion bean as you
> asked,
> > but...
> >
> > This is the ejbCreate() method for an Entity Bean that I have come up with
> to
> > insert a new record into an Oracle Database Table where the primary key is
> > generated using a Sequence.
> >
> > After inserting the record, I get the current sequence number and set the
> > Primary Key to it. I originally tried to do this step in the
> ejbPostCreate(),
> > but I needed to obtain the value for the Primary Key prior to returning
> from the
> > ejbCreate().
> >
> >
> >
> > I hope that this helps
> >
> >
> >
> > Tim
> >
> >
> > public ValidationRulePK ejbCreate(long attrID, int validationID, String
> name,
> > byte validationType) throws CreateException, SQLException
> > {
> > ValidationRulePK primaryKey = new ValidationRulePK();
> > // Validate the parameters, throw a CreateException on error.
> > if ((attrID < 1) || (validationID < 1) || (name == null) ||
> > (validationType != 'L' && validationType != 'R' && validationType !=
> 'T'))
> > {
> > throw new CreateException("Invalid Parameters");
> > }
> > this.AttrID = attrID;
> > this.ValidationID = validationID;
> > this.Name = name;
> > this.ValidationType = validationType;
> > try
> > {
> > openConnection();
> > // Insert a new (non-blank) entry in the database
> > pStatement = con.prepareStatement("insert into VALIDATION_RULE "
> +
> > "(RULE_ID, ATTR_ID, VALIDATION_ID, NAME, VALIDATION_TYPE) "
> +
> > "values (ValidationRuleSeq.nextval, ?, ?, ?, ?)");
> > pStatement.setLong(1, AttrID);
> > pStatement.setInt(2, ValidationID);
> > pStatement.setString(3, Name);
> > pStatement.setByte(4, ValidationType);
> > if (1 != pStatement.executeUpdate())
> > {
> > log.error("Failed to insert record into VALIDATION_RULE");
> > }
> > else
> > {
> > log.debug("Inserted record into VALIDATION_RULE");
> > }
> > // Get the Sequence number and timestamp just created
> > pStatement = con.prepareStatement("select
> ValidationRuleSeq.currval
> > from dual");
> > result = pStatement.executeQuery();
> > if (result.next())
> > {
> > primaryKey.RuleID = this.RuleID = result.getInt(1);
> > //
> > log.debug("RuleID: " + String.valueOf(this.RuleID));
> > }
> > else
> > {
> > throw new EJBException();
> > }
> > }
> > catch (SQLException se)
> > {
> > log.error("Failed to insert record into VALIDATION_RULE");
> > log.exception(se);
> > throw se;
> > }
> > finally
> > {
> > closeConnection();
> > }
> > return primaryKey;
> > }
> >
> > ------------------------------------------------------- Original
> Message ----------------------------------------------
> > From: "Sacha Labourey" <[EMAIL PROTECTED]>
> > To: "jBoss-User Mailing List" <[EMAIL PROTECTED]>
> > Date: Mon, 7 May 2001 18:40:06 +0200
> > Message-ID: <[EMAIL PROTECTED]>
> > MIME-Version: 1.0
> > Content-Type: text/plain; charset="iso-8859-1"
> > Content-Transfer-Encoding: quoted-printable
> > Importance: Normal
> > Subject: [JBoss-user] Oracle Sequences PK in Session bean
> > Sender: [EMAIL PROTECTED]
> > Precedence: bulk
> > Reply-To: [EMAIL PROTECTED]
> > List-Help: <mailto:[EMAIL PROTECTED]?subject=help>
> > List-Post: <mailto:[EMAIL PROTECTED]>
> > List-Subscribe: <http://lists.sourceforge.net/lists/listinfo/jboss-user>,
> <
> > mailto:[EMAIL PROTECTED]?subject=subscribe>
> > List-Id: The JBoss User main mailing list
> <jboss-user.lists.sourceforge.net>
> > List-Unsubscribe:
> <http://lists.sourceforge.net/lists/listinfo/jboss-user>, <
> > mailto:[EMAIL PROTECTED]?subject=unsubscribe>
> > List-Archive: <http://lists.sourceforge.net/archives//jboss-user/>
> >
> >
> > Hello,
> >
> > Does someone already has developped a session bean for JBoss which sole
> purpose
> > is to get an Oracle Sequence value from an underlying Oracle database? If
> it is
> > the case and you accept to share it, I take it with joice!
> >
> > Just not to develop it twice... ;)
> >
> > Thank you.
> >
> > Cheers,
> >
> >
> >
> >
> > Sacha
> >
> >
> >
> >
> >
> > _______________________________________________
> > JBoss-user mailing list
> > [EMAIL PROTECTED]
> > http://lists.sourceforge.net/lists/listinfo/jboss-user
> >
>
> _______________________________________________
> JBoss-user mailing list
> [EMAIL PROTECTED]
> http://lists.sourceforge.net/lists/listinfo/jboss-user
_______________________________________________
JBoss-user mailing list
[EMAIL PROTECTED]
http://lists.sourceforge.net/lists/listinfo/jboss-user
