INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Databases and tables

Generate integer IDs (unique, usable for primary key) by Olaf Doschke
Posted: 1 Dec 14

How to generate integer IDs itself is easy: Use an Integer (Autoinc) field type and it'll automatically get an incrementing value. The default of this type of field is like with SQL Server Identity or MS Access Autonumber or sequences in other databases: Start value 1, increment 1, so you get values 1,2,3....

There are some rules for such fields: They are read only, they are generated when inserting into the DBF file and then only readable.

And that has some consequences in working with autoinc fields: You can't append into them, eg from CSV or other DBFs. You can suppress error messages, when setting SET AUTOINCERROR OFF, but that'll just suppress the error, it's not like SET IDENTTIY OFF enabling to write into the autoinc field.

Another common problem with autoinc fields is with buffered updatable views. Records you insert buffered get an autoinc value and the autoinc counter in the DBF header file is incremented right then and available to any concurrent clients also adding buffered data to the DBF, but it has it's problems with generating IDs and using them as foreign keys in buffered modes and then reverting.

For this and other reasons it's still preferable to have a writable ID/primary key field in your tables. You can do this and still make use of the reliability of autoinc, by using a stored procedure making use of a single autoinc field in a single database system table just being used as source of new ids globally for all tables. That means you have the additional benefit of any ID integer value being unique in the database, not only in each single table.

CODE --> VFP

#Define ccDesiredDirectoryOfDBC "C:\AppData\Yourcompany\GAI\" && whatever desired target folder for your database, it must exist, so create it, if it doesn't
Local lcStoredProcs, lcTempfile

* To apply to an existing database open it here, instead of creating a new one:
Cd (ccDesiredDirectoryOfDBC)
Create Database 'GlobalAutoinc.DBC'

Set Database To GlobalAutoinc
Create Table 'S_Auto.DBF' (iInc I Not Null Autoinc Nextvalue 1 Step 1) && the only autoinc field ever needed

* Sample tables for usage of the S_Auto autoinc field via stored proc
Create Table 'SampleTable1.DBF' (Id I Not Null Default gai(), cText C(10))
Alter Table 'SampleTable1' Add Primary Key Id Tag Id Collate 'MACHINE'

Create Table 'SampleTable2.DBF' (Id I Not Null Default gai(), cText C(10))
Alter Table 'SampleTable2' Add Primary Key Id Tag Id Collate 'MACHINE'

Close Tables All

* definition of the stored proc "gai()"
TEXT To lcStoredProcs NoShow
Function gai() && gai = global auto inc
   Local lnAutoinc

   * Adapt this to your database name: GlobalAutoinc!S_Auto=> YourDbc!S_Auto
   Use GlobalAutoinc!S_Auto In Select("S_Auto") Again Alias S_Auto
   Begin Transaction
   Append Blank In S_Auto
   lnAutoinc = S_Auto.iInc
   RollBack && never keep records in the S_Auto.dbf, just use the autoinc counter for the iInc field in the S_Auto.dbf header.
   Use In Select("S_Auto")

   Return m.lnAutoinc
EndFunc
ENDTEXT

lcTempfile = Addbs(Getenv("TEMP"))+Sys(2015)
Strtofile(lcStoredProcs,lcTempfile,.F.)
Append Procedures From (lcTempfile)
Erase (lcTempfile)

Compile Database GlobalAutoinc

Insert Into SampleTable1 (cText) Values ("hello") && will get ID=1
Insert Into SampleTable2 (cText) Values ("world!") && will get ID=2, not 1 as you would with a separate autoinc integer for each table.

*From now on, wherever you insert/append new data it'll get the next autoinc value. 

As the sample tables demonstrate, the ID counter is used by both tables and wherever you add the next record in one of them, the ID will be 3.

Back to Microsoft: Visual FoxPro FAQ Index
Back to Microsoft: Visual FoxPro Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close