Auto Incrementing Primary Key 
Author Message
 Auto Incrementing Primary Key

I am just starting to play around with VFP 6.0, and I am trying to
figure out how to incorporate an auto incrementing primary key into
several of my tables.  I would really appreciate any insight on how this
can best be accomplished.

Thanks

Chris



Sat, 30 Jun 2001 03:00:00 GMT  
 Auto Incrementing Primary Key
Take a look at the Tastrade demo database that comes with VFP. Check out
SETUP.DBF, PRODUCTS.DBF and its primary key column DEFAULT function Newid().
The code for Newid is a stored procedure in the database.
-Anders
Quote:

>I am just starting to play around with VFP 6.0, and I am trying to
>figure out how to incorporate an auto incrementing primary key into
>several of my tables.  I would really appreciate any insight on how this
>can best be accomplished.

>Thanks

>Chris



Sun, 01 Jul 2001 03:00:00 GMT  
 Auto Incrementing Primary Key

Quote:

> I am just starting to play around with VFP 6.0, and I am trying to
> figure out how to incorporate an auto incrementing primary key into
> several of my tables.  I would really appreciate any insight on how this
> can best be accomplished.

> Thanks

> Chris

Add the following as a stored procedure to your database.
Then, in any field in tables in the database where you
want an incrementing id, just set its default value to
NEXTVAL(cSeq,nInitial,nIncr) where "mySeq" is a unique name for
the sequence, nInitial is the initial desired value for
the ID (typically 1), and nIncr is how much to increment
subsequent values by (also typically 1).  Also add a
table to your database called "sequences" with the
structure:

seq_name   C(20)
next_val   N
increment  N

This method is modeled after how Oracle implements
auto-incrementing values.  I prefer this approach to
the one in Tastrade because mine allows you to increment
by values other than one and to start at whatever value
you want to.  So, if you wanted to, you could begin your
values at 50000 and increment them by 100.  Or start
at 1.00 and increment by 0.25.  I even use a negative
increment for certain purposes!

******************************************************************************
*
*       Stored Proc: Nextval
*
*       Operation:      Return the next available ID for the passed in sequence.
*
*       Arguments:      tcSequence - name of the sequence to get the next ID from
*                               tnInitial - initial value for the sequence when creating
*                               tnIncrement - increment value for the sequence when creating
*
*       Author:         Tim Witort, (c) Gerst Software Inc.
*
*       Change History:
*       Changer         Date                            Description
*       ----------      -------------   ---------------------------------------------
*       trw                     20-FEB-1998             Initial Release
*       trw                     18-SEP-1998             Added optional initial and increment args
*
******************************************************************************
FUNCTION Nextval(tcSequence, tnInitial, tnIncrement)
*------------------------------ Local Variables
-----------------------------*
  LOCAL lcSequence, ;
        lnID, ;
        lcOldReprocess, ;
        lnOldArea, ;
        lnInitial, ;
        lnIncrement
*------------------------------ Procedure Code
------------------------------*

  *
  *  Remember current work area:
  *
  lnOldArea = SELECT()

  *
  *  If the sequence for which the ID is desired was not
  *  specified error out:
  *
  IF PARAMETERS() < 1
    Messagebox("ERROR: No sequence identified for NEXTVAL.",48,"Bad
NEXTVAL Usage")
    RETURN -1
  ELSE
    lcSequence = UPPER(tcSequence)
  ENDIF

  *
  *  Initialize the ID to nothing:
  *
  lnID = ""

  *
  *  Save old reprocess setting:
  *
  lcOldReprocess = SET('REPROCESS')

  *
  *  We will be locking the sequence table until the user
  *  presses escape or we get in:
  *
  SET REPROCESS TO AUTOMATIC

  *
  *  If the sequences table isn't open yet, open it:
  *
  IF !USED("SEQUENCES")
    USE sequences IN 0
  ENDIF
  SELECT sequences

  *
  *  Locate an existing sequence with the name passed in:
  *
  IF SEEK(lcSequence, "sequences", "primary")

    *
    *  Found the sequence, lock the table while we get the
    *  value and update it:
    *
    IF RLOCK()

      *
      *  Get the next value:
      *
      lcID = sequences.next_val

      *
      *  Update the next value:
      *
      REPLACE sequences.next_val WITH sequences.next_val +
sequences.increment

      *
      *  Our work here is done, release the lock on the table:
      *
      UNLOCK
    ENDIF

  *
  *  Sequence doesn't exist, create one:
  *
  ELSE
        *
        *  If the user passed in the initial value, use it, otherwise
        *  default it to 1:
        *
        IF TYPE("tnInitial") = "N" AND !EMPTY(tnInitial)
                lnInitial = tnInitial
        ELSE
                lnInitial = 1
        ENDIF

        *
        *  If the user passed in the increment value, use it, otherwise
        *  default it to 1:
        *
        IF TYPE("tnIncrement") = "N" AND !EMPTY(tnIncrement)
                lnIncrement = tnIncrement
        ELSE
                lnIncrement = 1
        ENDIF

        lcID = lnInitial

        INSERT INTO sequences (seq_name, increment, next_val) ;
                VALUES (ALLTRIM(lcSequence), lnIncrement, lnInitial+lnIncrement)
  ENDIF

  *
  *  Restore the work area and reprocess settings:
  *
  SELECT (lnOldArea)
  SET REPROCESS TO lcOldReprocess

  *
  *  Return the nextval for the sequence:
  *
  RETURN lcID

ENDFUNC



Sun, 01 Jul 2001 03:00:00 GMT  
 Auto Incrementing Primary Key
Not sure, but:

ON KEY LABLE 'DOWN ARROW' DO my skip

***  my skip

SKIP
SHOW GETS

*** end my skip

Maybe have to do another BROW and not SHOW GETS

Quote:

>I am just starting to play around with VFP 6.0, and I am trying to
>figure out how to incorporate an auto incrementing primary key into
>several of my tables.  I would really appreciate any insight on how this
>can best be accomplished.

>Thanks

>Chris



Wed, 04 Jul 2001 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Auto-incrementing a primary key field

2. auto-incrementing a primary key

3. auto-incrementing a primary key

4. auto-generating primary keys

5. Auto Increment Field

6. Auto Increment

7. Auto Increment Field

8. Auto-Incrementing

9. Auto Increment Field

10. Auto-Incremented field

11. Auto-increment Field in Foxpro

12. How to make an auto-incrementing index

 

 
Powered by phpBB® Forum Software