I.T. Discussion Community!
-Collapse +Expand
Paradox
Search Paradox Group:

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

Prestwood eMagazine

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

   ► KBDesktop Data...Paradox & Ob...ObjectPAL Co...OPAL: Langua...   Print This     
  From the December 2015 Issue of Prestwood eMag
 
Paradox OPAL: Language Details:
Using ObjectPAL's TCursor Object Type
 
Posted 11 years ago on 6/5/2008 and updated 3/13/2009
Take Away:

Exploring ObjectPAL's TCursor object type.

KB101169



Encapsulation is the bundling of methods and variables within an object so that access to the variables is permitted only through the object's published interface. ObjectPAL supports encapsulation. It was used to create Paradox and is surfaced nicely in ObjectPAL. The TCursor has a lot of code associated with it that allows us programmers to manipulate data, but only the published interface is available to us. The details of how it does things is hidden within the TCursor object.

Each ObjectPAL object is also known as an object type. ObjectPAL also has a long list of commands called methods and procedures that a programmer can use. These methods and procedures are organized by object types. For example, there is a set of methods and procedures associated with the TCursor object type (a TCursor allows you to access a table).

A TCursor (table cursor) is a tool used to manipulate the data in a table and is a pointer to a record in a table and is one of the five data model objects. You use data model objects to manipulate data in tables and databases. Here are all five:

  • Database object
  • Query object
  • SQL object
  • Table object
  • TCursor object 

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.

Declaring and 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

As soon as the variable is declared, you can use any of the methods in its class to manipulate it. For example, as soon as a TCursor is defined, you then can use any of the TCursor object type or class of methods to manipulate it:

TCursors are Fast

Fast code is more elegant than slow code. Code that uses a TCursor (a pointer to a table) can, in some situations, be more elegant than code that uses a UIObject (a user interface object). A TCursor is faster because it doesn't have the overhead of screen refreshes. (In many cases, however, you can accomplish the same objective by using a UIObject-especially if it's already there.)

How does the TCursor type compare to the Table type?

The Table object's methods and procedures represent the table itself and are distinct from a TCursor's methods and procedures. A TCursor object is a pointer to the data while a Table object points to the whole table. It also is distinct from a TableFrame and a TableView, which are objects that display the data. Use the Table methods and procedures to add, copy, create, and index tables, to do column calculations, and to get information about a table's structure. Don't use Table methods to edit records, however. Use a TCursor or a table frame-a UIObject-instead.

Many of the UIObject methods are duplicated among the TCursor methods. For example, insertRecord() works on both a UIObject and a TCursor. The UIObject methods that work with tables work on the underlying table by means of a visible object. Actions directed to the UIObject that affect a table are immediately visible in the object to which the table is bound. On the other hand, TCursor methods work with a table behind the scenes as if another user were making the changes.

Tip: If you ever have a calculated field on a form not update when you think it should, then you can use the ObjectPAL method forceRefresh() to make the calculated field display the correct values. This can occur, for example, when you update a field involved with a calculated field using a TCursor. 

Using TCursors

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 on screen. Because the screen isn't refreshed, changes are made quickly.

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: sFieldString   ;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().

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:

Name the drop-down field fldCommonName.

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).

Method open(var eventInfo Event)
   DoDefault
   Self.datasource = "[:work:biolife.\"Common Name\"]"
endMethod

Alter the pushButton event of the button as follows:

;Button :: ushbutton
method ushbutton(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

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.

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.

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.

Alter the ushbutton event as follows:

 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

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

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.

Name the TableFrame tfCustomer.

Restructure the Customer table and add a secondary index called secPhone (See Figure 14-1 for the settings).

  • 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).

  • Add this secStreet index to the Customer table

Alter the mouseClick event of the Customer No heading text object as follows:

1: method mouseClick(var eventInfo MouseEvent)
2: tfCustomer.switchIndex()
3: endMethod

Alter the mouseClick event of the Street heading text object as follows:

1: method mouseClick(var eventInfo MouseEvent)
2: tfCustomer.switchIndex("secStreet")
3: endMethod

Alter the mouseClick event of the Phone heading text object as follows:

1: method mouseClick(var eventInfo MouseEvent)
2: tfCustomer.switchIndex("secPhone")
3: endMethod

Check your syntax, save the form as CustomerList.fsl, and switch it to View Data mode. Click the Street, Phone, and Customer No fields.

ObjectPAL Garbage Collection -- 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
 2:  var
 3: tc TCursor
 4:  endVar

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

 1:  ; Button1 :: pushbutton
 2:  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

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 ushbutton(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.

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.

Restructure the Customer table and add a secondary index called City.

  • Add this City index to the Customer table

Alter the ushbutton event of the Set Range of Cities button as follows:

 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

In the pushbutton event of the All Cities button, enter line 3.

 1:  ;btnAll :: pushbutton
 2:  method ushbutton(var eventInfo Event)
 3:  CUSTOMER.switchIndex()
 4:  endMethod

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

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 ushbutton event of a button on a form bound to the Orders table.

 1:  ;btnShowMiddle :: pushbutton
 2:  method 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().
 9:  endMethod

Using Temporary Tables

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.

Copy a 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.

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

Set your working directory to Paradox's Samples directory. Create a new form with the Customer table in the data model.

Create a table called incremnt.db.

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.

TCursors and Built-In Error Codes

Rather than always just setting the error code to a nonzero value, try using built-in error constants whenever possible. The following example enforces uniqueness in a table and checks whether the field is required. This example is interesting because it uses the peReqdErr and peKeyViol constants instead of just nonzero values.

 1: ;Record :: action
 2: var
 3:   tc TCursor
 4: endVar
 5:
 6: ;check for required field
 7: if isBlank(Ship_Via) and self.locked then
 8:   eventinfo.setErrorCode(peReqdErr)
 9:   return
10: endIf
11:
12: ;Key violation check
13: tc.attach(ORDERS)
14: if tc.locate("Order_No", Order_No.value) then
15:   if tc.recNo()<> self.recNo then
16:     eventinfo.setErrorCode(peKeyViol)
17:   endIf
18: endIf

Note that the preceding code works for both Paradox and dBASE tables.

Using Scan Loops

You bought Paradox so that you could view, enter, and manipulate data. For the ObjectPAL programmer, the scan loop is very easy to use, and it's a powerful tool for manipulating the data in a table. In many ways, using a scan loop is similar to using a query in Interactive mode. In fact, there are occasions when using a scan loop is faster than doing a complicated query.

The scan loop is an extremely powerful tool that you can use to manipulate a whole table or, with the for keyword, a subset of a table. The scan loop scans a TCursor and executes ObjectPAL instructions. The syntax for a scan loop is as follows:

scan tcVar [for booleanExpression]:
  Statements
endScan

The colon at the end of the first line is required. It separates the first line from the following statements. Scan starts with the first record in a TCursor and moves from record to record, executing Statements for every record. Scan automatically moves from record to record through the table, so you don't need to call action(DataNextRecord) within your statements. When an indexed field is changed by a scan loop, the changed record moves to its sorted position in the table. Therefore, it's possible to encounter the same record more than once. As with all the loops discussed in this chapter, be sure to put statements that occur only once before the loop.

The for expression is used to filter the records. Only the records that match the expression are acted on. All other records are skipped. When you use the for keyword with scan, it must be followed by a colon to differentiate it from a for loop.

Loop to Make a Field Proper

Suppose that you want to use a scan loop to change a field to a proper format. This example uses a scan loop and the format() command to change a field named Name in the CUSTOMER.DB table to a proper format. Before you make drastic changes to a table, you should always make a backup copy. This section acquaints you with the script, the scan loop, and format().

Make the Paradox's Samples directory your working directory. Open the CUSTOMER.DB file. Change some of the last names to all uppercase or all lowercase.

Choose File | New | Script and type in lines 3-16. Line 4 declares tc as a TCursor variable. Line 7 asks for permission to continue using a message question box. If the answer is Yes, then line 8 opens the TCursor and puts it in Edit mode in line 9. In this code, the scan loop uses only three lines: lines 10-12. Line 13 ends the edit session, and line 14 closes the TCursor. Line 15 tells the user that the program is done.

 1: ;Script :: Run
 2: method run(var eventInfo Event)
 3: Var
 4: tc TCursor
 5: endVar
 6:
 7: if msgQuestion("Question?",
  "Make Name field proper?") = "Yes" then
 8: tc.open("CUSTOMER.DB")
 9: tc.edit()
10: scan tc:
11: tc."Name" = format("cl,cc", tc."Name")
12: endScan
13: tc.endedit()
14: tc.close()
15: msgInfo("","All done converting Name field")
16: endIf
17: endMethod

Check the syntax, save the script as PROPER.SSL, and run the script. After the script has finished executing, reopen CUSTOMER.DB. Now the Name field is in a proper format.

Using In-Memory TCursors

In-memory TCursors are interesting because they are fast and they are not connected to the live data. You can ask "what if" questions of the data before writing the data to the live database. The command wantInMemoryTCursor() is used when you want to specify how a TCursor is created resulting from a query. Following is the syntax:

wantInMemoryTCursor(const yesNo Logical)

Note: The procedure wantInMemoryTCursor(Yes) is only necessary for queries that would normally produce live query views. If the query doesn't meet the conditions for a live query, and the answer is a TCursor, the TCursor will be in memory nonetheless.

Specify Yes (or Null) to build the TCursor in-memory; the TCursor is not connected to a table. Specify No to attach the TCursor to the live data. If you do not call wantInMemoryTCursor() before you execute a query to a TCursor, the default is the same as if you called it and passed it No; the resulting TCursor is live to the data. The exception is with multitable queries that specify an answer TCursor, which will always produce an in-memory TCursor. Here is how you use wantInMemoryTCursor() with a query to gain access to an in-memory TCursor:

 1:   ;Button :: pushbutton
 2:   method pushbutton(var eventInfo Event)
 3: var
 4:   qryCustomerQuery  Query
 5:   tcCustomerQuery   TCursor
 6: endVar
 7:
 8: qryCustomerQuery.wantInMemoryTCursor(Yes)
 9: qryCustomerQuery.readFromFile("CustomerQuery.QBE")
10: qryCustomerQuery.executeQBE(tcCustomerQuery)
11:
12: ;Utilize in-memmoy TCursor here.
13:   endMethod

This command also works with SQL variables. In fact, SQL variables are very similar in nature to Query variables. You manipulate them with ObjectPAL using the same techniques. Other methods that are useful with in-memory TCursors include isInMemoryTCursor(), isView(), and instantiateView(). isInMemoryTCursor() returns True when the TCursor is an in-memory TCursor. isView() returns True if the TCursor is connected to live data. Finally, instantiateView() copies an in-memory TCursor to a physical table.

Transactions: The Protocol for Communication

Client applications such as Paradox communicate with SQL database servers with a unit of work called a transaction. Although a transaction is perceived as a single operation, a transaction may consist of a number of operations. For example, a single transaction could update multiple records. If you want to have some control over undeleting changes to your data, then use the following methods and procedures:

  • beginTransaction()                           Starts a transaction on a server
  • commitTransaction()                        Commits all changes within a transaction
  • rollbackTransaction()                       Rolls back all changes within a transaction (undo feature)

Commit and Rollback Overview

The term commit is part of a concept for entering data. The idea is to enter data into a temporary table and commit, or copy, the data to a main table. The term rollback is also part of a concept for entering data. The idea is to enter data into a temporary table with the opportunity to empty the temporary table and to leave the main table untouched. The term two phase commits applies this idea over a wide area network. With two phase commits, you can have data entry from anywhere in the world.

In the following example, the first record 1001 is posted to the table and then undone with the rollback:

1:  ;Button :: pushButton
2:  method pushButton(var eventInfo Event)
3:  var
4:  dbSQL  Database
5:  tc  TCursor
6:  endVar
7:
8:  dbSQL.open(":Server1:")
9:  dbSQL.beginTransaction()
10: tc.open(":Server1:guest.customer")
11: tc.insertRecord()
12: tc.(1) = 1001
13: tc.(2) = "Mike Prestwood"
14: tc.postRecord()
15: dbSQL.rollbackTransaction()   ;Un-inserts record 1001.
16: dbSQL.begintransaction()
17: tc.(1) = 1002
18: tc.(2) = "Lisa Prestwood"
19: dbSQL.commitTransaction()  ;Commits record 1002.
20: tc.close()
21: endMethod

Statement Atomicity

In ObjectPAL, all database commands are committed immediately. For example, a single QBE query, a single ObjectPAL table append, and a single edit operation are examples of commands that are executed and committed to the database immediately.

Referring to Fields with a TCursor Standard

When using a TCursor to refer to fields in a table, always use quotation marks, or dereference with parentheses. There are two ways to refer to a field using a TCursor: tc.Last_Name, tc."Last_Name", and a third for dereferencing - tc.(2). The preferred usage for referencing a field is with quotation marks. For example, in tc."Name" it is clear that Name is a field.

Referring to Columns with Local SQL Standard

Since single quotes are not allowed when referring to fields using a TCursor, the PCC standard for referring to fields using both local SQL or a TCursor is to use quotation marks (sometimes referred to as double quotes). The following SQL statement abides by the PCC standard:

select o."City"
from Orders o

The following usage of a TCursor abides by the PCC standard:

var
  tcCustomer TCursor
endVar
tcCustomer.open("Customer")
msgInfo("", tcCustomer."City")

Notice the use of double quotes in the previous two examples to refer to the City field even when quotes (single or double) was not required.

Naming TCursors

Use common sense when naming objects. After the initial 1- to 3-character beginning, use a common-sense, camel-caps description. For example, use the table name for Table and TCursor variables (tcOrders, tcCustomer, tc1Lineitem, tc2Lineitem). Use the name of the table or field to name objects that are defined to a table or a field (tfOrders, fldLast_Name).

Warning and Critical Errors

In ObjectPAL, two levels of errors occur: warning errors and critical errors. Because warning errors aren't critical errors, they display nothing during runtime, or, at most, a message in the status bar. A key violation error is an example of this type of error. If you want to include a higher level of error trapping in your applications, use one of the following techniques:

  • If the method or procedure returns a Logical, use it in an if statement.
  • Use errorShow() to display the built-in error messages.
  • Use a try structure to trap for errors.

Warning errors do not stop execution, whereas critical errors do. To illustrate, type the following code into the pushButton event of a button.

1:  ;btn1 :: pushButton
2:  method pushButton(var eventInfo Event)
3:    var
4:      tc  TCursor
5:    endVar
6:
7:    errorTrapOnWarnings(No)  ;Make sure warning errors
8:    ;stay warning errors.
9:
10:   msgInfo("", "Before error")
11:   tc.open("123xyz")  ;123xyz does not exist.
12:   msgInfo("", "After error")  ;This message does appear.
13: endMethod

Now, raise the warning error to a critical error. Type the following:

1:  method pushButton(var eventInfo Event)
2:    var
3:      tc  TCursor
4:    endVar
5:
6:    errorTrapOnWarnings(Yes) ;Raise warning errors to
7:    ;critical errors.
8:
9:    msgInfo("", "Before error")
10:   tc.open("123xyz")  ;123xyz does not exist.
11:   msgInfo("", "After error")  ;Note that this message never appears.
12: endMethod

This is an important part of dealing with errors. Note that warning errors do not stop execution of code, whereas critical errors do.

More Info

Tip:  Speed Up TCursors with setBatchOn()

Linked Message Board Threads

 TCursor not opened in ObjectPAL MB Topic (3 replies)
 Two TCursors problem in ObjectPAL MB Topic (15 replies)
 Opening a Tcursor on Saddam in ObjectPAL MB Topic (4 replies)
 updating table from table with TCursor in ObjectPAL MB Topic (38 replies)
 Help with Using a TCursor in ObjectPAL MB Topic (9 replies)
 Howto: Update PK of master table w/ Tcursor. in ObjectPAL MB Topic (2 replies)
 TCursor Closing Automation in ObjectPAL MB Topic (10 replies)
 TCursor quits after 85 records in ObjectPAL MB Topic (5 replies)
 Syncronising with tcursors in ObjectPAL MB Topic (2 replies)
 TCursor in ObjectPAL MB Topic (1 replies)
 Using TableFrames and TCursors together in ObjectPAL MB Topic (1 replies)
 Use library custom method to get TCursor to ditto in ObjectPAL MB Topic (3 replies)

Comments

1 Comments.
Share a thought or comment...
Comment 1 of 1

how would one write a code a SSL(script) program that would queuy the

INVOICES.DB file on the STARTDATE field on a RANGE or DATES

from 05/01/2010 to 05/31/2010, then saving results to the db file

using the present WORKING DIRECTORY as "RESULTS.DB" ?

---
troy nall
Posted 8 years ago
 
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 = P1161A1
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 #101169 Counter
38913
Since 6/5/2008
Follow PrestwoodBoards on: 


©1995-2019 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech