Paradox Community
Search:

 Welcome |  What is Paradox |  Paradox Folk |  Paradox Solutions |
 Interactive Paradox |  Paradox Programming |  Internet/Intranet Development |
 Support Options |  Classified Ads |  Wish List |  Submissions 


Paradox Newsgroups  |  Paradox Web Sites  |  Paradox Book List  |  FAQs From The Corel FAQ Newsgroup  


Paradox Community note: the attachment for this FAQ was updated 
and that update was posted here on 24 Oct 2003.  The text of this 
FAQ was not updated to reflect changes in the library.

Subject: TIP:PdoxWin:Incrementing Primary Keys:2001.05.26

Version 1.0 (2001.05.26)
edited by Paradox FAQ Team

====================
0. Introduction
====================

This FAQ addresses the matter of creating a new primary 
key value in such a way as to ensure that each generated 
value is unique throughout the application.

-------------------------------
 0.1 Legal Info and Disclaimers
-------------------------------

Paradox is a trademark of Corel.
Borland Database Engine (BDE) is a trademark of Inprise.

The information provided in this FAQ is provided "as is"
and is not warranted in any way. The information provided
in this FAQ is not endorsed or authorized by Corel or
Inprise in any shape, form, or manner.

The editors claim NO responsibility for ANY illegal
activity regarding this file, or as a result of someone
reading this file.

You may distribute this file, as long as the copies are
complete, unaltered, and are in electronic form only.

-------------
 0.2 Feedback
-------------

Please send feedback in a Corel Paradox newsgroup or the
news:comp.databases.Paradox newsgroup to any of the FAQ
Team mentioned in the "FAQ: FAQ FAQ" document.
Please preface the subject of your post with the string
"PDXWIN FAQ" to alert Team members to the function of
the message.

Please specify the FAQ name and section number the
comment applies to, if any.

-------------
 0.3 Attachments
-------------

These is a self-extracting Zip file, CoordLib.Exe, attached
to this posting. It contains a library and a table. Decide 
where to place these and adjust the contents of the Const 
section of the library accordingly.

==============================
1. General Information
==============================

In Paradox, as in Access, MS SQL Server, and other
systems, there is an "Auto-Increment" type. This type can
be used as the first field of a table, thus providing a
unique value for each row. This satisfies the requirements
for being a Primary Key.

========
2. Problem Definition
========

The problem with using an autoIncrementing value lies in
the inability of the programmer to manipulate it. For
example, if a user accidentally deletes a record somehow,
it is not possible to recreate the record.

Another area that neither Paradox nor most other database
systems cover is providing the programmer with the ability
to create his or her own "nextValue()" method. Paradox,
for example, only allows monotonically increasing longInt
values. MS SQL Server does allow other value sequences, and
it does allow subsequent fill-in, but that still leaves
serious gaps. For example, your value may not be destined to
be a Primary Key. It might be, for instance, an Invoice
number, where the number might have to be of the form
"991111" (the "99" is the year number and the "1111" the
sequential number from the start of the year.

All sorts of weird and wonderful sequences can be dreamed up
by those for whom you have to create systems, so what you
really need is a simple system that can be added to at will.

==========
3. The Solution
==========

We offer here a sample version of a system that you can
extend as you will. It is composed of a library for the
interface code and a table to keep the values.

You can take this code and modify it as you will, save that
you do not represent it as your own (aside from those parts
that actually are your additions and modifications).

-------------
3.1. The Tables
-------------
The table is called CoordLib, to match the Library, and has
the following structure:

ItemName           A16*
ItemValue          A24
LastUpdate         @
LastRead           @
AlterationType     A16

The LastUpdate and LastRead values are just for programming
information. The idea is that a value is named by the entry
in ItemName and held as a string in ItemValue. The size of
ItemValue is dictated by the number of significant digits in
a Number type - 18.
AlterationType defines the name of the type of
tramnsformation that will create the next value in the
sequence.

-------------
3.2. The Library
-------------

The library holds a very small number of methods:

const()
open()
emIncrementValue()
emGetValue
emSetValue()
lockPhantomTable()
unlockPhantomTable()

-------------
3.2.1. const()
-------------

Const
   coCoordFileSpec = ":SystData:CoordLib"
   coScriptDirectory = ":SystData:"
endConst

-------------
3.2.2. open()
-------------

method open(var eventInfo Event)
var
   taCoordLib        Table
endVar
   if taCoordLib.attach(coCoordFileSpec) and
      taCoordLib.IsTable() then
   else
      msgStop("Error",
               "Coordination Library cannot find table")
   endif
endmethod

-------------
3.2.2. emIncrementValue(stItemName String) String
-------------

method emIncrementValue(stItemName String) String
;----------------------------------
;	Increments and returns the value in coordInf.db
;	corresponding to the name provided.
;	The value is returned as a string, length and
;	leading zeroes preserved.
;	For error checking, seek a "!" in the first position
;	of the returned string.
;----------------------------------
var
   tcCoordLib       tCursor
   stOldVal         String
   stNewVal         String
   stScriptName     String
   stMethod         String
endvar
;=====================================Can I lock the record ?
   if not lockPhantomTable(stItemName) then
      return("!Error 1")
   endif
;=====================================Can I open the table ?
	if not tcCoordLib.open(coCoordFileSpec) then
      return("!Error 2")
   endif
;=====================================Can I edit the table ?
	if not tcCoordLib.edit() then
      return("!Error 3")
   endif
;=====================================Can I find the record ?
	if not tcCoordLib.qLocate(stFieldName) then
      return("!Error 4")
   endif
;=====================================
   stOldVal = tcCoordLib."ItemValue"
   stScriptName = tcCoordLib."AlterationScript"
   stMethod = tcCoordLib."AlterationType"
   switch
      case stMethod = "YYPrefix" :
         stNewVal = cpYYIncrement(stOldVal)
      case stMethod = "Int" :
         stNewVal = cpIntIncrement(stOldVal)
      case stMethod.subStr(stMethod.size()-2, 3) = "Int" :
         stNewVal = cpPrefixIntIncrement(stOldVal,
                           stMethod.size()-3)
   endSwitch
   tcCoordLib."ItemValue" = stNewVal
   tcCoordLib."LastUpdate" = dateTime()
   tcCoordLib.endEdit()
   tcCoordLib.close()
   unlockPhantomTable(stItemName)
;=====================================
   return stNewVal
endmethod

-------------
3.2.3. emSetValue(stItemName String, stItemValue String)String
-------------

method emSetValue(stItemName String, stItemValue String)
var
   tcCoordLib        tCursor
endvar
;=====================================Can I lock the record ?
   if not lockPhantomTable(stItemName) then
      return("!Error 1")
   endif
;=====================================Can I open the table ?
	if not tcCoordLib.open(coCoordFileSpec) then
      return("!Error 2")
   endif
;=====================================Can I edit the table ?
	if not tcCoordLib.edit() then
      return("!Error 3")
   endif
;=====================================Can I find the record ?
	if not tcCoordLib.qLocate(stItemName) then
      return("!Error 4")
   endif
;=====================================
   tcCoordLib."ItemValue" = stItemValue
   tcCoordLib."LastUpdate" = dateTime()
   tcCoordLib.endEdit()
   tcCoordLib.close()
   unlockPhantomTable(stItemName)
;=====================================
   return "True"
endmethod


-------------
3.2.4. emGetValue(stItemName String) String
-------------

method emGetValue(stItemName String) String
var
   tcCoordLib       tCursor
   stNewVal         String
endvar
;=====================================Can I lock the record ?
   if not lockPhantomTable(stItemName) then
      return("!Error 1")
   endif
;=====================================Can I open the table ?
	if not tcCoordLib.open(coCoordFileSpec) then
      return("!Error 2")
   endif
;=====================================Can I edit the table ?
	if not tcCoordLib.edit() then
      return("!Error 3")
   endif
;=====================================Can I find the record ?
	if not tcCoordLib.qLocate(stItemName) then
      return("!Error 4")
   endif
;=====================================
   stOldVal = tcCoordLib."ItemValue"
   tcCoordLib."LastRead" = dateTime()
   tcCoordLib.endEdit()
   tcCoordLib.close()
   unlockPhantomTable(stItemName)
;=====================================
   return stOldVal
endmethod

-------------
3.2.5. lockPhantomTable(stItemName String)Logical
-------------

method lockPhantomTable(stFileName String) Logical
;  Paradox does not actually lock tables; instead, it locks 
;	the name of the table. This means that you can create 
;	as many "mutex" (mutual exclusion) locks as you like.
;  I use that here to lock a virtual table with the name
;  of the item that is being incremented. In this way, 
;	locking and unlocking is extremely fast.

var
   tblVar       Table
   pdoxTbl      String
   lRet		Logical
endvar
   pdoxTbl = coScriptDirectory + stFileName + ".PTL"
   lRet = tblVar.attach(pdoxTbl)
   lRet = tblVar.lock("Full")
   return lRet
endmethod

-------------
3.2.6. unlockPhantomTable(stItemName String)
-------------

method unLockPhantomTable(stFileName String)
var
   tblVar				Table
   pdoxTbl				String
endvar
   pdoxTbl = coScriptDirectory + stFileName + ".PTL"
   tblVar.attach(pdoxTbl)
   tblVar.unLock("Full")
   return
endmethod

==========
4. Transformation Examples
==========

The following are some code examples for you to include in
your transformation routines.

-------------
4.1. Simple Increment of an Integer
-------------
cpIntIncrement(stOldVal String) String
;           A simple variation of this provides a
;           decrementing counter, of course !
var
   liTemp		longInt
   stNewVal		String
   stLeadingZeroes	String
endvar
;=====================================
   stLeadingZeroes = "0000000000000000"
;=====================================
   liTemp = longInt(stOldVal)
   liTemp = liTemp + 1
   stNewVal = String(liTemp)
   if stNewVal.size() < stOldVal.size() then
	stNewVal = stLeadingZeroes.subStr(1, stOldVal.size() -
				stNewVal.size()) + stNewVal
   endif
   return stNewVal
endMethod

-------------
4.2. Prefixed Increment of an Integer
-------------

method cpPrefixIntIncrement(stOldVal String, 
                           siPrefixLength smallInt)String
;----------------------------
;	This method handles incrementing an integer whose
;	first few places are a fixed prefix.
;	For example, a key might be defined as an "R" and an int.
;	The Prefix length siPrefixLength is then 1
;----------------------------
var
   liTemp			longInt
   stNewVal			String
   stLeadingZeroes		String
   stPrefix			String
endvar
;=====================================
   stLeadingZeroes = "0000000000000000"
;=====================================
   stPrefix = stOldVal.subStr(1, siPrefixLength)
   stOldVal = stOldVal.substr(siPrefixLength+1,
			stOldVal.size()-siPrefixLength)
   liTemp = longInt(stOldVal)
   liTemp = liTemp + 1
   stNewVal = String(liTemp)
   if stNewVal.size() < stOldVal.size() then
      stNewVal = stLeadingZeroes.subStr(1,
		stOldVal.size() - stNewVal.size()) + stNewVal
   endif
   return stPrefix + stNewVal
endMethod

-------------
4.2. Increment an Integer that has a 2-digit Year Prefix
-------------

method cpYYIncrement(stOldVal String) String
;------------------------------
;	This is a prefixed increment system, where
;	the prefix is a 2-digit number representing
;	the current year value. This is often used for
;	generating billing sequences
;------------------------------
var
   daTemp                       Date
   stLeadingZeroes		String
   stOldYear			String
   stYear			String
   siOldVal			smallInt
   stNewVal			String
   stResult			String
endvar
;=====================================
   stLeadingZeroes = "0000000000000000"
;=====================================
   daTemp = date()
   stYear = String(daTemp.Year())	;get today's year
   stOldYear = stOldVal.substr(1,2)	;get prefix year,
   stOldVal = stOldVal.substr(3, (stOldVal.size()-2));counter
   switch				;
      case stYear = stOldYear :		;still in same year
         siOldVal = smallInt(stOldVal)	;
                     stNewVal = String(siOldVal + 1)	
      case stYear > stOldYear :		;now in new year !
         stNewVal = "0000"		;
      case stYear < stOldYear :		;
         if stOldYear = "99" and	;Y2K or new century
            stYear = "00"		;
         then				;
            stNewVal = "0000"		;
         else				;
            msgInfo(	"CoordLib Error",
			"IncrementValue invalid year value. "+
			"Please check your PC clock ! ")
            return  "!5 "
         endif					;
      otherwise :				;
   endswitch					;
   if stNewVal.size() < stOldVal.size() then
      stNewVal = stLeadingZeroes.subStr(1, stOldVal.size() -
				stNewVal.size()) + stNewVal
   endif                                  ;concatenate year
   stResult = stYear + stNewVal       ;and counter again
endMethod

==========
5. Use
==========

---------------------
5.1. Simple Increment
---------------------

Assume you have a table Customers, with a longInt Primary
Key. This code would be used when adding a new record:

tcCustomers.InsertRecord()
tcCustomers."CustID" =
                longInt(CoordLib.emIncrementValue("CustID"))

emIncrementValue will return the new value as a string,
padded with leading zeroes. Converting that into a longInt
will remove the zeroes and also make it acceptable to the
table.

The Transformation code that you would use here would most
likely be one to cause a simple integer addition.

---------------------
5.1. Invoices
---------------------

Invoices are frequently tagged by a composite number. Here,
generation of an new invoice might well entail two visitss
to CoordLib; one for the primary key of the table, and one
for the invoice number that gets printed.

tcInvoices.InsertRecord()
tcInvoices."InvoiceID" =
         longInt(CoordLib.emIncrementValue("InvoiceID"))
tcInvoices."InvoiceNumber" =
         longInt(CoordLib.emIncrementValue("InvoiceNumber"))

The Transformation codes that you would use here would most
likely be one to cause a simple integer addition for the 
InvoiceID and one such as the "YYInt" one shown in the
examples above for the Invoice Number.
Note that in each case I have used the same value for the
item name as the field name; this is not necessary.
coordlib.zip


Paradox Community Newsgroups


 Feedback |  Paradox Day |  Who Uses Paradox |  I Use Paradox |  Downloads 


 The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation.
 Paradox is a registered trademark of Corel Corporation.


 Modified: 24 Oct 2003
 Terms of Use / Legal Disclaimer


 Copyright © 2001- 2003 Paradox Community. All rights reserved. 
 Company and product names are trademarks or registered trademarks of their respective companies. 
 Authors hold the copyrights to their own works. Please contact the author of any article for details.