In one of my programs I was looking for the table a certain list of fields belonged to.
To translate this to the sports db, imagine this list of fields: "order-num,promise-date". There is only one table in the sports db that contains both fields and that is 'order'. Some fields might occur in more than one table, but in our database this is a bit easier since all field names are unique (Mfg/Pro database). Since there can be invalid entries in the list of fields, I try to ignore those and I set the table to the first one that has 2 hits from the list.
When I try to solve this dynamically (I have more than 1 database) I run into the problem that a secondary dynamic buffer is not released on consecutive iterations (both v10 and v11). So on iteration 2 the program tries to find the field 'hello-world', which obviously does not exist. But the _file buffer that was found in the previous iteration is still available.
Below is a reproduction on the sports db. My question is: is this expected / desired behaviour? At least it is not intuitive.
DEFINE VARIABLE cWhere AS CHARACTER NO-UNDO.
DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE hField AS HANDLE NO-UNDO.
DEFINE VARIABLE hFile AS HANDLE NO-UNDO.
DEFINE VARIABLE cFields AS CHARACTER NO-UNDO INITIAL 'promise-date,hello-world'.
DEFINE VARIABLE i AS INTEGER NO-UNDO.
CREATE BUFFER hFile FOR TABLE "_File".
CREATE BUFFER hField FOR TABLE "_Field".
DEFINE VARIABLE cField AS CHARACTER NO-UNDO.
CREATE QUERY hQuery.
hQuery:SET-BUFFERS(hField,hFile).
/* Dynamic solution */
DO i = 1 TO NUM-ENTRIES(cFields):
cWhere = SUBSTITUTE( "FOR EACH _field WHERE _field._field-name = '&1', FIRST _file OF _field", ENTRY(i,cFields)).
hQuery:QUERY-PREPARE(cWhere).
hQuery:QUERY-OPEN().
hQuery:GET-FIRST(NO-LOCK).
IF hFile:AVAILABLE THEN
MESSAGE 'Dynamic Query' SKIP(1)
i ENTRY(i,cFields) '/' hFile::_file-name
VIEW-AS ALERT-BOX INFO BUTTONS OK.
hQuery:QUERY-CLOSE().
END.
/* Static solution */
DO i = 1 TO NUM-ENTRIES(cFields):
FOR EACH _field WHERE _field._field-name = ENTRY(i,cFields)
, FIRST _file OF _field:
MESSAGE 'Static Query' SKIP(1)
i ENTRY(i,cFields) '/' _file._file-name
VIEW-AS ALERT-BOX INFO BUTTONS OK.
END.
END.
They'll probably say it's the expected behaviour. But hQuery:QUERY-OFF-END is correct. Also hQuery:GET-FIRST(...) returns NO on second iteration. I don't remember I ever checked for <buffer>:AVAILABLE in dynamic queries.
Could be that I am not doing it the "right" way, checking on availability of a buffer is - I admit - a bit old school. Better / more modern might be to check on the return value of the GET-FIRST method as you suggest.
Hi Patrick,
I tend to just use GET-NEXT. This will return false is there are no records. Perhaps you could just reference the return value of this.
do while hQuery:Get-Next():
/* Logic Here */
end.Yeah, I largely simplified this example. I solved it by replacing the :available construction with "if get-first", which is shorter, and - on second thought - a better construction.
In my own example I am testing on side-effect of my action, whereas I should be testing on the direct feedback of the action (in my case the get-first) itself.
I’ve almost stoped querying system tables and favour create buffer/buffer-field instead. Not knowing the exact requirements you can easily loop through all tables (yeah, using _file system table) and then just create a buffer for that table and go through all fields list and see if (or how many) are there then decide if you have a winner or need to go all the way through…