Query parser

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.