IT SOLUTIONS
Your full service technology partner! 
-Collapse +Expand
Paradox
Search Paradox Group:

Advanced
-Collapse +Expand Paradox To/From
To/FromCODEGuides
-Collapse +Expand Paradox Store
PRESTWOODSTORE

Prestwood eMagazine

May Edition
Subscribe now! It's Free!
Enter your email:

   ► KBDesktop Data...Paradox & Ob...P9 Book: Pow...   Print This     
  From the September 2011 Issue of Prestwood eMag
 
Paradox P9 Book: Power Programming:
Power: Chapter 14, Using Table and TCursor Variables
 
Posted 16 years ago on 3/22/2003
Take Away: Chapter 14, "Using Table and TCursor Variables" from Paradox 9 Power Programming by Mike Prestwood.

KB100207



A table is an object that consists of rows of records and columns of fields. In ObjectPAL, a Table variable is a handle to a table on disk or to a table located on a SQL server. It’s different from a TCursor variable. A TCursor variable looks at the data in a table, whereas a Table variable looks at the whole table. Table variables enable you to manipulate tables as a whole. For example, you can add records from one table to another, copy a table, and get the average value for a field. Table object type methods and procedures deal with the table as a whole.

A TCursor (table cursor) is a pointer to a record (or row) in a table. A TCursor is a tool used to manipulate a table directly without going through a UIObject. After you declare a TCursor variable, you can use it to open a handle to the first row of a specific table. Once open, you then have a handle to the data in the table.

The Table Object

An open() method is conspicuously absent from the table methods. The attach() method associates a Table variable with a table’s filename. The extension .DB and .DBF specify the type of table. To use a Table variable, you need to declare it, as in the following:

1: var
2: tbl Table
3: endVar

After you have a Table variable with which to work, you open it by attaching directly to a table on disk, as in the following:

1: tbl.attach("CUSTOMER.DB")

For example, to display the total number of customers in the Customer table, alter the pushButton event of a button as follows:

method pushButton(var eventInfo Event)
const
CUSTOMER = ":work:Customer.db"
endConst
var
tblCustomer Table
endVar

tblCustomer.attach(CUSTOMER)
msgInfo("Number of Customers", tblCustomer.cCount("Customer No"))
endMethod

Using Table Methods and Procedures

Many of the table methods deal with data in the table and are duplicated in the TCursor class. After you declare a Table variable and open it with attach(), you can use the Table methods on it. The following statements, for example, are valid:

1: tbl.attach("DEST.DB")
2: tbl.cMax("Total")
3: tbl.delete()

The next example puts the cAverage() method to work with a Table variable.

Using cAverage() with a Table Variable

Suppose that you want to find the average sale for all invoices in the Customer table. You can do this by using cAverage() and a Table variable.

Step By Step

  1. Change your working directory to Paradox’s Samples directory. Open a form or create a new one. Place a button labeled Average Order on it.
  2. Alter the pushButton event of the button as follows:
  3. 1: ;Button :: pushButton
    2: method pushButton(var eventInfo Event)
    3: const
    4: ORDERS = ":work:Orders.db"
    5: endConst
    6: var
    7: tblOrders Table
    8: endVar
    9:
    10: tblOrders.attach(ORDERS)
    11: msgInfo("Average Order", tblOrders.cAverage("Total Invoice"))
    12: endMethod

  4. Check the syntax, save the form as TableExamples.fsl, and change the mode to View Data. Click the button. After a short time, the answer appears onscreen:

Illustration 1

Compacting dBASE and Paradox tables

Deleted records are not immediately removed from a dBASE table. Instead, they are flagged as deleted and kept in the table. In addition, if you delete records from a Paradox table, they cannot be retrieved. However, the table file and associated index files contain dead space where the record was originally stored. To compact a Paradox table, alter the pushButton event of a button as follows:

 1. method pushButton(var eventInfo Event)
 2. const
 3. ORDERS = ":work:Orders.db"
 4. endConst
 5. var
 6. tblOrders Table
 7. regIndex Logical
 8. endVar
 
 9. tblOrders.attach(ORDERS)

10. try
11. regIndex = True
12. if not tblOrders.compact(regIndex) then errorShow() endIf
13. onFail
14. ;There probably is a lock on the table.
15. errorShow()
16. endTry
17. endMethod

For dBASE tables, the optional argument regIndex specifies whether to regenerate or update the indexes associated with the table. When regIndex is set to True, this method regenerates all indexes associated with the table. If you use compact() with a Paradox table, all indexes are regenerated and dead space is removed. This method fails if any locks have been placed on the table or the table is open. This method returns True if successful; otherwise, it returns False.

Creating Tables

You can use the Create keyword to create a table. The following example demonstrates creating a Paradox table:

1. method pushButton(var eventInfo Event)
2. const
3. CUSTOMERNOTES = ":work:CustomerNotes.db"
4. endConst
5. var
6. tblCustomerNotes Table
7. tvCustomerNotes TableView
8. endVar

9. if isTable(CUSTOMERNOTES) then
10. if msgQuestion("Confirm", "Table exists. Overwrite it?") <> "Yes" then
11. return
12. endIf
13. endIf

14. errorTrapOnWarnings(True)

15. tblCustomerNotes = create CUSTOMERNOTES
16. with "Customer No" : "N",
17. "Notes" : "F",
18. "ModifyTimeStamp" : "@"
19. key "Customer No"
20. endCreate

21. tvCustomerNotes.open(CUSTOMERNOTES)
22. endMethod

The TCursor Object

When you use a TCursor, it works in the background similar to another user. You can manipulate a TCursor variable just like any other variable using any of the TCursor object type methods. In fact, a TCursor has many of the same methods as a UIObject. The Table object type doesn’t have nearly as many methods as a TCursor does. Its functionality is more limited because it operates only for the table as a whole, whereas a TCursor works directly on the data.

Think of a TCursor as a channel you open to a table. Typically, you open a TCursor with the following:

1: tc.open("TABLE.DB")

tc is a TCursor class variable. All further references to the table can be represented by the TCursor as in the following:

1: tc.FieldName = Today()

In this example, quotation marks aren’t used around the field name in the table. Quotation marks aren’t needed for field names that have no special characters. If, however, a field name contains a special character, such as a space or a hyphen, quotation marks are required. For the sake of consistency, you might put quotation marks around all field names when you use a TCursor, as in the following example:

1: tc."FieldName" = Today()

A TCursor works in the background. Therefore, when you manipulate a database, movement through the table doesn’t appear onscreen. Because the screen isn’t refreshed, changes are made quickly.

Using a TCursor

Treat a TCursor variable like other variables. Declare it in the Var window of the object. If the method executes only once (like pushButton), or if you need a private version of the variable, declare it within the method.

In general, opening and closing a TCursor can be time-consuming because opening and closing a file on a disk is slower than leaving it open. Therefore, it’s best to minimize the number of times you open and close these objects. If the method you use occurs once, such as pushButton, it’s okay to declare it inside the method:

1: ;Button :: pushButton
2: method pushButton(var eventInfo Event)
3: var
4: tc TCursor
5: endVar
6:
7: tc.open("Customer.db")
8: msgInfo("Current Customer", tc."Name")
9: tc.close()
10: endMethod

Referring to Fields in a Table with a TCursor

ObjectPAL offers three ways to use a TCursor to refer to fields in a table: without quotes, with quotes, and dereferencing. For example:

1: tc.Last_Name ;Without quotes.
2: tc."Last_Name" ;With quotes (allows special characters).
3: tc.(2) ;Dereferencing with parentheses.

Line 1 above refers to the field with just the field name of the field as it appears in the table. If you have a field with spaces in it—for example, Last Name—then you cannot use this first technique. Line 2 above surrounds the field name with quotes and works with all field names. The preferred usage is to always use quotation marks, because it will always work. Line 3 above shows how to dereference a field by surrounding it with parentheses. Line 3 above is referring to the second field in the table. You could also dereference a field by using a variable. For example:

1: var
2: sField String ;Declare a variable.
3: tcCustomer TCursor
4: endVar
5: tcCustomer.open("Customer.db")
6: sField = "Name" ;Assign a field name to the variable.
7: view(tcCustomer.(sField)) ;Dereference the variable using parentheses.

Dereferencing with parenthesis is a general ObjectPAL concept and is used in other places in ObjectPAL. For example, dereferencing is used with UIObjects. You can store the name of a UIObject in a variable and use it as part of your dot notation listing the object path. For example, if you name a page of a form pge1 and place a box on it named box1, you can do the following:

1: var
2: sObject String ;Declare a variable.
3: endVar
4:
5: sObject = "box1" ;Assign an object name to the variable.
6: pge1.(sObject).color = Red ;Dereference the variable using parenthesis.

Example of Using a TCursor to Refer to a field

Suppose that you want to find a row based on one field and then display a different field from that record. This next example uses the Biolife table to demonstrate moveToRecord() and locate().

Step By Step

  1. Set your working directory to Paradox’s Samples directory. Create a new form, place a drop-down field and a button labeled Display Species on it:
  2. Illustration 2

  3. Name the drop-down field fldCommonName.
  4. Alter the open event of the list object of the drop-down field as follows (use the Object Tree to get to the list object of the drop-down field).
  5. method open(var eventInfo Event)
    DoDefault
    Self.datasource = "[:work:biolife.\"Common Name\"]"
    endMethod

  6. Alter the pushButton event of the button as follows:
  7. ;Button :: pushButton
    method pushButton(var eventInfo Event)
    const
    BIOLIFE = ":work:biolife.db"
    endConst
    var
    tcBiolife TCursor
    endVar

    if fldCommonName.isBlank() then
    msgStop("Error", "First select a common name.")
    return
    endIf

    if not tcBiolife.open(BIOLIFE) then
    errorShow()
    return
    endIf

    tcBiolife.locate("Common Name", fldCommonName.value)
    msgInfo("Species", fldCommonName.value + " = " + tcBiolife."Species Name")
    endMethod

  8. Check the syntax, save the form as TCursorExamples.fsl, and change the mode to View Data mode. Select a common name from the drop-down field and click the button:

Illustration 3

Inserting a Record with a TCursor

With a TCursor, you can manipulate and add data directly to a table with no interaction on the form, just as you can use a UIObject to put the table connected to it into Edit mode, insert a record, and post a value. Suppose that you want to insert a new record into the Customer table. To do this, open a TCursor to the Customer table and insert a new record. You can do the same tasks with a TCursor, as the following example demonstrates.

Step By Step

  1. Set your working directory to Paradox’s Samples directory. Open the TCursorExamples.FSL form you created in the last example and add a button labeled Add your Name.
  2. Alter the pushButton event as follows:
  3. 1: ;Button :: pushButton
    2: method pushButton(var eventInfo Event)
    3: const
    4: CUSTOMER = ":work:Customer.db"
    5: endConst
    6: var
    7: tcCustomer TCursor
    8: sName String
    9: tvCustomer TableView
    10: endVar
    11:
    12: tcCustomer.open(CUSTOMER)
    13: tcCustomer.edit()
    14: tcCustomer.insertRecord()
    15:
    16: ; Enter a new customer name.
    17: sName = ""
    18: sName.view("Enter your name")
    19: if sName = "" then
    20: beep()
    21: message("No name entered. Aborted")
    22: tcCustomer.cancelEdit()
    23: return
    24: endIf
    25: tcCustomer.Name = sName
    26:
    27: ; Generate a new customer number.
    28: try
    29: tcCustomer."Customer No" = tcCustomer.cMax("Customer No") + 1
    30: onFail
    31: sleep(1000)
    32: retry
    33: endTry
    34:
    35: ; Post new record.
    36: tcCustomer.postRecord()
    37: tcCustomer.endEdit()
    38: tcCustomer.close()
    39:
    40: ;View table.
    41: tvCustomer.open(CUSTOMER)
    42: tvCustomer.action(MoveEnd)
    43: tvCustomer.action(MoveScrollLeft)
    44: tvCustomer.action(MoveScrollLeft)
    45: tvCustomer.action(MoveBeginLine)
    46: endMethod

  4. Check the syntax, save the form, run the form, and click the button. Nothing seems to happen. Open the Customer table. Now the first record is 100, and it displays your name.

Using switchIndex() with a TCursor

When you want to change the active index on a TCursor, use the switchIndex() method. The switchIndex() is in both the UIObject and TCursor object types. The syntax for switchIndex() is the same, as in the following:

 
switchIndex( [const IndexName_String][, const_stayOnRecord Logical ] ) Logical
 

To switch a table frame to a secondary index named secCity, for example, use the following:

1: CUSTOMERS.switchIndex("secCity")

To switch back to the primary key, leave out the secondary index, as in the following example:

1: CUSTOMERS.switchIndex()

You can use switchIndex() on a TCursor just like on a UIObject. You can even synchronize a UIObject connected to the same table with resync(). The next example demonstrates the technique of switching an index on a TCursor using ObjectPAL, and then resyncing it to the UIObject.

Example of Using switchIndex()

Suppose that you want to be able to quickly sort the Customer table by Name, City, Zip/Postal Code, or Phone Number. This is very handy for quickly looking up a customer who has forgotten their Customer No. To do this, we will have to add a secondary index for each sort we want.

Step By Step
  1. Change your working directory to the Paradox’s Samples directory. Create a new form with the Customer table in the data model, as shown next. Choose Tabular in the Style panel in the Design Layout dialog box.
  2. Illustration 4

  3. Name the TableFrame tfCustomer.
  4. Restructure the Customer table and add a secondary index called secPhone (See Figure 14-1 for the settings).
  5. Figure 1: Add this secPhone index to the Customer table

  6. Restructure the Customer table again, this time adding a secondary index called secStreet (see Figure 14-2 for the settings).
  7. Figure 2: Add this secStreet index to the Customer table

  8. Alter the mouseClick event of the Customer No heading text object as follows:
  9. 1: method mouseClick(var eventInfo MouseEvent)
    2: tfCustomer.switchIndex()
    3: endMethod

  10. Alter the mouseClick event of the Street heading text object as follows:
  11. 1: method mouseClick(var eventInfo MouseEvent)
    2: tfCustomer.switchIndex("secStreet")
    3: endMethod

  12. Alter the mouseClick event of the Phone heading text object as follows:
  13. 1: method mouseClick(var eventInfo MouseEvent)
    2: tfCustomer.switchIndex("secPhone")
    3: endMethod

  14. Check your syntax, save the form as CustomerList.fsl, and switch it to View Data mode. Click the Street, Phone, and Customer No fields. This illustration shows the table sorted by street:

Illustration 5

Redeclaring TCursors

The TCursor class has both an open() and a close() method. It generally is considered good programming practice to close any TCursor you open. Any TCursor you leave open will use up resources. If you open a TCursor, should you close() it before reusing it? Although it is generally a good habit to get into, it is not always necessary. Look at the following code:

1: var
2: tc TCursor
3: endVar
4:
5: tc.open(t1)
6: tc.open(t2)

In this simple example, a TCursor is declared and used twice in a row, without ever closing the first TCursor. The question is, "Does the first instance of the tc variable close when you reopen it?" Yes.

Now take a look at the following example. The following code is in a Var window at the form level.

1: ; Var Window of form
1: var
2: tc TCursor
3: endVar

This code is in the pushButton event of a button on the form.

1: ; Button1 :: pushButton
4: tc.open(t1)

The question is, should you close the TCursor with tc.close() after using it? The answer is, it depends. You could leave the TCursor open just in case you’re going to use it again. This would save the time needed to reopen it. If, however, you are only going to use the TCursor once, then you should close it to save resources.

attach() Versus open()

Many people confuse attaching and opening. You can attach a TCursor to a table window, to a UIObject, or to a TCursor variable that is already attached. This establishes an association between a new TCursor variable and an already open channel. This new TCursor variable inherits all the characteristics that apply from the attached variable. This includes Edit mode, record number, and range.

When you open a TCursor, its view is the entire table. When you attach a TCursor, its view is restricted. In a multitable form, the first table in the data model is the master and controlling table. All other linked tables are detail tables. The second table shows only those records that match the current master record, and are said to have a restricted view. When you attach a TCursor to a detail table, the TCursor inherits the restricted view of the detail table.

A TCursor Can Respect a Restricted View

A table is in restricted view when it is filtered down to a subset of records. When you establish a 1:M relationship between the order numbers in the ORDERS.DB table and the records in the LINEITEM.DB table, the subset of records in the LINEITEM.DB table is restricted or filtered.

In addition to opening a TCursor in the background, you can attach a TCursor to a UIObject, which forces the TCursor to respect the restricted view of the object. For example, in a 1:M relationship, or in an active setRange(), you can attach a TCursor variable to any UIObject and the TCursor will be restricted, just as the original UIObject is, on the same record that the UIObject is and in the same Edit mode.

The next example shows you how to open a TCursor by attaching it to an object already connected to the table.

Implementing Cascade Delete Using ObjectPAL

A cascade delete is a setting you can set with many database products. Cascade delete deletes all the child records of a parent record. Because Paradox doesn’t support cascade deletes, you must delete the child records. In a 1:1 relationship, this isn’t a big deal. Simply delete both records in each table, as in the following:

1: ObjectConnectedToTableTwo.deleteRecord()
2: ObjectConnectedToTableOne.deleteRecord()

This technique works quite well. You just have to remember to do it.

In a 1:M relationship, deleting child records is trickier. You have to loop through the children and delete them one at a time. You shouldn’t use a scan loop to delete records from a table. Instead, use either a while loop with eot() (end of table) or for loop with nRecords(). The following is an example using a for loop:

1: var
2: Counter Number
3: tc TCursor
4: endVar
5:
6: tc.attach(ChildUIObjectName)
7: tc.edit()
8:
9: for Counter from 1 to tc.nRecords()
10: tc.deleteRecord()
11: endFor

In this code, you attach the TCursor to the UIObject, which ensures that the TCursor will have the same restricted view that the object has. Therefore, tc.nRecords() returns the number of records in the restricted view—not the whole table.

Another technique is to use a while loop with eot(). The following code, for example, works great in versions 1.0 and 4.5:

1: method pushButton(var eventInfo Event)
2: var
3: tc TCursor
4: endVar
5:
6: errorTrapOnWarnings(Yes)
7:
8: tc.attach(LINEITEM) ;Attach to detail table.
9: tc.edit()
10:
11: ;Delete all children records.
12: while not tc.eot()
13: tc.deleteRecord()
14: endWhile
15:
16: edit() ;Make sure form is in edit mode.
17: Order_No.deleteRecord() ;Then delete the parent record.
18: endMethod

The preceding technique is not complete with version 5.0 and above of Paradox because of the interactive filter settings introduced with version 5.0. The following represents the preferred way to implement cascade delete in Paradox 9:

1: ;btnCascadeDelete :: pushButton
2: method pushButton(var eventInfo Event)
3: var
4: tc TCursor
5: endVar
6:
7: tc.attach(LINEITEM) ;Attach to detail table.
8: tc.dropGenFilter() ;Drop any user set filters.
9: tc.home() ;Put TCursor on first record.
10: tc.edit()
11:
12: while not tc.eot() ;If there are any child
13: tc.deleteRecord() ;records, delete all of them.
14: endWhile
15:
16: edit() ;Make sure form is in edit mode.
17: Order_No.deleteRecord() ;Delete the parent record.
18: endMethod

Why show you three different ways to accomplish the same task? For several reasons, first, to get you acquainted with the various ObjectPAL commands; and second, to show you that in ObjectPAL, there often are many ways to accomplish a single task. Which one is best? The best technique usually is the fastest or the one that uses the smallest amount of code. In this case, I believe all three are about equal.

Using setRange()

setRange() specifies a range of values (contrasted with setGenFilter(), which provides true filters—discussed next). setRange() is always preferred over setGenFilter(), because setRange() uses the active index. This makes setRange() faster than setGenFilter().

Suppose that you want to allow the user to specify a range of records they want to see—similar to a live query. The technique presented in this example uses setRange() on a TCursor with the resync() method.

Step By Step

  1. Change your working directory to Paradox’s Samples directory and create a new form with the Customer table in the data model and displayed in a table frame. Add two buttons labeled All Cities and Set Range of Cities. Finally, add two fields named fldStart and fldEnd, as shown here:
  2. Illustration 6

  3. Restructure the Customer table and add a secondary index called City (see Figure 14-3 for the settings).
  4. Figure 3: Add this City index to the Customer table

  5. Alter the pushButton event of the Set Range of Cities button as follows:
  6. 1: ;btnRange :: pushButton
    2: method pushButton(var eventInfo Event)
    3: var
    4: tcCustomer TCursor
    5: endVar

    6: if not tcCustomer.open("CUSTOMER") then ;Open TCursor.
    7: errorShow()
    8: endIf

    9: tcCustomer.switchIndex("City") ;Switch index on TCursor.
    10: tcCustomer.setRange(fldStart.value, fldEnd.value) ;Set range of records.

    11: CUSTOMER.resync(tcCustomer) ;Update the table frame.
    12: endMethod

  7. In the pushButton event of the All Cities button, enter line 3.
  8. 1: ;btnAll :: pushButton
    2: method pushButton(var eventInfo Event)
    3: CUSTOMER.switchIndex()
    4: endMethod

  9. Check the syntax, save the form as SetRange.fsl, and run it. This illustration shows the completed example. Your form should look similar:

Illustration 7

Using setGenFilter()

Using setGenFilter() requires two steps. First you declare a DynArray variable and populate it with the filtering data, and then you pass the DynArray to setGenFilter(). After you declare a DynArray, you assign values to it specifying the field and the values. Following are some examples of the types of formulas you can use with setGenFilter():

1: var
2: dyn DynArray[] String
3: endVar
4:
5: dyn["State"] = "CA" ;State field equals 'CA'.
6: dyn["Total"] = "< 0" ;Negative numbers in Total field.
7: dyn["Total"] = "> 100, < 1000" ;Greater then 100 & less then 1000.
8: dyn["Total"] = ">= 4, <= 8"

For example, to view all orders with a Balance Due over $100.00 and less than $1,000.00, enter the following on the pushButton event of a button on a form bound to the Orders table.

1: ;btnShowMiddle :: pushButton
2: pushButton (var eventInfo Event)
3: var
4: dyn DynArray[] String ;Declare DynArray.
5: endVar
6:
7: dyn["Balance Due"] = "> 100, <1000" ;Assign filter to it.
8: ORDERS.setGenFilter(dyn) ;Use it with setGenFilter().

Using Temporary Files

Sometimes, you need to create temporary tables to store information. Temporary tables are temporary because the data is needed only while the program is running. When you’re done, you can delete them. One technique for deleting the tables is to use the canDepart event of the form.

A better technique is to use a little-known feature built into Paradox. Whenever you quit Paradox, it deletes all the tables in the private directory whose names start with two underscores and stores them in your private directory. You can use this feature to your advantage. Whenever you create tables for temporary use in ObjectPAL, give them names that start with two underscores. Paradox takes care of deleting them for you.

This technique isn’t limited to tables. In fact, it isn’t limited to Paradox files. Whenever it exits, Paradox deletes all files in the private directory whose names start with two underscores. Use this feature to your advantage. Put all scratch files into your private directory and give them filenames that start with two underscores.

Manipulating Data in a Table

There are four basic approaches to manipulating tables and records with ObjectPAL:

  • Attach a Table variable to a table on disk. Then, use the Table object type methods to manipulate the table. (The table methods deal with the table as a whole.)
  • Open a TCursor or attach it to a UIObject. Then, use the TCursor class methods to manipulate the table. No manipulations are updated to the screen. If you want to update the screen, use resync().
  • Use the UIObject methods to manipulate the data. Each manipulation updates the screen as it occurs.
  • Send action commands to the UIObjects, such as active.action(DataNextRecord). The action commands simulate what a user does.

Tip: You can speed up a TCursor by using update(), setBatchOn(), or copyToArray(). If you use setBatchOn(), make sure to follow it with setBatchOff() every time you use it because it places an exclusive lock.

Copying an Entire Record with a TCursor

You can copy an entire record in a table frame by using the ObjectPal method copyToArray(). For the sake of simplicity, create a button on the form. In the pushButton event, use the following code:

1: method pushButton(var eventInfo Event)
2: var
3: recArr Array[] AnyType
4: endVar
5:
6: tFrameObject.edit()
7: tFrameObject.copyToArray(recArr)
8: tFrameObject.insertAfterRecord()
9: tFrameObject.copyFromArray(recArr)
10: endMethod

In doing this, a complete duplicate of the record will be entered after the current record. Another approach would be to attach the preceding code to the table frame object using the keyPhysical event to monitor which key was pressed.

Autoincrementing

So far, this chapter has only touched on the power and capabilities of the Table and TCursor variables. A whole book could be devoted to just these two variable types. This final section of this chapter addresses autoincrementing with the TCursor.

In this section, you learn how to autoincrement using ObjectPAL. First, you autoincrement a simple field. Second, you autoincrement a nonkey field. Third, for the most elegant solution, you add locking to the routine. By studying simple and elegant methods, you learn how to implement different routines under different situations and functional programming.

Autoincrementing a field involves inserting a new record, finding the highest value, adding 1 to it, and storing the new value. You already know how to insert a new record, as in the following:

1: active.insertRecord()
2: Line_Item.insertRecord()
3: self.action(DataInsertRecord)
4: tc.insertRecord()

To get the current highest value, either move to the end of the table and put the value in a variable, or use the cMax() method. Either way, after you get the highest value, you need to put it into a variable.

Autoincrementing and Locking

Now you have just one more loophole to close. Theoretically, it’s still possible for two users to end up with the same number. You can use autoincrementing with locks to make sure that this doesn’t happen. A lock is a feature of the BDE that prevents other users from viewing, changing, or locking a table or a record while one user has a lock on it. The next example uses autoincrementing with locks.

Example of Autoincrementing with Locks

Suppose that you want to autoincrement a field in a multiuser environment. To do this, you need to work with locks.

Step By Step

  1. Set your working directory to Paradox’s Samples directory. Create a new form with the Customer table in the data model:
  2. Illustration 8

  3. Create a table called incremnt.db (see Figure 14-4 for the structure).
  4. Figure 4: The incremnt table

  5. Open the incremnt table and add one row to it with the current highest Customer No value from the Customer table. This should be 9,841 unless you’ve altered the data in the table.
  6. Add lines 3 and 4 to the Var window of the page. Lines 3 and 4 declare a TCursor and SmallInt variables for use in the action event.
  7. 1: ;Page :: Var
    2: Var
    3: tc TCursor
    4: siCounter SmallInt
    5: endVar

  8. Alter the action event of the page as follows:
  9. 1: ;Page :: action
    2: method action(var eventInfo ActionEvent)
    3: if eventInfo.id() = DataInsertRecord then
    4: if not tc.open("incremnt.db") then errorShow() endIf
    5: siCounter = 0
    6: while not tc.lock("Full")
    7: siCounter = siCounter + 1
    8: message("Attempting to establish lock: " + String(siCounter))
    9: sleep(1000)
    10: if siCounter = 10 then
    11: DisableDefault
    12: msgStop("Warning", "Could not establish lock.")
    13: return
    14: endIf
    15: endWhile
    16: edit()
    17: DoDefault
    18: tc.edit()
    19: tc."Customer No" = tc."Customer No" + 1
    20: tc.postRecord()
    21: Customer_No = tc."Customer No"
    22: tc.unLock("Full")
    23: Name.moveTo()
    24: tc.close()
    25: endIf
    26: endMethod

  10. Check the syntax, save the form as Auto3.fsl, and run the form. Insert a record.

Summary

In this chapter, you learned about ObjectPAL’s Table and TCursor objects. You learned they differ in fundamental ways. Table objects give you a handle to a table and, in general, its methods and procedures deal with the table as a whole. A TCursor object gives you a handle to a specific record (or row) in a table and, in general, its methods and procedures deal with the data inside the table. When utilizing TCursor and Table variables, remember that you are utilizing another channel to the database. Table variables can lock out regular users by putting write and exclusive locks on the table. When programming a TCursor, think of the open TCursor as another user and code accordingly.


Comments

0 Comments.
Share a thought or comment...
 
Write a Comment...
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1257A1
Enter key:
KB Post Contributed By Mike Prestwood:

Mike Prestwood is a drummer, an author, and creator of the PrestwoodBoards online community. He is the President & CEO of Prestwood IT Solutions. Prestwood IT provides Coding, Website, and Computer Tech services. Mike has authored 6 computer books and over 1,200 articles. As a drummer, he maintains play-drums.com and has authored 3 drum books. If you have a project you wish to discuss with Mike, you can send him a private message through his PrestwoodBoards home page or call him 9AM to 4PM PST at 916-726-5675 x205.

Visit Profile

 KB Article #100207 Counter
14820
Since 4/2/2008
Go ahead!   Use Us! Call: 916-726-5675  Or visit our new sales site: 
www.prestwood.com


©1995-2019 Prestwood IT Solutions.   [Security & Privacy]