Applicable Versions: 3.02.0156 and above


DataPA OpenAnalytics provides a hook on the server that allows you to intercept and modify the query statement before any freeform query  is executed by the AppServer. This allows the administrator to add code to the server to log, modify or override any freeform query statement before it is executed on a particular AppServer. 


To log, modify or overwrite the query statement you must complete the following steps, each of which is described in more detail below;

  1. Add a new internal procedure called PAManageQuery to an existing or new Progress procedure file (.p) that logs, modifies or overrides the query string.
  2. Ensure the Progress procedure file that contains the PAManageQuery internal procedure is added to the super procedure stack of the AppServer.


Creating a PAManageQuery internal procedure


In an existing or new Progress procedure file (.p), define a new internal procedure called PAManageQuery with the following signature;


PROCEDURE PAManageQuery:
  DEFINE INPUT-OUTPUT PARAMETER ipcString AS CHARACTER NO-UNDO.


END PROCEDURE.


Once your progress procedure file is added to the super procedure stack of the AppServer (see below), this procedure will be each time BEFORE a freeform query is run, passing in the query string the query will use. If you make any changes to the query string in this procedure, these changes will be reflected in the query that is executed. 


To check your procedure is being called, and see what is being passed, you could begin by adding the following procedure;


PROCEDURE PAManageQuery:
  DEFINE INPUT-OUTPUT PARAMETER ipcString AS CHARACTER NO-UNDO.
  MESSAGE ""PAManageQuery: "" + ipcString.
END PROCEDURE.


This will result in an entry in the AppServer server log file similar to that below each time you run a freeform query;


[13/02/22@10:05:38.219+0000] P-007292 T-007296 1 AS -- (Procedure: 'PAManageQuery C:\Temp\BL\OrderFunctions.p' Line:418) PAManageQuery: FOR EACH sports2000.Customer,  EACH  sports2000.Order WHERE sports2000.Order.CustNum = sports2000.Customer.CustNum,  EACH sports2000.OrderLine OF sports2000.Order,  EACH sports2000.Item OUTER-JOIN OF sports2000.OrderLine,  EACH  sports2000.Salesrep WHERE sports2000.Salesrep.SalesRep = sports2000.Customer.SalesRep 


Creating a generic procedure to add a condition for each table in any freeform query can be complex, as it requires code to intelligently parse the query string. The following function and procedure can be very useful in achieving this, but should you need further assistance, please feel free to raise a support ticket by mailing support@datapa.com.


FUNCTION PATableList RETURNS CHARACTER
  ( INPUT ipcstring AS CHARACTER ) :
  /*------------------------------------------------------------------------------
  Purpose: Builds a list of tables from a query string
  Notes:
  ------------------------------------------------------------------------------*/
  DEFINE VARIABLE cPrevious AS CHARACTER NO-UNDO.
  DEFINE VARIABLE iCount AS INTEGER NO-UNDO.
  DEFINE VARIABLE cDelimiter AS CHARACTER NO-UNDO.
  DEFINE VARIABLE cTableList AS CHARACTER NO-UNDO.
  ASSIGN cDelimiter = CHR(1).
  RUN PATokeniseQuery (INPUT-OUTPUT ipcstring, INPUT cDelimiter).
  /* Build list of tables names in query string */
  cTableList = REPLACE(TRIM(ENTRY(3,ipcString,cDelimiter)),"","","" "") NO-ERROR.
  IF ERROR-STATUS:ERROR THEN RETURN ERROR.

  DO iCount = 3 TO NUM-ENTRIES(ipcString, cDelimiter):
    IF LENGTH(cPrevious) > 0 AND
    SUBSTRING(cPrevious,LENGTH(cPrevious),1) = "","" AND
    (ENTRY(iCount,ipcString,cDelimiter) = ""EACH"" OR
    ENTRY(iCount,ipcString,cDelimiter) = ""FIRST"" OR
    ENTRY(iCount,ipcString,cDelimiter) = ""LAST"" OR
    ENTRY(iCount,ipcString,cDelimiter) = ""PREV"" OR
    ENTRY(iCount,ipcString,cDelimiter) = ""NEXT"")
    THEN IF iCount + 1 <= NUM-ENTRIES(ipcString,cDelimiter) THEN DO:
      cTableList = cTableList + cDelimiter + ENTRY(iCount + 1,ipcString,cDelimiter) NO-ERROR.
      IF ERROR-STATUS:ERROR THEN RETURN ERROR.
    END.
    cPrevious = ENTRY(iCount, ipcString, cDelimiter) NO-ERROR.
    IF ERROR-STATUS:ERROR THEN RETURN ERROR.
  END.
  RETURN cTableList. /* Function return value. */
END FUNCTION.


PROCEDURE PATokeniseQuery :
  /*------------------------------------------------------------------------------
  Purpose: Splits the query string into tokens, taking into account any strings
  Parameters: <none>
  Notes:
  ------------------------------------------------------------------------------*/
  DEFINE INPUT-OUTPUT PARAMETER iopcString AS CHARACTER NO-UNDO.
  DEFINE INPUT PARAMETER ipcDelimiter AS CHARACTER NO-UNDO.
  DEFINE VARIABLE cTemp AS CHARACTER NO-UNDO.
  DEFINE VARIABLE cchr AS CHARACTER NO-UNDO.
  DEFINE VARIABLE cChar AS CHARACTER NO-UNDO.
  DEFINE VARIABLE lLastDelim AS LOGICAL NO-UNDO.
  DEFINE VARIABLE iCnt AS INTEGER NO-UNDO.
  DEFINE VARIABLE lInQuote AS LOGICAL NO-UNDO.
  DEFINE VARIABLE lInQuote1 AS LOGICAL NO-UNDO.
  DEFINE VARIABLE lLiteral AS LOGICAL NO-UNDO.
  cTemp = """".
  lInQuote = FALSE.

  DO iCnt = 1 TO LENGTH(iopcstring):
    cChar = SUBSTRING(iopcstring,icnt,1).
    IF cChar = CHR(34) THEN DO:
      IF NOT lInQuote1
      AND NOT (lInQuote AND lLiteral)
      THEN lInQuote = NOT lInQuote.
    END.

    IF cChar = ""'"" THEN do:
      IF NOT lInQuote
      AND NOT (lInQuote1 AND lLiteral)
      THEN lInQuote1 = NOT lInQuote1.
    END.

    IF NOT lInQuote AND NOT lInQuote1 AND cChar = "" ""
    THEN DO:
      IF lLastDelim THEN NEXT.
      cTemp = cTemp + ipcDelimiter.
      lLastDelim = TRUE.
    END.
    ELSE DO:
      cTemp = cTemp + cChar.
      lLastDelim = FALSE.
    END.
    lLiteral = (cChar = CHR(126)).
  END.

  iopcstring = cTemp.
END PROCEDURE.


So as an example the following code excerpt will look for any instances of sports2000.Customer or sports2000.Order in a freeform query and if they exist add a condition to filter the query based on a list of sales reps related to some context information;


PROCEDURE PAManageQuery :
  /*------------------------------------------------------------------------------
  Purpose: Called by the DataPA server-side procedures whenever a freeform
  query is run. Recieves the query string that DataPA will use so
  it can be changed on the server.

  Parameters: <none>
  Notes:
  ------------------------------------------------------------------------------*/
  DEFINE INPUT-OUTPUT PARAMETER cQueryString AS CHARACTER.
  /* cTargetTables contains a commer seperated list of the tables this query will search for to filter */
  DEFINE VARIABLE cTargetTables AS CHARACTER NO-UNDO INITIAL ""sports2000.Customer,sports2000.Order"".
  /* cAddCondition contains the condition that will be used to filter the tables */
  DEFINE VARIABLE cAddCondition AS CHARACTER NO-UNDO INITIAL ""CAN-DO(DYNAMIC-FUNCTION('GetRepList', SESSION:SERVER-CONNECTION-CONTEXT), <TABLE-NAME>.SalesRep)"".
  /* Other internal variables */
  DEFINE VARIABLE cAllTableList AS CHARACTER NO-UNDO.
  DEFINE VARIABLE cTableList AS CHARACTER NO-UNDO.
  DEFINE VARIABLE ix AS INTEGER NO-UNDO.
  DEFINE VARIABLE cTable AS CHARACTER NO-UNDO.
  DEFINE VARIABLE cWorkString AS CHARACTER NO-UNDO.
  DEFINE VARIABLE iy AS INTEGER NO-UNDO.
  DEFINE VARIABLE cPrevious AS CHARACTER NO-UNDO.
  DEFINE VARIABLE cCurrentTable AS CHARACTER NO-UNDO.
  DEFINE VARIABLE cToken AS CHARACTER NO-UNDO.
  DEFINE VARIABLE cAndWhere AS CHARACTER NO-UNDO.
  DEFINE VARIABLE lIncludesOr AS LOGICAL NO-UNDO.

  /* Evaluation the dynamic function first to improve performance and incase there are any finds or for each's in it */
  cAddCondition = REPLACE(cAddCondition, ""CAN-DO(DYNAMIC-FUNCTION('GetRepList', SESSION:SERVER-CONNECTION-CONTEXT), <TABLE-NAME>.SalesRep)"", """""""" + CAN-DO(DYNAMIC-FUNCTION('GetRepList', SESSION:SERVER-CONNECTION-CONTEXT), <TABLE-NAME>.SalesRep) + """""""").
  ASSIGN cWorkString = cQueryString.
  RUN PATokeniseQuery(INPUT-OUTPUT cWorkString, INPUT CHR(1)).
  /* Build a list of tables we need to add a condition to */
  cAllTableList = PATableList(cQueryString).
  REPEAT WITH ix = 1 TO NUM-ENTRIES(cAllTableList,CHR(1)):
    cTable = TRIM(ENTRY(ix,cAllTableList,CHR(1))).
    IF CAN-DO(cTargetTables,cTable) THEN DO:
      /* OK, here is a table we need to add the condition to */
      IF cTableList <> """" THEN cTableList = cTableList + "","".
      cTableList = cTableList + cTable.
    END.
  END.
  /* If we have found any tables we need to add conditions too, */
  /* process the query string to add them */
  IF cTableList <> """" THEN DO:
    cQueryString = """".
    cCurrentTable = TRIM(REPLACE(TRIM(ENTRY(3,cWorkString,CHR(1))),"","","" "")) NO-ERROR.
    cAndWhere = ""WHERE"".
    lIncludesOr = FALSE.
    REPEAT WITH ix = 1 TO NUM-ENTRIES(cWorkString,CHR(1)):
      cToken = TRIM(ENTRY(ix,cWorkString,CHR(1))).
      IF cToken = ""OR"" THEN lIncludesOr = TRUE.
      IF CAN-DO(cTableList,cCurrentTable) THEN DO:
        IF cToken = ""BY"" THEN DO:
          IF lIncludesOr
          THEN DO:
            cQueryString = REPLACE(cQueryString,cCurrentTable + "" Where "",cCurrentTable + "" Where ("").
            cAndWhere = "") And"".
          END.
          cToken = cAndWhere + "" "" + TRIM(REPLACE(cAddCondition,""<TABLE-NAME>"",cCurrentTable)) + "" BY"".
          cCurrentTable = """".
        END.
        ELSE IF cToken MATCHES ""*,"" THEN DO:
          IF lIncludesOr
          THEN DO:
            cQueryString = REPLACE(cQueryString,cCurrentTable + "" Where "",cCurrentTable + "" Where ("").
            cAndWhere = "") And"".
          END.
          cToken = SUBSTRING(cToken,1,LENGTH(cToken) - 1) + "" "" + cAndWhere + "" "" + TRIM(REPLACE(cAddCondition,""<TABLE-NAME>"",cCurrentTable)) + "","".
          cCurrentTable = """".
        END.
      END.
      cQueryString = cQueryString + cToken + "" "".
      IF cToken = ""Where"" THEN cAndWhere = ""AND"".
      IF CAN-DO(cTableList,cCurrentTable) THEN DO:
        IF ix = NUM-ENTRIES(cWorkString,CHR(1)) THEN DO:
          IF lIncludesOr
          THEN DO:
            cQueryString = REPLACE(cQueryString,cCurrentTable + "" Where "",cCurrentTable + "" Where ("").
            cAndWhere = "") And"".
          END.
          cQueryString = cQueryString + "" "" + cAndWhere + "" "" + TRIM(REPLACE(cAddCondition,""<TABLE-NAME>"",cCurrentTable)).
          cCurrentTable = """".
        END.
      END.
      IF LENGTH(cPrevious) > 0
      AND SUBSTRING(cPrevious,LENGTH(cPrevious),1) = "",""
      AND CAN-DO(""EACH,FIRST,LAST,PREV,NEXT"",cToken)
      THEN IF ix + 1 <= NUM-ENTRIES(cWorkString,CHR(1)) THEN DO:
        cCurrentTable = TRIM(ENTRY(ix + 1,cWorkString,CHR(1))) NO-ERROR.
        cAndWhere = ""WHERE"".
        lIncludesOr = FALSE.
      END.
      cPrevious = cToken.
    END.
  END.
END PROCEDURE.


Adding a procedure to the Super Procedure Stack


Follow these steps to add the structured procedure that contains the function definitions to the super procedure stack:

  1. Open the Progress AppBuilder
  2. Select Tools --> New Procedure Window
  3. When an AppServer agent runs a startup procedure, it passes in a character variable. Therefore your procedure must start with an input character variable definition as below:
    DEFINE INPUT PARAMETER startup-data AS CHARACTER NO-UNDO.

  4. Define a progress handle variable we can use to store the procedure handle:
    DEFINE VARIABLE hProc AS HANDLE NO-UNDO.

  5. Run the structured procedure persistently, storing the handle in the defined variable, and add it to the session super
    RUN OrderFunctions.p PERSISTENT SET hProc.
    SESSION:ADD-SUPER-PROCEDURE(hProc).

  6. Save the procedure as startup.p somewhere in the PROPATH of the AppServer.
  7. Open Progress Explorer
  8. Open the properties screen for your AppServer
  9. Select Agent --> Advanced Features
  10. Enter startup.p in the Startup text box.
  11. Press OK
  12. Restart the AppServer.