![]() |
![]() |
|
![]() |
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. ![]() |
![]() |
|