Query parser - Forum - OpenEdge Development - Progress Community
 Forum

Query parser

This question is answered

For years, we have had the possibility to manage queries dynamically at runtime. In order to do that, we wrote code for retrieving the current structure of the query (used buffers, where clauses, by clauses etc.) by parsing the query string on-the-fly. 

Our parsing logic has been working just fine, but there are some query structures that are not supported. And I was wondering if there would nowadays be an easier way to handle this. 

Here is sample code to show what I was hoping to find:

def var oQuery as Query.

oQuery = new Query("for each customer no-lock where customer.name begins 'a', each order of customer no-lock").

display oQuery:NumBuffers            // 2
        oQuery:Buffer(2):Name        // "order"
        oQuery:WhereExpression(1).   // "customer.name begins 'a'"

Note that we can't use query object for this because you have to know and set the buffers (hQuery:set-buffers) before using query-prepare. In our use case, we do not always know the buffers in advance so they must be parsed from the query string.

Any ideas?

Verified Answer
  • I have a query library that does something similar, albeit not with classes. What I do to retrieve the tables used is this:

    FUNCTION openQuery RETURNS HANDLE
      ( INPUT pcQuery      AS CHARACTER
      , INPUT pcBufferList AS CHARACTER
      ):
    
      /* Name: openQuery
       * Desc: Create and open a dynamic query with all its buffers.
       *
       * You can define alternative names for a buffer:
       *
       *       hQuery = openQuery( 'FOR EACH thingy, EACH order OF thingy'
       *                         , 'thingy=customer, order'
       *                         ).
       *
       * If the 2nd parameter is optional if you use standard buffer names.
       * You can also provide buffer handles as follows:
       *
       *       hQuery = openQuery( 'FOR EACH ttCustomer'
       *                         , SUBSTITUTE('ttCustomer=&1', BUFFER ttCustomer:handle'
       *                         ).
       *
       * If you want to use temp-tables, you need to provide buffer handles
       */
      DEFINE VARIABLE cBuffer    AS CHARACTER   NO-UNDO.
      DEFINE VARIABLE cEntry     AS CHARACTER   NO-UNDO.
      DEFINE VARIABLE cErrorList AS CHARACTER   NO-UNDO.
      DEFINE VARIABLE cNextWord  AS CHARACTER   NO-UNDO.
      DEFINE VARIABLE cQuery     AS CHARACTER   NO-UNDO.
      DEFINE VARIABLE cTable     AS CHARACTER   NO-UNDO.
      DEFINE VARIABLE cWord      AS CHARACTER   NO-UNDO.
      DEFINE VARIABLE hBuffer    AS HANDLE      NO-UNDO.
      DEFINE VARIABLE hQuery     AS HANDLE      NO-UNDO.
      DEFINE VARIABLE iBuffer    AS INTEGER     NO-UNDO.
      DEFINE VARIABLE iError     AS INTEGER     NO-UNDO.
      DEFINE VARIABLE iWord      AS INTEGER     NO-UNDO.
      DEFINE VARIABLE lPrepared  AS LOGICAL     NO-UNDO.
    
      /* Create a list of buffers */
      IF pcBufferList = '' THEN
      DO:
        cQuery = pcQuery.
        cQuery = REPLACE(cQuery,',',' '). /* To separate all words */
    
        /* Remove double spaces */
        REPEAT WHILE INDEX(cQuery,"  ") > 0:
          cQuery = REPLACE(cQuery,'  ', ' ').
        END.
    
        /* Walk thru the query to collect table names */
        DO iWord = 1 TO NUM-ENTRIES(cQuery,' ') - 1:
          cWord     = ENTRY(iWord,cQuery,' ').
          cNextWord = ENTRY(iWord + 1,cQuery,' ').
    
          IF LOOKUP(cWord,'EACH,FIRST,LAST') > 0 AND LOOKUP(cNextWord,pcBufferList) = 0 THEN
            pcBufferList = TRIM(pcBufferList + ',' + cNextWord,',').
        END.
    
        PUBLISH 'debugMessage' (1, SUBSTITUTE('Automatically collected buffers: &1', pcBufferList)).
      END.
    
      CREATE QUERY hQuery.
      DO iBuffer = 1 TO NUM-ENTRIES(pcBufferList):
    
        cEntry  = TRIM(ENTRY(iBuffer, pcBufferList)).
        cBuffer = ENTRY( 1, cEntry ,'=').
        cTable  = ENTRY(NUM-ENTRIES(cEntry,'='), cEntry,'=').
    
        /* Remove database reference from buffer name */
        cBuffer = ENTRY(NUM-ENTRIES(cBuffer,'.'), cBuffer,'.').
    
        /* Try to interpret the buffer name as a handle */
        hBuffer = ?.
        hBuffer = WIDGET-HANDLE(cTable) NO-ERROR.
    
        /* If that works, then we received a buffer handle */
        IF VALID-HANDLE(hBuffer) THEN
        DO:
          /* Then don't do anything, but use the buffer provided */
          PUBLISH 'debugMessage' (1, SUBSTITUTE('Use buffer "&1" for table "&2"', cTable, cBuffer )).
        END.
    
        ELSE
        DO:
          /* Just a name, so create a buffer for this table */
          CREATE BUFFER hBuffer FOR TABLE cTable BUFFER-NAME cBuffer NO-ERROR.
          PUBLISH 'debugMessage' (1, SUBSTITUTE('Create buffer for table "&1"', cTable)).
        END.
    
        IF ERROR-STATUS:ERROR THEN RETURN ?.
        hQuery:ADD-BUFFER(hBuffer).
      END.
    
      lPrepared = hQuery:QUERY-PREPARE(pcQuery) NO-ERROR.
      IF lPrepared THEN
        hQuery:QUERY-OPEN.
      ELSE
      DO:
        /* Collect errors */
        DO iError = 1 TO ERROR-STATUS:NUM-MESSAGES:
          cErrorList = cErrorList + CHR(1) + ERROR-STATUS:GET-MESSAGE(iError).
        END.
        cErrorList = TRIM(cErrorList,CHR(1)).
    
        PUBLISH 'debugMessage' (2, 'Errors opening query:' ).
        DO iError = 1 TO NUM-ENTRIES(cErrorList,CHR(1)):
          PUBLISH 'debugMessage' (2, ENTRY(iError,cErrorList,CHR(1)) ).
        END.
    
        hQuery = ?.
      END.
    
      RETURN hQuery.
    END FUNCTION. /* openQuery */
    
    DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
    hQuery = openQuery('for each customer no-lock','').
    
    REPEAT WHILE hQuery:GET-NEXT():
      DISPLAY 
        hQuery:GET-BUFFER-HANDLE(1)::cust-num 
        hQuery:GET-BUFFER-HANDLE(1)::NAME FORMAT 'x(30)'.
    END.
    
  • We've walked this path before but are stepping away and are now taking another approach.

    Instead of parsing a query string we build a query object, which is basically a tree of buffers (joins) with conditions, fields phrases, use-index phrases etc.

    This object can way more precise be handled (e.g. adding/changing a condition at a specific place in the tree) than strings.

    Only when we actually want to execute the query we call ToString() method or use a QueryStringBuilder and pass the resulting string to the QUERY-PREPARE() method of a query handle.

    In no way will we parse or alter that string again; all manipulations go through the object.

All Replies
  • I have a query library that does something similar, albeit not with classes. What I do to retrieve the tables used is this:

    FUNCTION openQuery RETURNS HANDLE
      ( INPUT pcQuery      AS CHARACTER
      , INPUT pcBufferList AS CHARACTER
      ):
    
      /* Name: openQuery
       * Desc: Create and open a dynamic query with all its buffers.
       *
       * You can define alternative names for a buffer:
       *
       *       hQuery = openQuery( 'FOR EACH thingy, EACH order OF thingy'
       *                         , 'thingy=customer, order'
       *                         ).
       *
       * If the 2nd parameter is optional if you use standard buffer names.
       * You can also provide buffer handles as follows:
       *
       *       hQuery = openQuery( 'FOR EACH ttCustomer'
       *                         , SUBSTITUTE('ttCustomer=&1', BUFFER ttCustomer:handle'
       *                         ).
       *
       * If you want to use temp-tables, you need to provide buffer handles
       */
      DEFINE VARIABLE cBuffer    AS CHARACTER   NO-UNDO.
      DEFINE VARIABLE cEntry     AS CHARACTER   NO-UNDO.
      DEFINE VARIABLE cErrorList AS CHARACTER   NO-UNDO.
      DEFINE VARIABLE cNextWord  AS CHARACTER   NO-UNDO.
      DEFINE VARIABLE cQuery     AS CHARACTER   NO-UNDO.
      DEFINE VARIABLE cTable     AS CHARACTER   NO-UNDO.
      DEFINE VARIABLE cWord      AS CHARACTER   NO-UNDO.
      DEFINE VARIABLE hBuffer    AS HANDLE      NO-UNDO.
      DEFINE VARIABLE hQuery     AS HANDLE      NO-UNDO.
      DEFINE VARIABLE iBuffer    AS INTEGER     NO-UNDO.
      DEFINE VARIABLE iError     AS INTEGER     NO-UNDO.
      DEFINE VARIABLE iWord      AS INTEGER     NO-UNDO.
      DEFINE VARIABLE lPrepared  AS LOGICAL     NO-UNDO.
    
      /* Create a list of buffers */
      IF pcBufferList = '' THEN
      DO:
        cQuery = pcQuery.
        cQuery = REPLACE(cQuery,',',' '). /* To separate all words */
    
        /* Remove double spaces */
        REPEAT WHILE INDEX(cQuery,"  ") > 0:
          cQuery = REPLACE(cQuery,'  ', ' ').
        END.
    
        /* Walk thru the query to collect table names */
        DO iWord = 1 TO NUM-ENTRIES(cQuery,' ') - 1:
          cWord     = ENTRY(iWord,cQuery,' ').
          cNextWord = ENTRY(iWord + 1,cQuery,' ').
    
          IF LOOKUP(cWord,'EACH,FIRST,LAST') > 0 AND LOOKUP(cNextWord,pcBufferList) = 0 THEN
            pcBufferList = TRIM(pcBufferList + ',' + cNextWord,',').
        END.
    
        PUBLISH 'debugMessage' (1, SUBSTITUTE('Automatically collected buffers: &1', pcBufferList)).
      END.
    
      CREATE QUERY hQuery.
      DO iBuffer = 1 TO NUM-ENTRIES(pcBufferList):
    
        cEntry  = TRIM(ENTRY(iBuffer, pcBufferList)).
        cBuffer = ENTRY( 1, cEntry ,'=').
        cTable  = ENTRY(NUM-ENTRIES(cEntry,'='), cEntry,'=').
    
        /* Remove database reference from buffer name */
        cBuffer = ENTRY(NUM-ENTRIES(cBuffer,'.'), cBuffer,'.').
    
        /* Try to interpret the buffer name as a handle */
        hBuffer = ?.
        hBuffer = WIDGET-HANDLE(cTable) NO-ERROR.
    
        /* If that works, then we received a buffer handle */
        IF VALID-HANDLE(hBuffer) THEN
        DO:
          /* Then don't do anything, but use the buffer provided */
          PUBLISH 'debugMessage' (1, SUBSTITUTE('Use buffer "&1" for table "&2"', cTable, cBuffer )).
        END.
    
        ELSE
        DO:
          /* Just a name, so create a buffer for this table */
          CREATE BUFFER hBuffer FOR TABLE cTable BUFFER-NAME cBuffer NO-ERROR.
          PUBLISH 'debugMessage' (1, SUBSTITUTE('Create buffer for table "&1"', cTable)).
        END.
    
        IF ERROR-STATUS:ERROR THEN RETURN ?.
        hQuery:ADD-BUFFER(hBuffer).
      END.
    
      lPrepared = hQuery:QUERY-PREPARE(pcQuery) NO-ERROR.
      IF lPrepared THEN
        hQuery:QUERY-OPEN.
      ELSE
      DO:
        /* Collect errors */
        DO iError = 1 TO ERROR-STATUS:NUM-MESSAGES:
          cErrorList = cErrorList + CHR(1) + ERROR-STATUS:GET-MESSAGE(iError).
        END.
        cErrorList = TRIM(cErrorList,CHR(1)).
    
        PUBLISH 'debugMessage' (2, 'Errors opening query:' ).
        DO iError = 1 TO NUM-ENTRIES(cErrorList,CHR(1)):
          PUBLISH 'debugMessage' (2, ENTRY(iError,cErrorList,CHR(1)) ).
        END.
    
        hQuery = ?.
      END.
    
      RETURN hQuery.
    END FUNCTION. /* openQuery */
    
    DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
    hQuery = openQuery('for each customer no-lock','').
    
    REPEAT WHILE hQuery:GET-NEXT():
      DISPLAY 
        hQuery:GET-BUFFER-HANDLE(1)::cust-num 
        hQuery:GET-BUFFER-HANDLE(1)::NAME FORMAT 'x(30)'.
    END.
    
  • Thanks, Patrick, this is helpful. One of those features that would be nice to have in OpenEdge out-of-the-box instead of everyone writing their own logic...

  • We've walked this path before but are stepping away and are now taking another approach.

    Instead of parsing a query string we build a query object, which is basically a tree of buffers (joins) with conditions, fields phrases, use-index phrases etc.

    This object can way more precise be handled (e.g. adding/changing a condition at a specific place in the tree) than strings.

    Only when we actually want to execute the query we call ToString() method or use a QueryStringBuilder and pass the resulting string to the QUERY-PREPARE() method of a query handle.

    In no way will we parse or alter that string again; all manipulations go through the object.

  • That sounds like a good approach, we could use this idea for new code. Thanks.