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.
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:
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:
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:
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:
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:
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().
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:
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.
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.
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.
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:
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.
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.
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.
Illustration 4
Name the TableFrame tfCustomer.
Restructure the Customer table and add a secondary index called secPhone (See Figure 14-1 for the settings).
Figure 1: Add this secPhone index to the Customer table
Restructure the Customer table again, this time adding a secondary index called secStreet (see Figure 14-2 for the settings).
Figure 2: Add this secStreet index to the Customer table
Alter the mouseClick event of the Customer No heading text object as follows:
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:
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:
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.
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 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.
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:
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
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:
Illustration 6
Restructure the Customer table and add a secondary index called City (see Figure 14-3 for the settings).
Figure 3: Add this City index to the Customer table
Alter the pushButton event of the Set Range of Cities button as follows:
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 pushButtonevent, use the following code:
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:
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.
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
Set your working directory to Paradox’s Samples directory. Create a new form with the Customer table in the data model:
Illustration 8
Create a table called incremnt.db (see Figure 14-4 for the structure).
Figure 4: The incremnt table
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.
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.
1: ;Page :: Var
2: Var
3: tc TCursor
4: siCounter SmallInt
5: endVar
Alter the action event of the page as follows:
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
Check the syntax, save the form as Auto3.fsl, and run the form. Insert a record.
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.
Paradox
Print This
Tags:
