Buffers not released in dynamic query - Forum - OpenEdge Development - Progress Community

Buffers not released in dynamic query

 Forum

Buffers not released in dynamic query

This question is not answered

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.
All Replies
  • 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.
  • There is a subtlety with dynamic buffer creation.
     
    In the static world, we say
    DEFINE BUFFER myField FOR _Field.
     
    And  we get a named, distinct buffer.
     
    IN the dynamic world,
    CREATE BUFFER hBuffer FOR _Field.
     
    Gets us the DEFAULT buffer (ie not a named buffer). To do that you must say
    CREATE BUFFER hBuffer FOR _Field BUFFER-NAME “firstBuffer”.
     
    You’re also not specifying the (logical) db name for the _Field and _File buffers. Might that be something (or is it just from the simplification).
     
    You should also be deleting the dynamic buffers (a finally block is a good place to do so).
  • 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… 

    But then again, you should load the database model in a ERD diagram and then get all sort of useful info like that ;)

    Marian Edu

    Acorn IT 
    +40 740 036 212

  • That one I liked :-) thanks!!

    Sendt fra min iPad

    Den 6. okt. 2017 kl. 13.51 skrev Kim Ward <bounce-wardy484@community.progress.com>:

    Update from Progress Community
    Kim Ward

    Hi Patrick,

    I tend to just use GET-NEXT.

    do while hQuery:Get-Next():
        /* Logic Here  */
    end.

    View online

     

    You received this notification because you subscribed to the forum.  To unsubscribe from only this thread, go here.

    Flag this post as spam/abuse.