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?
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.
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 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.
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.
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().
@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)
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.
