Re: Next number processing -- JAVA400-L

Patrick,

That's basically the same as I do.  I consider using a seperate table much
safer than the alternative of reading the highest currently used value, say
SELECT MAX (KEY) FROM TABLE
and then adding one to it and doing the insert statement, because in theory
in a multiuser/multithread environment, two threads could read the previous
maximum value, add one to it and both try to insert with the same key.
Your version could fail in this way too, if two users ran the update
statement at similar times so they then retrieved the identical value with
the select statement then you'd also get two attempted inserts with the
same key, although I know you did say you were only dealing with a single
thread so shouldn't be an issue.

I check the update statement to ensure it updated from the value expected,
in case another thread modified the table in the meantime:

long value = 0;
            numeratorReadStatement.setString (1, tableName);
            ResultSet numeratorReadResultSet =
numeratorReadStatement.executeQuery ();
            try
            {
                  if (numeratorReadResultSet.next ())
                  {
                        // Found the last used numerator value in the file
- need add 1 to it which we do in the next loop
                        value = numeratorReadResultSet.getLong (1);

                        // When doing the update, check against the old
numerator value to ensure that someone else
                        // didn't swipe this value in the time since we
read it
                        int valueUpdated = 0;
                        while (valueUpdated == 0)
                        {
                              value++;
                              numeratorUpdateStatement.setLong (1, value);
                              numeratorUpdateStatement.setString (2,
tableName);
                              numeratorUpdateStatement.setLong (3, value -
1);
                              valueUpdated =
numeratorUpdateStatement.executeUpdate ();
                        }
                  }
                  else
                  {
                        // First numerator value for this file - return 1
and add record to numerator file
                        value = 1;

                        numeratorAddStatement.setString (1, tableName);
                        numeratorAddStatement.setLong (2, value);
                        numeratorAddStatement.executeUpdate ();
                  }
            }
            finally
            {
                  numeratorReadResultSet.close ();
            }

The statements are:
      public static String SQL_NUMERATOR_READ = "SELECT ENUMKEY FROM CPADEN
WHERE ENUMTBLNME = ?";
      public static String SQL_NUMERATOR_UPDATE = "UPDATE CPADEN SET
ENUMKEY = ? WHERE ENUMTBLNME = ? AND ENUMKEY = ?";
      public static String SQL_NUMERATOR_ADD = "INSERT INTO CPADEN
(ENUMTBLNME, ENUMKEY) VALUES (?,?)";

where CPADEN is the table which holds the next IDs, much like your MQ_PARM
table appears to work.  ENUMTBLNME is the key to this table much like your
ID.

Hope this helps,

Nigel Gay.



|---------+------------------------------------>
|         |           patrick.odowd@co-operativ|
|         |           ebank.co.uk              |
|         |           Sent by:                 |
|         |           java400-l-bounces@midrang|
|         |           e.com                    |
|         |                                    |
|         |                                    |
|         |           09/03/2004 08:03         |
|         |           Please respond to Java   |
|         |           Programming on and around|
|         |           the iSeries / AS400      |
|         |                                    |
|---------+------------------------------------>
  
>--------------------------------------------------------------------------------------------------------------|
  |                                                                             
                                 |
  |       To:       java400-l@xxxxxxxxxxxx                                      
                                 |
  |       cc:                                                                   
                                 |
  |       Subject:  Next number processing                                      
                                 |
  
>--------------------------------------------------------------------------------------------------------------|




Before acting on this e-mail or opening any attachment, you are advised to
read the disclaimer at the end of this mail.

Hi,
I have an Event file that has a field called ID as a primary key. ID is an
integer, incremented by 1 for each record added.

I am wondering what the options are for retrieving the next sequence number
for
the event records.

At the moment I have a separate file that stores the last event ID used,
when a
new event record needs to be added I call a static method on a database
helper
class that returns the next number, code below.

There will only be 1 thread incrementing this event. The volume of events
is
low, a few per day. It is a batch process.

What I have works but I am wondering if there a better way to do this?

Thanks in advance,
Pat

  public static synchronized int getNextNumber(Connection dbConn,
                                               String collection, String
key)
throws
      JS_Exception {
    Statement nextNumStm;
    ResultSet nextNumSet;
    String nextNumQry;
    int nextNum = -1;

    try {
      nextNumStm = dbConn.createStatement();
      nextNumStm.executeUpdate("UPDATE " + collection + ".MQ_PARM " +
                               "SET INTVALUE = INTVALUE + 1 WHERE ID =" +
                               '\'' + key + '\'');
      nextNumQry = "SELECT * FROM " + collection + ".MQ_PARM WHERE ID =" +
                   '\'' + key + '\'';
      nextNumSet = nextNumStm.executeQuery(nextNumQry);

      if (nextNumSet.next()) {
        nextNum = nextNumSet.getInt("INTVALUE");
      }
    }
    catch (SQLException e) {
      JS_Utils.processException(e);
      throw new JS_Exception();
    }

    return nextNum;
  }


**********************************************************************
This e-mail is intended solely for the addressee and is strictly
confidential. If you are not the addressee, please do not read, print,
re-transmit, store or act in reliance on it or any attachments. Instead
please e-mail it back to the sender and delete the message from your
computer.

E-mail transmission cannot be guaranteed to be secure or error free and The
Co-operative Bank accepts no liability for changes made to this e-mail (and
any attachments) after it was sent or for viruses arising as a result of
this e-mail transmission.

Any unauthorised reproduction, dissemination, copying, disclosure,
modification, distribution and/or publication of this e-mail message is
strictly prohibited.

The Co-operative Bank reserves the right to intercept any e - mails or
other communication for permitted purposes in accordance with the current
legislation which you send to, or receive from, any of the employees or
agents of the Bank via Bank telecommunication systems. By so corresponding
you also give your consent to the Bank monitoring and recording of any
correspondence using these systems.

The Co-operative Bank p.l.c. is registered in England and Wales, number
990937. The registered office is at PO Box 101, 1, Balloon Street,
Manchester, M60 4EP.
**********************************************************************
_______________________________________________
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.








********************************************************************************
The information in this message is confidential and may be legally
privileged. It is intended solely for the addressee; access to this
email by anyone else is unauthorised.

If you are not the intended recipient: (1) you are kindly requested
to return a copy of this message to the sender indicating that you
have received it in error, and to destroy the received copy; and (2)
any disclosure or distribution of this message, as well as any action
taken or omitted to be taken in reliance on its content, is prohibited
and may be unlawful.
********************************************************************************