Adding and Updating Records using code

I have a table called “sgCategories” that has the following structure:

                FILE,DRIVER('TOPSPEED')

SCA:SGCATEGORYPK KEY(+CompanyId,+id),NOCASE,PRIMARY
Record RECORD
SCA:CompanyId LONG
SCA:Categoryid LONG
SCA:Description STRING(100)
SCA:DEFAULTYN BYTE
SCA:DATEMODIFIED DATE
SCA:TIMEMODIFIED TIME
SCA:DATECREATED DATE
SCA:TIMECREATED TIME
END
END

I want to import data from a JSON file, which I put in a queue. I have left that part out of the this code, for simplicity. Instead, I am just generating a random number of records and inserting the record if not found, or updating it if found.

Access:sgCategory.Init()
Access:sgCategory.Open()                                ! Open the table
Access:sgCategory.UseFile()                             ! Use the table
Access:sgCategory.ClearKey(sca:sgCategoryPK)            ! Assure all Key fields have lowest value
! sca:sgCategoryPK = sca:CompanyID + sca:CategoryID
SET(sca:sgCategoryPK,sca:sgCategoryPK)                  ! Choose the PK index
sca:CompanyID = glo:coID                                ! Global value for current company
LOGOUT(1,sgCategory)                                    ! Speed up transactions?
LOOP i = 1 TO RANDOM(50,250)   ! Test values loop
    sca:CategoryID = i
    if Access:sgCategory.Fetch(sca:sgCategoryPK) = Level:Benign then ! Found
        !// Update the date modified, leave the other fields alone
        sca:DateModified = TODAY()  ! Todays date
        sca:TimeModified = CLOCK()  ! Now
    else ! Not found
        !// Insert a new record and update its fields where applicable
        Access:sgCategory.Insert()
        Access:sgCategory.PrimeRecord()
        sca:CompanyID = glo:coID
        sca:CategoryID = i
        sca:Description = 'Category ' & i
        !sca:DefaultYN = False           ! False by default anyway
        sca:DateModified = TODAY()  ! Todays date
        sca:TimeModified = CLOCK()  ! Now
        sca:DateCreated = TODAY()
        sca:TimeCreated = CLOCK()
    end
    Access:sgCategory.Update()                          ! Update the record
END ! Test values loop
COMMIT                                                  ! Make sure the data is all written to the table
Access:sgCategory.Close()                               ! Close the table

Is there anything I have left out or anything that I should change to improve this code? Do I need the “PrimeRecord” command or the “Commit” command, for example?

Any advice would be most welcome

Follow-up question: I want to have only one record with DefaultYN = True.

Let’s say I edit CategoryID = 33 and set it to true. How do I do

UPDATE sgCategory SET DefaultYN = 0 WHERE CategoryID <> 33 AND DefaultYN = True

I know how to do this on a SQL table, but not a TPS table, other than by looping through all the records, which isn’t terribly efficient.

You need an index on DefaulYN to quickly find the row that is marked as True. Otherwise, you have to loop through the table.

1 Like

To me that is an odd way to go about it. Doing the Insert of an empty row then doing an update.
The PrimeRecord is not technically necessary. It will set any default values you have set in the dictionary for you.
If you have dictionary validation setup, your Insert call will likely because the tables record buffer is blank when you call the Insert. Also, if there is a primary unique key on CategoryID, then I’d think the Insert would fail because or add a row with zero for the ID. Which means only one can insert can happen at a time (multi-user environment), if this logic is used.
If you have the LOGOUT, then the COMMIT is necessary. Remember with TPS, the table will be locked from other Insert/Updates during the transaction.
I’d move the Update into the IF after successful Fetch and move the Insert to the bottom of the ELSE branch.

1 Like

Thank you. Now I have:

Access:sgCategory.Init()
Access:sgCategory.Open()                                ! Open the table
Access:sgCategory.UseFile()                             ! Use the table
Access:sgCategory.ClearKey(sca:sgCategoryPK)            ! Assure all Key fields have lowest value
! sca:sgCategoryPK = sca:CompanyID + sca:CategoryID
SET(sca:sgCategoryPK,sca:sgCategoryPK)                  ! Choose the PK index
sca:CompanyID = glo:coID                                ! Global value for current company
LOGOUT(1,sgCategory)                                    ! Speed up transactions?
LOOP i = 1 TO RANDOM(50,250)   ! Test values loop
    sca:CategoryID = i
    if Access:sgCategory.Fetch(sca:sgCategoryPK) = Level:Benign then ! Found
        !// Update the date modified, leave the other fields alone
        sca:DateModified = TODAY()  ! Todays date
        sca:TimeModified = CLOCK()  ! Now
        Access:sgCategory.Update()                          ! Update the record
    else ! Not found
        !// Insert a new record and update its fields where applicable
        Access:sgCategory.PrimeRecord()
        sca:CompanyID = glo:coID
        sca:CategoryID = i
        sca:Description = 'Category ' & i
        !sca:DefaultYN = False
        sca:DateModified = TODAY()  ! Todays date
        sca:TimeModified = CLOCK()  ! Now
        sca:DateCreated = TODAY()
        sca:TimeCreated = CLOCK()
        Access:sgCategory.Insert()
    end
END ! Test values loop
COMMIT                                                  ! Make sure the data is all written to the table
Access:sgCategory.Close()                               ! Close the table

I hope I followed your recommendations correctly

2 Likes

I created an index key called sca:sgCatDefaultYNKey that just uses the DefaultYN field.

I created 3 local variables in the edit form:

loc:CompanyID LONG
loc:CategoryID long
loc:DefaultYN Byte

I set loc:DefaultYN = False when the window opens.

In the ThisWindow.Update procedure I have added:

  CODE
  ! Start of "WindowManager Method Executable Code Section"
  ! [Priority 2500]
    sca:DateModified = TODAY()  ! We have changed this record
    sca:TimeModified = CLOCK()
  ! Parent Call
  PARENT.Update
  ! [Priority 6300]
    loc:CompanyID = sca:CompanyID
    loc:CategoryID = sca:CategoryID
    loc:DefaultYN = sca:DefaultYN

and then in the ThisWindow.TakeCompleted procedure I have

  ! Parent Call
  ReturnValue = PARENT.TakeCompleted()
  ! [Priority 6300]
        if loc:DefaultYN then ! Default
            ! Unmark all other records
            Access:sgCategory.Open()                            ! Open the table
            Access:sgCategory.UseFile()                         ! Use the table    
            Access:sgCategory.ClearKey(sca:sgCatDefaultYNKey)   ! Assure all Key fields have lowest value
            sca:DefaultYN = True
            LOGOUT(1,sgCategory)                       
            SET(sca:sgCatDefaultYNKey,sca:sgCatDefaultYNKey)
            LOOP UNTIL Access:sgCategory.Next() 
                if (sca:CategoryID <> loc:CategoryID) and (sca:DefaultYN = true) then ! found
                    sca:DefaultYN = False
                    sca:DateModified = TODAY()
                    sca:TimeModified = CLOCK()
                    Access:sgCategory.Update()
                end ! found
            END ! LOOP
            COMMIT
            Access:sgCategory.Close()                               ! Close the table 
        end ! Default
    RETURN ReturnValue

This seems to work as required. Have I broken anything or put the code in the wrong place? I’m getting to learn how the FileManager stuff works.

In your TakeCompleted logic you wan to wrap that in IF ReturnValue = Level:Benign. That indicates the update/insert was successful.
If this is an update form for sgCategory, you don’t need the .Open/UseFile/Close calls (they aren’t hurting anything).

I’m not sure the Update method is what you think. That is not where the row is updated.That’s a method called related to UI like the UPDATE statement in the language. It gets called frequently before objects in the form do something and updates the object with current values from the UI (selected rows in a browse, etc.)

If you want to set the modified date time, I’d use the triggers in the data dll global embeds. If you don’t want to do that, then I’d put the code to update the modified date/time in the TakeComplete method before the parent call.

2 Likes

Donn
A slight problem might be that the .Fetch() will clear the buffer if it fails. See
http://www.clarion.help/doku.php?id=fetch_get_a_specific_record_by_key_value_.htm

And you set the company field Only Once!

1 Like

You dont need this as the autonumbered fields are not being used anywhere else before an insert() and insert() will prime the record before saving it anyway.

You dont have any error checking on some of your abc code

eg

And will your logout complete in 1 second considering the random loop?
LOOP i = 1 TO RANDOM(50,250)

I use

Logout(Seconds,File)
IF ErrorCode()
   !Handle the slowness
Else
    LogoutTime = Clock()
End

Set(Key,Key)
Loop Until Access:File.Next() <> Level:Benign
   !Some Processing
    IF Clock() => LogoutTime + (Seconds - 5 seconds) !Doesnt work past midnight, you need to handle that yourself.
         Commit
         IF ErrorCode()
              !Handle the commit error
         End
         Logout(Seconds,File)
          IF ErrorCode()
              !HAndle the logout error
          Else
               LogoutTime = Clock()
           End
     End
End
Commit
IF ErrorCode()
!Handle the commit error
End

Obviously the way people code is an opinion though.

1 Like

I appreciate all the comments, and will take appropriate action to fix the code.

I put all the Open/UseFile/Close statements because I am modifying records other than the one open in the edit form, so I waited until the last possible minute.

I will re-read the .Update instructions in the manual. I’m learning a great deal. Thanks!

A long time ago Russ Egen did a lesson possibly his Understanding Classes in Hants,UK which @Mark_Sarson organised or an article possibly in Clarion mag on how to override the ABC error messages and throw your own more intuitive error message. If you can track that down, you can use the ABC error class to provide more feedback to users. I think from memory it involved overriding the messages in ABError.TRN file. You can see from code posted by others, its not something used very often, as most code posted still uses Message().

fwiw.

1 Like

Hi Donn

The training Richard remembers was based on Russ’s book, Programming Objects in Clarion.

Since Russ’s passing, it has been kindly open sourced, and the repository with PDF can be found here.

2 Likes

@Mark_Sarson @anon23294430 Thanks, I will take a look. Bruce’s ABC book also talks about error handling, and I will definitely improve my code with error checking and reporting. I just want to get the basic commands right first.

The Update method changes (rewrites) the current record. The Update method handles any errors that occur while changing the record.
The TryUpdate method provides a slightly different (manual) alternative for changing records.

From the help: ABC Library Reference > FileManager > FileManager Methods > Update
Not to be confused with UPDATE (write from screen to USE variables)

I think there’s stuff he he wrote about that on clarionmag.

1 Like

Where do I find these? Not heard of this before

Yes, that is correct. But the code you posted is in the ThisWindowManager.Update method, not a file manager method.

In the Global Embeds of your “data” DLL for a multi-DLL solution or in the Global Embeds of your EXE for a single EXE solution, open the Global Objects -> ABC Objects -> File Managers branch. You will see all of the tables in your dictionary’s file manager objects listed.
You’ll see methods PreInsert, PreUpdate, PreDelete and their Post… equivalent. Any code you put in the Pre methods will execute before a row is inserted, updated, etc. This ensures the code executes no matter where in your code you issue the command.
You can also put code in the dictionary that will generate into these methods, but I’m not a fan of that because the editor for the trigger code in the dictionary isn’t that nice.

1 Like

To be clear, this can be achieved in the trigger for tables in your dictionary. Probably easier for remembering:

A NOTE: For SQL folks, these are client-side triggers executed from the program, and not triggers executed on the back end.

Yeah, it is available in the dictionary.
I really don’t like the editor in the dictionary. It isn’t full features, no code completion, etc.

A tip for that issue is using

INCLUDE('Triggers.clw','SectionName'),ONCE 

Put this in the DCT, then you can edit Triggers.clw in your favorite editor of choice.

I find this easier than the embeds as it separates out the triggers from the APP and the DCT.

1 Like