openedgeprogress-4gldynamicquerydynamic-queries

How to replace static "query" way of working by dynamic "queries"?


In this other question, I asked how to handle the situation where a table might be defined or not. The proposed solution I'm trying to follow, is working with dynamic "queries" instead of static ones. Unfortunately I have no idea how to do this, can anybody help me with this? Let me give you an example:

Static way of working:

FIND Table1 WHERE Table1.field1          = 123
                AND Table1.field2        = Current-Table.field2
                AND UPPER(Table1.field3) = "Constant-string"
                AND Table1.field4        = temp-table.field4 NO-ERROR.
IF NOT AVAILABLE Table1
  THEN DO:
           CREATE Table1.
           ASSIGN Table1.field1 = 123
                  Table1.field2 = Current-Table.field2
                  Table1.field3 = "Constant-string"
                  Table1.field4 = temp-table.field4.
           RELEASE Table1.
       END.

Dynamic way of working:

CREATE BUFFER h-Table1 FOR TABLE "Table1" NO-ERROR.
IF VALID-HANDLE(h-Table1)
THEN DO:
         L-Found = h-Table1:FIND-FIRST("WHERE Table1.field1 = " + STRING(123) + 
                                         "AND Table1.field2   = " + STRING(Current-Table.field2) +
                                         "AND UPPER(Table1.field3) = 'Constant-string'" + 
                                         "AND Table1.field4 = " + temp-table.field4) NO-ERROR.
         IF NOT L-Found
         THEN DO:
                  h-Table1:BUFFER-CREATE("").
              END.
         ELSE MESSAGE "FOUND".
     END.

Is it BUFFER-CREATE or some other method, how should I fill in the parameters (like ASSIGN Table1.Field1 = 123), ...?


Solution

  • Use:

    def var hb as handle no-undo.
    
    create buffer hb for table "mytable".
    hb:buffer-create().
    assign
       hb::myfield = 123
       hb::another = "ok"
       .
    hb:buffer-release()
    
    finally:
       delete object hb no-error.
    end finally.
    

    Note that hb::myfield is shorthand for

    hb:buffer-field('myfield'):buffer-value
    

    And also beware that when using dynamic objects, you are responsible for garbage collection. If you create it, you need to delete it.

    Dynamic find-unique part:

    def var hb     as handle  no-undo.
    def var lfound as logical no-undo.
    
    create buffer hb for table 'Table1'.
    
    lfound = hb:find-uniqe(
                'where field1 = 123'
                +  ' and field2 = ' + quoter( current-table.field2 ) )
                +  ' and upper( field3 ) = "constant-string"'
                +  ' and field4 = ' + quoter( temp-table.field4 ) 
             ) no-error.
    

    or using a substitute:

    lfound = hb:find-unique(
                substitute(
                   'where field1 = &1':u
                   +  ' and field2 = &2':u
                   +  ' and upper( field3 ) = &3':u
                   +  ' and field4 = &4':u,
                   123,
                   quoter( current-table.field2 ),
                   quoter( 'constant-string' ),
                   quoter( temp-table.field4 )
                )
             ) no-error.
    
    

    You will soon want to create a query builder class / function to handle creating queries and possibly also handling run-time validation which you are now also responsible for.