Question Accessing tables from multiple db

[jdpjamesp]

Ah yes here it is. It's just a rip of an internal procedure so won't work as it is, but gives you an idea.

PROCEDURE AmalgamateLines :
  /*------------------------------------------------------------------------------
   Purpose:
   Notes:
  ------------------------------------------------------------------------------*/
  DEFINE VARIABLE lv-SortField       AS CHARACTER NO-UNDO.
  DEFINE VARIABLE lv-Query           AS HANDLE    NO-UNDO.
  DEFINE VARIABLE lv-QueryString     AS CHARACTER NO-UNDO.
  DEFINE VARIABLE lv-QueryBuffer     AS HANDLE    NO-UNDO.
  DEFINE VARIABLE lv-Handle          AS HANDLE    NO-UNDO.
  DEFINE VARIABLE lv-RecQueryString  AS CHARACTER NO-UNDO.
  DEFINE VARIABLE lv-RecQueryHandle  AS HANDLE    NO-UNDO.
  DEFINE VARIABLE lv-RecBufferHandle AS HANDLE    NO-UNDO.
  DEFINE VARIABLE lv-i               AS INTEGER   NO-UNDO.

  RUN GetBufferHandle IN lv-Browse-Manager-Handle
    (OUTPUT lv-Handle).

  CREATE BUFFER lv-QueryBuffer     FOR TABLE lv-Handle.
  CREATE BUFFER lv-RecBufferHandle FOR TABLE lv-Handle.

  ASSIGN
    lv-SortField = com-Unique:SCREEN-VALUE IN FRAME {&FRAME-NAME}.

  ASSIGN
    lv-QueryString = "FOR EACH " + lv-Handle:NAME + " BREAK BY " + lv-Handle:NAME + "." + lv-SortField.

  CREATE QUERY lv-Query.
  lv-Query:SET-BUFFERS(lv-QueryBuffer).
  lv-Query:QUERY-PREPARE(lv-QueryString).
  lv-Query:QUERY-OPEN.

  lv-Query:GET-FIRST.

  DO WHILE NOT lv-Query:QUERY-OFF-END:
    IF lv-Query:FIRST-OF(1) THEN
    DO:
      ASSIGN 
        lv-RecQueryString = "FOR EACH " + lv-RecBufferHandle:NAME + " WHERE RECID(" + lv-RecBufferHandle:NAME + ") EQ " + STRING(lv-QueryBuffer:RECID).
      CREATE QUERY lv-RecQueryHandle.
      lv-RecQueryHandle:SET-BUFFERS(lv-RecBufferHandle).
      lv-RecQueryHandle:QUERY-PREPARE(lv-RecQueryString).
      lv-RecQueryHandle:QUERY-OPEN.
      lv-RecQueryHandle:GET-FIRST.
    END.
    ELSE
    DO:
      DO lv-i = 1 TO NUM-ENTRIES({&AmalgListStartDate}):
        FIND FIRST tt-DataCombos
          WHERE tt-DataCombos.DataColumn EQ entry(lv-i,{&AmalgListStartDate}) NO-ERROR.
        IF tt-DataCombos.WidgetHandle:SCREEN-VALUE NE "0" THEN
        DO:
          IF lv-QueryBuffer:BUFFER-FIELD(tt-DataCombos.BrowseColumn):BUFFER-VALUE LT lv-RecBufferHandle:BUFFER-FIELD(tt-DataCombos.BrowseColumn):BUFFER-VALUE THEN
            lv-RecBufferHandle:BUFFER-FIELD(tt-DataCombos.BrowseColumn):BUFFER-VALUE = lv-QueryBuffer:BUFFER-FIELD(tt-DataCombos.BrowseColumn):BUFFER-VALUE.
        END.
      END.
      DO lv-i = 1 TO NUM-ENTRIES({&AmalgListEndDate}):
        FIND FIRST tt-DataCombos
          WHERE tt-DataCombos.DataColumn EQ entry(lv-i,{&AmalgListEndDate}) NO-ERROR.
        IF tt-DataCombos.WidgetHandle:SCREEN-VALUE NE "0" THEN
        DO:
          IF lv-QueryBuffer:BUFFER-FIELD(tt-DataCombos.BrowseColumn):BUFFER-VALUE GT lv-RecBufferHandle:BUFFER-FIELD(tt-DataCombos.BrowseColumn):BUFFER-VALUE THEN
            lv-RecBufferHandle:BUFFER-FIELD(tt-DataCombos.BrowseColumn):BUFFER-VALUE = lv-QueryBuffer:BUFFER-FIELD(tt-DataCombos.BrowseColumn):BUFFER-VALUE.
        END.
      END.
      DO lv-i = 1 TO NUM-ENTRIES({&AmalgListDec}):
        FIND FIRST tt-DataCombos
          WHERE tt-DataCombos.DataColumn EQ entry(lv-i,{&AmalgListDec}) NO-ERROR.
        IF tt-DataCombos.WidgetHandle:SCREEN-VALUE NE "0" THEN
          lv-RecBufferHandle:BUFFER-FIELD(tt-DataCombos.BrowseColumn):BUFFER-VALUE = lv-RecBufferHandle:BUFFER-FIELD(tt-DataCombos.BrowseColumn):BUFFER-VALUE + lv-QueryBuffer:BUFFER-FIELD(tt-DataCombos.BrowseColumn):BUFFER-VALUE.
      END.
      lv-QueryBuffer:BUFFER-DELETE.
    END.

    IF lv-Query:LAST-OF(1) THEN
    DO:
      IF VALID-HANDLE(lv-RecQueryHandle) THEN
      DO:
        lv-RecQueryHandle:QUERY-CLOSE.
        DELETE OBJECT lv-RecQueryHandle.
      END.
    END.
    lv-Query:GET-NEXT.
  END.

  lv-Query:QUERY-CLOSE.
  DELETE OBJECT lv-Query.
  DELETE OBJECT lv-QueryBuffer.
  DELETE OBJECT lv-RecBufferHandle.


END PROCEDURE.

 

[RealHeavyDude]

One can learn something new every day. Thanks Cringer!

I've looked for the first-of functionality on the buffer handle - obviously I was looking in the wrong place though.

Heavy Regards, RealHeavyDude.

 

[Shubhrant]

My progress version is 10.1 CHUI with MFG/Pro eb2 SP1.
I have attached the code in the previous communication... in that code while creating the QUERY i have added a break by. but where i am running the DISPLAY portion by accessing the buffer, the FIRST-OF() or LAST-OF() does not work. even if is tried with the syntax:
if first-of(buf[2]:BUFFER-FIELD("_Index-Name"):name) then.

I am getting a compile time error.
** BREAK keyword or BY phrase missing for aggregate expression. (574)

 

[jdpjamesp]

I believe that dynamic break by may have been introduced in 10.1C. if first-of(buf[2]:BUFFER-FIELD("_Index-Name"):name) will definitely not work. Maybe if you paste the code you are having issues with we can help debug it.

 

[Shubhrant]

Hey Cringer....you are a Star.
Finally i managed to get the output as expected with the FIRST-OF().

Just a quick suggestion on the error ...actually a progress error for


This is not a show stopper. actually for the last record with blank value this appears.
Even though i tried to suppress it through error handling then also this error keeps on popping up.
Any idea on it how to by pass it or resolve it.

 

[jdpjamesp]

Can you paste your code?

 

[Shubhrant]

{mfdtitle.i}
define variable qh     as widget-handle.
define variable buf    as widget-handle extent 10.
define variable numvar as integer initial 10.
define variable cQuery as character no-undo.
define variable mTable as character no-undo format "x(20)".
define variable cTable as character no-undo format "x(20)".
define variable mTab   as character no-undo format "x(20)" extent 4.
/*define variable mTab2  as character no-undo format "x(20)".*/
define variable i      as int.
define variable j      as int.
define variable cnt    as int.
define variable mDbname as character no-undo format "x(16)".
define variable mFldDet as logical   no-undo format "Field/Index".
define frame a
    mDbname colon 20 label "DBNAME"
    mTable  colon 20 label "TABLE NAME"
    mFldDet colon 20 label "DETAILS"
    with width 80 side-labels row 2.
define frame c
    /*_File-name*/
    _Order
    _Field-Name
    _Label
    _Col-label
    _Data-Type
    _Format
    _Mandatory
    _Field._Extent
    with width 240 down.
define frame d
    _Index-name
    _Field-name
    _Unique
    with width 80 down.
repeat:
    update
        mDbname
        mTable
        mFldDet
        with frame a.
    /*assign cTable = "tr_hist,xx_gvs_lst".*/
 
    mDbname = mDbname + ".".
    mTab[1] = (mDbname + "_File").
    if mFldDet then do:
        mTab[2] = (mDbname + "_Field").
        assign
            cQuery = "FOR EACH " + mDbname + "_File" +
            "  where _File._File-name = " + "'" + mTable + "'" + " no-lock," +
            "each " + mDbname + "_Field where _Field._File-recid = recid(_File) no-lock" +
            " break by _order" .
    end.
    else do:
        mTab[2] = (mDbname + "_Index").
        mTab[3] = (mDbname + "_Index-Field").
        mTab[4] = (mDbname + "_Field").
        assign
            cQuery = "FOR EACH " + mDbname + "_File" +
            "  where _File._File-name = " + "'" + mTable + "'" + " no-lock," +
            "each " + mDbname + "_Index where _Index._File-recid = recid(_File) no-lock," +
            "each " + mDbname + "_Index-field where _Index-Field._Index-recid = recid(_Index) no-lock," +
            "each " + mDbname + "_Field where recid(_Field) = _Index-Field._Field-recid no-lock" +
            " break by _Index-Name".
    end.
    output to "cQuery.p".
    put unformatted cQuery format "x(500)" at 1.
    output close.
     
    /*cnt = num-entries(mTab1,mTab2).*/
    {mfselbpr.i "PAGE" 240}
        {mfphead.i}
    if error-status:error then do:
        message "List Completed".
    end.
    CREATE QUERY qh.
    if mFldDet then do:

        repeat i = 1 to 2.
            create buffer buf[I] for table mTab[I].
            qh:add-buffer(buf[I]).
        end.

        qh:QUERY-PREPARE(cQuery).
        qh:QUERY-OPEN.
        DO WHILE NOT qh:QUERY-OFF-END:
            qh:get-next().
            j = j + 1.
            if buf[2]:BUFFER-FIELD("_Order"):buffer-value = ? then do:
                next.
            end.
            if error-status:error then do:
               message "List Completed" view-as alert-box.
            end.
            if j = 1 then
                display
                "TABLE DETIALS FOR:" at 1
                buf[1]:BUFFER-FIELD("_File-name"):buffer-value @ _File-name at 20 no-label
                with frame b down.

            display

                buf[2]:BUFFER-FIELD("_Order"):buffer-value @ _Order
                buf[2]:BUFFER-FIELD("_Field-Name"):buffer-value @ _Field-Name
                buf[2]:BUFFER-FIELD("_Label"):buffer-value @ _label
                buf[2]:BUFFER-FIELD("_Col-label"):buffer-value @ _Col-label
                buf[2]:BUFFER-FIELD("_Data-Type"):buffer-value @ _Data-Type
                buf[2]:BUFFER-FIELD("_Format"):buffer-value @ _Format 
                buf[2]:BUFFER-FIELD("_Mandatory"):buffer-value @ _Mandatory
                buf[2]:BUFFER-FIELD("_Extent"):buffer-value @ _Field._Extent 
                WITH frame c down no-error.
            down with frame c.   
        end.
    end.
    else do:
        repeat i = 1 to 4.
            create buffer buf[I] for table mTab[I].
            qh:add-buffer(buf[I]).
        end.
        qh:QUERY-PREPARE(cQuery).
        qh:QUERY-OPEN.
        DO WHILE NOT qh:QUERY-OFF-END:
            qh:get-next().
             if qh:first-of(1) then do:
                display
                    buf[2]:BUFFER-FIELD("_Index-Name"):buffer-value @ _Index-Name
                    buf[2]:BUFFER-FIELD("_Unique"):buffer-value @ _Unique column-label "Primary!Umique"
                    buf[4]:BUFFER-FIELD("_Field-name"):buffer-value @ _Field-name
                    with frame d down.
            end.
            else
                display
                    buf[4]:BUFFER-FIELD("_Field-name"):buffer-value @ _Field-name
                    with frame d down.
            down with frame d.
        end.
    end.

    qh:QUERY-CLOSE().
    DELETE OBJECT qh.
    {mfrtrail.i}
end.

 

[jdpjamesp]

Change

       DO WHILE NOT qh:QUERY-OFF-END:
           qh:get-next().

To

DO WHILE qh:GET-NEXT():

and don't do the qh:get-next() line at all. What's happening is that your get next is going off the end of the query at the end but you're still trying to process it.

 

[Shubhrant]

Cringer... Appreciate you support.
Code has worked and running smooth now.

 

[jdpjamesp]

High Five!