I have a TPS table where the last two fields are
selected_user CSTRING(9)
selected_yn BYTE
The user can click on a checkbox to set selected_yn to true, and the program notes which user did that in the selected_user field. Let’s assume the user has selected 50 records on the screen, out of a total of 100,000 records. (Too many to “untick” manually)
What I want to do is the equivalent of the SQL statement
UPDATE mytable SET selected_yn = 0, selected_user = ‘’ WHERE selected_yn=1 AND selected_user = ‘Donn’
I have tried various approaches, including a VIEW with a FILTER, and FileManager’s Access commands, without any success. I’m obviously missing something fundamental.
The Chapter 5 “Data File Processing” part of Clarion Language Programming seems to ignore this topic altogether. I really would prefer not to have to loop through 100,000 records if I don’t have to. Right now I can’t even get that to work properly.
I’m still a newbie so I will appreciate any suggestions, or even pointers to a Clarion example that does this.
Welcome to ISAM files. An Index on selected_user would help with performance, but there is no way to update multiple rows with a single statement. You will have a LOOP.
If you do have an index on selected_user then you can use either a VIEW() with a prop:filter or SET(key,key).
Put OPT on the KEY so it does not include records with selected_yn=0
In a multiuser system it would be common to allow multiple users to be tagging records. To do that you will need the tag fields in a separate tag file:
MyTableTagFile FILE
TagKey KEY( User, MyTableID )
RECORD
User CSTRING(9)
MyTableID LONG !ID that is Primary Key from MyTable
END
selected_user = ‘Donn’
SET(SelectUserKey,SelectUserkey)
LOOP Until Access:MyFile.Next()
IF NOT (selected_user = ‘Donn’) then Break.
IF selected_yn = 1
update stuff
end
end
You might want to wrap it all in a logout/commit for speed.
and clear the selected_user when no longer required.
so when processing:
LOOP
xxx:selected_user = ‘Donn’
set(xxx:SelectedUserKey,xxx:SelectedUserKey)
next(xxxFile)
if errorcode() or xxx:selected_user <> ‘Donn’ then break.
!--- <some processing goes here>
clear(xxx:selected_user)
put(xxxFile)
if errorcode() then <error handling but no messages!>.
END
I’ve put the set() inside the loop as I am clearing the selected user once the processing has been done.
As Sean mentioned, to speed it up, put a logout() before the LOOP and a commit after it. This should make it very quick but make sure there are no messages within the loop as other users are locked out of making changes while the transaction takes place.
Thanks everyone for the input. My final code looks like this
csUser = CLIP(p_web.GSV('UserName')) ! get the user name
Access:a3_1level.Open() ! Open the table
Access:a3_1level.UseFile() ! Use the table
AL1:selected_user = CLIP(csUser) ! **(1)** This is the value we are looking for
SET(AL1:al1_sel,AL1:al1_sel) ! Choose the al1_sel index
LOOP UNTIL Access:a3_1level.Next() ! Loop through all user records
IF NOT (AL1:selected_user = csUser) then BREAK. ! All done
if AL1:selected_yn <> 0 ! Row is selected
!// Update stuff
AL1:selected_user = '' ! **(2)** Set the new value
AL1:selected_yn = 0 ! Set the new value
Access:a3_1level.TryUpdate() ! Update the record
end ! Row is selected
END
Access:a3_1level.Close() ! Close the table
But I still have a question: There are two lines labelled (1) and (2)
AFAIK line (2) sets the value of the field in the current record to ‘’
Why doesn’t line (1) change the field value too?
Donn, I’m not sure if this is still a problem, but be aware that with ISAM files when you update the record and you update a field that is a part of the index you are looping through, the possibility exists of that update changing your position in the flow through the loop.
ie if you’re updating lines 1 to 5 and change 1 to 6 you may suddenly find yourself at the end and break out, even though you haven’t touched 2 to 5 yet. You are updating physical files. Not like SQL and isolation.
To answer your question about the lines (1) and (2).
Line (1) only sets the location in the file where you start looping through.
This way you don’t need to loop through the entire file, it breaks out when csUser isn’t found anymore.
The Access:a3_1level.Next() really reads the record and gives you the possibility to Update the record.
You indeed have to be carefull when updating a field that’s part of the key you use as seanh said before.
(1) and (2) both update the Record in Memory. Until you do a PUT via TryUpdate the database (disk file) is not changed to the Memory Record buffer values.
It may help you to read the Clarion Help on FILE and RECORD.
I’d suggest at (3) you always add a CLEAR(Pre:Record,-1) to assure all fields defined in the Key (but not assigned a value in your code) are at their lowest value otherwise you could have stale data left in the record from prior file use and the SET will not begin where desired.
If a Key field is Descending KEY(-Pre:xxx) you must set that one field to the highest possible value with CLEAR(Pre:Field, 1). If all Key fields are Descending you could Clear(Record,1).
csUser = CLIP(p_web.GSV('UserName')) ! get the user name
Access:a3_1level.Open() ! Open the table
Access:a3_1level.UseFile() ! Use the table
CLEAR(AL1:Record, -1) !(3) <--- Assure all Key fields have lowest value
AL1:selected_user = CLIP(csUser) ! **(1)** This is the value we are looking for
SET(AL1:al1_sel,AL1:al1_sel) ! Choose the al1_sel index
LOOP UNTIL Access:a3_1level.Next() ! Loop through all user records
csUser = CLIP(p_web.GSV('UserName')) ! get the user name
Access:a3_1level.Open() ! Open the table
Access:a3_1level.UseFile() ! Use the table
Access:a3_1level.ClearKey(AL1:al1_sel) !(3) <--- Assure all Key fields have lowest value
AL1:selected_user = CLIP(csUser) ! **(1)** This is the value we are looking for
SET(AL1:al1_sel,AL1:al1_sel) ! Choose the al1_sel index
LOOP UNTIL Access:a3_1level.Next() ! Loop through all user records