Saturday, December 17, 2022

Introducing Clarion FileManager (Part 2)

SoftVelocity Clarion

This is a continuation from Part 1.

Exercise 5: Try Some Error Checking

So far we have simply assumed everything is working, but we haven't tested for any error conditions. You shouldn't do that in real programs. In particular, we should use TryOpen, TryNext, TryNext, TryInsert and TryUpdate.

Work with pairs

One way to make this easier is to work with method pairs while you are writing the code. The first is the TryOpen and Close pair:
if Access:Table.TryOpen() = Level:Benign then ! Open Table
    !// Table has been opened
    
else ! Open Table
    ud.Debug(' Open Table failed')
end ! Open Table
Access:Table.Close()
Next, add the ClearKey and SET pairs.
if Access:Table.TryOpen() = Level:Benign then ! Open Table
    !// Table has been opened
    Access:Table.ClearKey(TBL:KeyName) ! Top of the primary key
    SET(TBL:KeyName) ! Set the processing order
else ! Open Table
    ud.Debug(' Open Table failed')
end ! Open Table
Access:Table.Close()
After that, you will probably want a loop to read several records. So add in the LOOP UNTIL TryNext and END pair. 
if Access:Table.TryOpen() = Level:Benign then ! Open Table
    !// Table has been opened
    Access:Table.ClearKey(TBL:KeyName) ! Top of the primary key
    SET(TBL:KeyName) ! Set the processing order
    LOOP UNTIL Access:Table.TryNext() <> Level:Benign ! Loop Table
    
    END ! Loop Table
else ! Open Table
    ud.Debug(' Open Table failed')
end ! Open Table
Access:Table.Close()
Notice how we comment the "if ... then", "else" and "end" statements to keep track of which "end" statement belongs to which "if" statement. Also, the same applies to the "LOOP" and "END" statements. Don't only rely on Ctrl-i to do the indenting for you. Use it to check your own comments and see if they line up properly. If not, check your code.
Also, you can avoid unforeseen bugs by ensuring that you put an "else" whenever you have an "if". Get into the habit of writing the "else" and the "end" whenever you write the "if", and put the comments at the end of each line, before you worry about what happens immediately after the "if".
With all of this in mind, open "MyTestCode" in the Embeditor and fix up the code. Note I have also copied the CUS:Address code into the insert block. It should end up something like this:
 Access:Customer.UseFile()
 if Access:Customer.TryOpen() = Level:Benign then ! Customer Open
     Access:Customer.Clearkey(CUS:KEYCUSTNUMBER) ! Top of the primary key
     SET(CUS:KEYCUSTNUMBER) ! sets the processing order
     i = 0 ! Record counter
     LOOP UNTIL Access:Customer.TryNext() <> Level:Benign ! CUS loop
         !// Process each customer record
         i += 1 ! Count the records
         ud.Debug(' Customer: ' & CUS:CustNumber |
             & ' Company: ' & clip(CUS:Company))
         0{PROP:StatusText,1} = 'Customer record ' & i
     END ! CUS loop
     ud.Debug(' Loop ends. There are ' & i & ' records')
     0{PROP:StatusText,1} = 'There are ' & i & ' records'
     !// List of US state codes, including Guam and Puerto Rico
     strStates = '..AK.AL.AR.AZ.CA.CO.CT.DE.FL.GA.GM.HI.IA.ID.IL.' |
       & 'IN.KS.KY.LA.MA.MD.ME.MI.MN.MO.MS.MT.NC.ND.NE.NH.NJ.NM.' |
       & 'NV.NY.OH.OK.OR.PA.PR.RI.SC.SD.TN.TX.UT.VA.VT.WA.WI.WV.WY.'
     !// State table lookups    
     Access:State.UseFile()
     if Access:State.TryOpen() = Level:Benign then ! State Open
         Access:State.Clearkey(STA:KEYSTATE) ! Top of the primary key
         SET(STA:KEYSTATE) ! sets the search order 
         !   
         !// Add in and update some more records
         LOOP i = 9 TO RANDOM(150,250) ! i
             CUS:CustNumber = i
             IF Access:Customer.TryFetch(CUS:KEYCUSTNUMBER) |
                = Level:Benign THEN ! Fetch
                 ud.Debug(' Fetched Customer: ' & CUS:CustNumber |
                     & ' State ' & clip(CUS:State))
                 STA:State = CUS:State ! This is the state code we want
                 IF Access:State.TryFetch(STA:KEYSTATE)|
                    = Level:Benign THEN ! STA
                     CUS:Address = 'Here in ' & clip(STA:StateName)
                 ELSE ! STA
                     CUS:Address = 'Not Supplied'
                 END ! STA 
                 if Access:Customer.TryUpdate() = Level:Benign then
                     ! Update changes to the customer record
                     ud.Debug(' Updated Customer: ' & CUS:CustNumber)
                     0{PROP:StatusText,1} = 'Updated customer ' |
                         & CUS:CustNumber
                 else ! Update
                     ud.Debug(' Failed to update Customer: ' |
                         & CUS:CustNumber)
                     0{PROP:StatusText,1} = 'Failed to update customer ' |
                         & CUS:CustNumber                         
                 end ! Update
             ELSE ! Fetch
                 !// Record not found, so add it in
                 Access:Customer.PrimeRecord() ! Cust number is autonumber
                 CUS:Company = 'Company ' & i
                 CUS:FirstName = 'First ' & i
                 CUS:LastName = 'Last ' & i
                 n = RANDOM(1,52) ! Get a random state number
                 CUS:State = SUB(strStates,n*3,2) ! State Code
                 CUS:City = SUB(strStates,n*3,2) & ' City'
                 CUS:ZIPcode = n * 1000 + i ! Made up "Zip" code
                 STA:State = CUS:State ! This is the state code we want
                 IF Access:State.TryFetch(STA:KEYSTATE)|
                    = Level:Benign THEN ! STA
                     CUS:Address = 'Here in ' & clip(STA:StateName)
                 ELSE ! STA
                     CUS:Address = 'Not Supplied'
                 END ! STA                  
                 if Access:Customer.TryInsert() = Level:Benign then
                     ! Add in the customer record
                     ud.Debug(' Added Customer: ' & CUS:CustNumber |
                         & ' Company: ' & clip(CUS:Company))
                     0{PROP:StatusText,1} = 'Added customer ' |
                         & CUS:CustNumber
                 else ! Insert
                     ud.Debug(' Failed to add Customer: ' |
                         & CUS:CustNumber & ' Company: ' |
                         & clip(CUS:Company))
                     0{PROP:StatusText,1} = 'Failed to add customer ' |
                         & CUS:CustNumber
                 end ! Insert
             END ! Fetch        
         END ! i
     else ! State Open
         ud.Debug(' Unable to open the State table')            
     end ! State Open
 else ! Customer Open
     ud.Debug(' Unable to open the Customer table')
 end ! Customer Open
 0{PROP:StatusText,1} = 'Test code completed.'
 Access:Customer.Close()
 Access:State.Close()
Now run the application and see if the debug messages match what you expected to see.
You may decide that it is important to notify the user of some error conditions. I have only used debug messages in this example for consistency, not for any other reason.
Warning: If you write 
if Access:Customer.TryUpdate() then
and omit the 
= level:Benign
part you will tear your hair out for days trying to figure out why the update "isn't working" when in fact it is working correctly but the IF statement is the wrong way around. I speak from personal experience.

Exercise 6: Making Changes Across Multiple Records

In SQL it takes a single UPDATE command with some WHERE clauses to update a bunch of records. With FileManager we don't always have that luxury. Say, for example, we want to delete all customers in the state of California. One way to do it is simply to loop through all the records and every time we find a California state code, delete the record and move on. It's not particularly efficient, but it will get the job done.
Open LCLesson, select the "BrowseCustomers" form and open it with the Window designer.
Drag a button control from the Toolbox and place it on the form as shown.
Change the Text to "Delete CA", the Use property to "?btnDeleteCA", the TextColour to Red, and the Message property to "Delete CA Customers". Then select the button, and double-click it.
This should open the Embed points for the button. Click on "Accepted", press the "Insert" button, choose "Source" and "Select".
Type in "ud.Debug(' Delete CA')" and press the green "Save and Close" button three times. Save your work. Now click on the "Embeditor" button.
Click on the "Next Filled Embed" button until you find the "ud.Debug" statement. Now we can add some suitable code:
   ud.Debug(' >Delete CA')
   Access:Customer.UseFile()
   if Access:Customer.TryOpen() = Level:Benign then ! Customer Open
       Access:Customer.ClearKey(CUS:KEYCUSTNUMBER)
       SET(CUS:KEYCUSTNUMBER)
       LOOP UNTIL Access:Customer.TryNext() <> Level:Benign ! Cust Loop
           ?btnDeleteCA{PROP:Text} = clip(CUS:CustNumber) ! Display Cust No
           DISPLAY() ! Update the screen display
           !// Delete stuff goes here
       END ! Customer Loop
   end ! Customer Open
   Access:Customer.Close()
   ud.Debug(' <Delete CA')
Use the green "Save and Close" button to exit the Embeditor. Save your work. Start the application and test the button.
We haven't actually deleted anything, but we have made sure that the looping works. Use the "Fast Forward" button to go to the last record and check that the record number matches the number on the button text.
So let's return to the code and make it a bit faster, and to count how many records were deleted. The variable i# is an "implicit LONG" variable used here.
   ud.Debug(' >Delete CA')
   Access:Customer.UseFile()
   if Access:Customer.TryOpen() = Level:Benign then ! Customer Open
       Access:Customer.ClearKey(CUS:KEYCUSTNUMBER)
       SET(CUS:KEYCUSTNUMBER)
       i# = 0
       LOOP UNTIL Access:Customer.TryNext() <> Level:Benign ! Cust Loop
           if clip(CUS:State) <> 'CA' then CYCLE . ! Try next record
           ?btnDeleteCA{PROP:Text} = clip(CUS:CustNumber) ! Display Cust #
           DISPLAY() ! Update the screen display
           !// Delete without asking
           if Access:Customer.DeleteRecord(False) |
               = Level:Benign then ! Delete
               ud.Debug(' Record deleted ' & CUS:CustNumber)
               i# += 1 ! Count the deleted record
           else ! Delete
               ud.Debug(' Record NOT deleted: ' & CUS:CustNumber)
           end ! Delete
       END ! Cust Loop
   end ! Customer Open
   Access:Customer.Close()
   ?btnDeleteCA{PROP:Text} = 'Deleted ' & i# ! No of records deleted      
   ThisWindow.Reset(1) ! Update display to remove the deleted records
   ud.Debug(' <Delete CA')
Some lines need further explanation: the purple line is a one-line if statement because it ends with a dot. The CYCLE command forces execution to go to the top of the LOOP, so everything below the purple line is ignored, down to the END of the loop. It prevents non-'CA' records from being deleted.
This is another way to write the test: look for the qualifying record and process it inside the "if" block. It's a matter of preference and legibility. You choose.
Click on the green "Save and Close" button, save your work and start the application. Let's see if the new code actually works as intended.
How to check? Click on the "by ZIP Code" tab and see if you can see any California addresses.
Did you notice these lines? 
   if Access:Customer.DeleteRecord(False) = Level:Benign then ! Delete
       ud.Debug(' Record deleted ' & CUS:CustNumber)
       i# += 1 ! Count the deleted record
   else ! Delete
       ud.Debug(' Record NOT deleted: ' & CUS:CustNumber)
   end ! Delete
How could we display the Customer number from a deleted record? (Line 2) Because the record buffer did not disappear when the physical record was deleted. It only changes once we move to the next record.

Exercise 7: Indexes Make Record Access Easier

Close the LCLesson application and open the LCLesson dictionary instead. Consider the Customer table.
Notice that the "State" code is not part of an index. So let's add an index for it. This will enable us to jump to any record based on its state code.
Right-click on the "Keys" folder and choose "Add Key"
Give the key the label "KEYSTATECODE" and a description, and then add the field "State" as the only field in the key. Click "OK"
Move the new key to the bottom of the list, and then click on the "Save All" button to save these changes. Now we need to get the data file itself to change.
Right-click on the Customer table and select "Browse Table".
Choose the "Convert and backup" option, and click "OK".
Click the top dropdown box to check that the new index/key is present in the data file. Close the window, and close the dictionary.
From the Start Menu, open the LCLesson Application. We need to introduce some changes to the Customer browse form. Select it, and click on the "Window" button to open the Window Designer.
Use the "List Box Format" command to change the width property of "First Name" and "Last Name" to "40" instead of "80". Then adjust the width of the screen to 640, and adjust the SHEET and the LIST controls as shown.
Now click on the SHEET to select it, and then right-click to add a new Tab.
In the properties on the right is a "Tabs" property, with the word "(Collection) ..." after it. Click on the ellipsis button and choose "tab4" in the Control Collection Editor". Change its text to "by State Code" and click "OK".
Change to the new "by State Code" tab, and then drag and drop the "State" field from the Data/Tables window and place it below the State column as shown. You can delete the label. Then mark the State control as "Flat" and "Read Only" as shown. This will display the state code for whatever record is selected in the browse form. Click on the green "Save and Close" button and save your work.
Keep the "BrowseCustomers" form selected, and click on the "Extensions" button. This will display the extensions that apply to the form.
First, we are going to delete the top extension, the one that allows the form to be resized. This will prevent the buttons moving around on the form. Then double-click on the line that reads "Browse on Customer ()".
Change to the "Conditional Behavior" tab, and click on the "Properties" button. This shows how the sort behaviour based on which tab is selected, works. Highlight and copy the "Condition" expression and click "Cancel".
Now click "Insert", and paste the clipboard to the "Condition" field. Change it to end with a "4" instead of a "2". Click on the ellipsis on the "Key to use" field and select the "KEYSTATECODE" key. Click "OK".
Make sure the choices are in numerical order, for sanity's sake. Click "OK". Click on the green "Save and Close" button and save your work. Start the application and try out the changes in the Customer Browse form.
Adjust the screen to fit the data. Notice how the scroll bar is using up some of the data space, so let's make a note to reduce the size of the "City" column just a bit (change the width from "80" to "70"). Notice how the data is sorted by the field on which the key is based. Close the app and return to Clarion.
Open the Window Designer and return to the "by State Code" tab. Add a button labeled "?btnDeleteThis" with text "Delete this State:" and Red TextColor. Change the adjacent "State" control to have red text too, and line up their middles for neatness.
Double-click on the button and click on the "Accepted" event. Thenc click on the "Insert" button and choose "Source". Type in "ud.Debug(' btnDeleteThis ' & CUS:State)" and click on the green "Save and Close" buttons (three times) and save your work.
Note: always use the "Insert" button shown. Do not be tempted to use the "Source" button to get into the code and add in stuff. It will cause the Clarion IDE to crash and you will lose some of your work. Never enter the Embeditor directly from the Window Designer.
Now that we have left the Window Designer, we can go to the code using the Embeditor. Click on the "Next Filled Embed" button a few times until you get to the "btnDeleteThis" section. The code is not going to be the same as the previous button, but we will borrow pieces from it.
First, move up to the top of the "ThisWindow.TakeAccepted" procedure, and define two variables as shown. Then move down to the "btnDeleteCA" code block and change the three "i#" entries to "i" because we have explicitly defined the i variable as LONG. Now let's write the new code:
  strStateCode = clip(CUS:State) ! Remember which state to delete
  ud.Debug(' ?btnDeleteThis ' & strStateCode)
  Access:Customer.UseFile()
  if Access:Customer.TryOpen() = Level:Benign then ! Customer Open
      Access:Customer.ClearKey(CUS:KEYSTATECODE) ! Top of the index
      CUS:State = strStateCode ! Jump to this state
      ! SET using this index, starting at this index value
      SET(CUS:KEYSTATECODE,CUS:KEYSTATECODE)            
      i = 0
      LOOP UNTIL Access:Customer.TryNext() <> Level:Benign ! Cust Loop
          if clip(CUS:State) = strStateCode then ! State
              ! Display Customer Number
              ?btnDeleteThis{PROP:Text} = clip(CUS:CustNumber) 
              DISPLAY() ! Update the screen display
              !// Delete without asking
              if Access:Customer.DeleteRecord(False) |
                  = Level:Benign then ! Delete
                  ud.Debug(' Record deleted ' & CUS:CustNumber)
                  i += 1 ! Count the deleted record
              else ! Delete
                  ud.Debug(' Record NOT deleted: ' & CUS:CustNumber)
                  MESSAGE('Unable to delete ' & CUS:Company)
              end ! Delete
          else ! State
              !// We have moved on to the next state code
              ud.Debug(' Break at ' & CUS:State)
              BREAK ! All done
          end ! State
      END ! Cust Loop
  end ! Customer Open
  Access:Customer.Close()
  ?btnDeleteThis{PROP:Text} = 'Deleted ' & i ! No of records deleted       
  ThisWindow.Reset(1) ! Update display to remove the deleted records
  ud.Debug(' <btnDeleteThis ' & strStateCode)
Note the SET command has the same index listed twice, to specify the search and sort order, and to allow you to specify the starting point of the loop. From the help file: "SET (key,key) specifies keyed sequence processing and positions to the first ... record which contains values matching the values in the component fields of the key. Both key parameters must be the same."
I always think of this technique as the "hop, skip and jump" method. We start with the index being reset. Then we use the assignment statement to tell the index which record to "hop" to. While we loop through the records, we are skipping from one valid record to the next. When we run out of valid records, we use the BREAK command to "jump" out of the loop because there are no more valid records. The index is doing most of the work, instead of having to rummage through all the records in the entire table.
Close the Embeditor, save your work, and Start the application. Go to a state near the end of the alphabet (I chose Vermont) and click the "Delete this State:" button. Watch in the debugger how the program skips through the records.

Exercise 8: Be Careful Not To Delete Parent Records

I assumed that since there was a relationship between Customers and Orders (and it has a "restrict" setting for deleting records) that "Customer" records with existing child records in the "Orders" table, would not be deleted. I was wrong. In any case, it is always better to check for these things explicity than to expect them to happen implicitly.
Open the Embeditor for the "BrowseCustomers" form, and go to the "Data/Tables" panel. Click on the "Other Files" folder, and the "Add" button, and select the "Order" table. This makes it easy to see the Index and Field names, and to drag and drop them into the code. So let's modify the code for the "delete this" button:
  strStateCode = clip(CUS:State) ! Remember which state to delete
  ud.Debug(' ?btnDeleteThis ' & strStateCode)
  Access:Customer.UseFile()
  if Access:Order.TryOpen() = Level:Benign then ! Order Open
    if Access:Customer.TryOpen() = Level:Benign then ! Customer Open
      Access:Order.ClearKey(ORD:KEYCUSTNUMBER) ! Top of the index
      Access:Customer.ClearKey(CUS:KEYSTATECODE) ! Top of the index
      CUS:State = strStateCode ! Jump to this state
      SET(CUS:KEYSTATECODE,CUS:KEYSTATECODE)  
      SET(ORD:KEYCUSTNUMBER) ! Use this index to find stuff 
      i = 0
      LOOP UNTIL Access:Customer.TryNext() <> Level:Benign ! Cust Loop
        if clip(CUS:State) = strStateCode then ! State
            ?btnDeleteThis{PROP:Text} = clip(CUS:CustNumber) ! Display Cust #
            DISPLAY() ! Update the screen display
            !// Delete without asking
            ! Look for this Customer in the Orders Table
            ORD:CustNumber = CUS:CustNumber 
            IF Access:Order.TryFetch(ORD:KEYCUSTNUMBER) |
              <> Level:Benign THEN ! Orders
                !// No orders found. Safe to delete empty customer
                if Access:Customer.DeleteRecord(False) |
                  = Level:Benign then ! Delete
                    ud.Debug(' Record deleted ' & CUS:CustNumber)
                    i += 1 ! Count the deleted record
                else ! Delete
                    ud.Debug(' Record NOT deleted: ' & CUS:CustNumber)
                    MESSAGE('Unable to delete ' & CUS:Company)
                end ! Delete
            ELSE ! Orders
                ud.Debug(' Orders exist. Record NOT deleted: ' & CUS:CustNumber)
                MESSAGE('Unable to delete ' & clip(CUS:Company) | 
                    & ' because of existing orders')    
            END ! Orders
        else ! State
            !// We have moved on to the next state code
            ud.Debug(' Break at ' & CUS:State)
            BREAK ! All done
        end ! State
      END ! Cust Loop
    end ! Customer Open
  end ! Order Open
  Access:Customer.Close()
  Access:Order.Close()
  ?btnDeleteThis{PROP:Text} = 'Deleted ' & i ! No of records deleted       
  ThisWindow.Reset(1) ! Update display to remove the deleted records
  ud.Debug(' <btnDeleteThis ' & strStateCode)
Now that we have the required checks in place, "Save and Close" the Embeditor, save your work, and start the application.
Change to the "by State Code" tab, and choose South Carolina. Click on the "Delete this State" button. The first record in the Customer table has an invoice, so we get the correct notification. Same with the third customer.
In the end, we deleted the empty customers, and two SC customers remain. We would have to delete their orders before we should delete these customers.

Essential Reading

I learnt most of what is covered in these articles from "Section 2: Accessing Data Tables" from Bruce Johnson's book, "Programming in Clarion's ABC". You really need to invest $49 in this book. I was also assisted by some very helpful people at ClarionHub, who kindly guided me through my mistakes and misunderstandings.




Posted by Donn Edwards at 8:57 PM
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)