project BLF > class Persistence (Progress) > method ReadQueryBackward

Description

Read data for ReadQuery in reverse order


Parameters


icQueryNameinputcharacter
icPrepareinputcharacter
icTablesinputcharacter
icFieldConvertinputcharacter
oiReturnStatusoutputintegerReturn status of the method.


Internal usage


BLF
method Progress.ReadQuery


program code (program1/progress.p)

if num-entries(icTables,"|") = 2
then assign vcFixedTables = entry(1,icTables,"|")
            icTables      = entry(2,icTables,"|").
else assign vcFixedTables = icTables.

vgDebugTime = etime.

/* ================================================================= */
/* Create dynamic query                                              */
/* ================================================================= */
create query vhQuery in widget-pool "non-persistent".

viRQTables = num-entries(icTables).
do viFcCount1 = 1 to viRQTables:
    vcBufferName = entry(viFcCount1,icTables).
    vhBuffer[viFcCount1] = ?.
    if vcBufferName matches "*1"
    then do:
        <M-18 run GetQBuffer
           (input  vcBufferName (icBufferName), 
            input  vhQuery:unique-id (iiQueryID), 
            output vhBuffer[viFcCount1] (ohBufferHandle)) in Progress>
    end.
    if vhBuffer[viFcCount1] = ?
    then do:
        /* These buffers will not be cached */
        create buffer vhBuffer[viFcCount1]
               for table substring(vcBufferName,1,length(vcBufferName,"CHARACTER":U) - 1,"CHARACTER":U)
               buffer-name vcBufferName in widget-pool "non-persistent".
        vlBufferCreated[viFcCount1] = yes.
    end.
    vhQuery:add-buffer(vhBuffer[viFcCount1]).
    vhRQBuffer[lookup(vcBufferName,vcFixedTables)] = vhBuffer[viFcCount1].
end.

for each tBufferFields where tBufferFields.thTempBuffer = vhRQDestinationBuffer:
    delete tBufferFields.
end.

do viFcCount1 = 1 to viRQTables:

    assign viFcCount4 = num-entries(icFieldConvert).
        
    do viFcCount2 = 1 to vhRQBuffer[viFcCount1]:num-fields:

        assign vhDbField  = vhRQBuffer[viFcCount1]:buffer-field(viFcCount2)
               vcTTField  = "".

        do viFcCount3 = 2 to viFcCount4 by 2:
            if entry(viFcCount3,icFieldConvert) =
               entry(viFcCount1,icTables) + ".":U + vhDbField:name
            then do:
                assign vcTTField = entry(2,entry(viFcCount3 - 1,icFieldConvert),".":U).
                leave.
            end.
        end.
        
        if vcTTField <> ""
        then do:
            create tBufferFields.
            assign tBufferFields.thBufferField      = vhDbField
                   tBufferFields.thDestinationField = vhRQDestinationBuffer:buffer-field(vcTTField)
                   tBufferFields.thTempBuffer       = vhRQDestinationBuffer
                   tBufferFields.tcFieldName        = vcTTField.
        end.
    end.
end.

vlFcOk = vhQuery:query-prepare (icPrepare) no-error.
if vlFcOk
then assign vlFcOk = vhQuery:query-open() no-error.
if vlFcOk
then do:
    if vlProgress
    then do viFcCount1 = 1 to vhQuery:num-buffers:
        assign vcIndexInfo = vcIndexInfo + chr(10) + "Index : ":U
                           + vhQuery:index-information(viFcCount1).
    end.

    /* ================================================================= */
    /* database read loop                                                */
    /* ================================================================= */
    
    do while true:
    
        vhQuery:get-prev(no-lock).
        if vhQuery:query-off-end
        then leave.
        
        /* Next statement handles a progress bug where query-off-end returns false
           after a get-last or get-prev even though no records were found. */
        if vhRQBuffer[1]:available = no
        then leave.
        
        /* simulate a reposition query to rowid (vcRQRowid) */
        if vlRQSkip
        or vlRQDistinct
        then do viFcCount2 = 1 to viRQTables:
            assign vcRQRowid = (if viFcCount2 = 1 then "" else vcRQRowid + ",":U)
                             + (if vhRQBuffer[viFcCount2]:available
                                then string(vhRQBuffer[viFcCount2]:rowid)
                                else "?":U).
        end.
    
        if vlRQSkip
        then do:
            if vcRQLastRowid = vcRQRowid
            then assign vlRQSkip = no.
            next.
        end.
    
        /* ================================================================= */
        /* For distinct queries, skip records that already exist in the      */
        /* result set. This test should remain here until progress handles   */
        /* it itself in the fill() procedure.                                */
        /* ================================================================= */
        if vlRQDistinct
        then do:
            assign vcTTField = "".
            for each tBufferFields where
                     tBufferFields.thTempBuffer = vhRQDestinationBuffer
                     on error undo, throw:
                assign vcTTField = (if vcTTField = ""
                                    then "for each ":U + vhRQDestinationBuffer:name + " where ":U
                                    else vcTTField + " and ":U)
                                 + vhRQDestinationBuffer:name + ".":U
                                 + tBufferFields.thDestinationField:name + " = ":U
                                 + (if tBufferFields.thBufferField:buffer-value = ?
                                    then "?":U
                                    else if tBufferFields.thDestinationField:name begins "tc":U
                                    then <M-6 EnQuote (input  tBufferFields.thBufferField:buffer-value (icToQuote)) in Progress>
                                    else string(tBufferFields.thBufferField:buffer-value)).
            end.
            create query vhTQuery in widget-pool "non-persistent".
            vhTQuery:forward-only = yes.
            vhTQuery:set-buffers(vhRQDestinationBuffer).
            vhTQuery:query-prepare(vcTTField).
            vhTQuery:query-open().
            vhTQuery:get-first().
            assign vlFcOk = not vhTQuery:query-off-end.
            vhTQuery:query-close().
            delete object vhTQuery.
            if vlFcOk
            then do:
                if vhRQRowidField <> ?
                then assign vhRQRowidField:buffer-value = vcRQRowid.
                next.
            end.
        end.
    
        if  viRQMaxNum <> 0
        and viRQMaxNum <= viRQCount
        then do:
            assign vlRQEof = no.
            leave.
        end.
    
        /* ================================================================= */
        /* Create and fill record in query resultset                         */
        /* ================================================================= */
        vhRQDestinationBuffer:buffer-create.
        for each tBufferFields where tBufferFields.thTempBuffer = vhRQDestinationBuffer:
            assign tBufferFields.thDestinationField:buffer-value = tBufferFields.thBufferField:buffer-value no-error.
        end.

        assign viRQCount = viRQCount + 1.
    
        if vhRQSequenceField <> ?
        then assign vhRQSequenceField:buffer-value = - viRQCount.
    
        if vhRQRowidField <> ?
        then do viFcCount2 = 1 to viRQTables:
            assign vhRQRowidField:buffer-value = (if viFcCount2 = 1
                                                then ""
                                                else vhRQRowidField:buffer-value + ",":U)
                                             + (if vhRQBuffer[viFcCount2]:available
                                                then string(vhRQBuffer[viFcCount2]:rowid)
                                                else "?":U).
        end.
        
        if vcRQCustomFields <> ""
        then do viFcCount2 = 1 to viRQTables:
            viFcCount3 = lookup (vhRQBuffer[viFcCount2]:name,vcRQCustomFields,"|":U).
            if viFcCount3 > 0
            then do:
                vhCustomField = vhRQDestinationBuffer:buffer-field(entry(viFcCount3 + 1, vcRQCustomFields, "|":U)).
                vcTTField = entry(viFcCount3 + 2, vcRQCustomFields, "|":U).
                do viFcCount3 = 1 to num-entries(vcTTField):
                    vcCustomValue = "".
                    if vhRQBuffer[viFcCount2]:available
                    then do:
                        vhSourceField = vhRQBuffer[viFcCount2]:buffer-field(entry(viFcCount3,vcTTField)).
                        vcCustomValue = string(vhSourceField:buffer-value).
                        if vcCustomValue = ?
                        then vcCustomValue = "".
                        else if vhSourceField:data-type = "date":U
                        then do:
                            if vcQueryDateFormat = ""
                            then do:
                                run StartCacherInPool (output vhFcComponent).
                                <M-2 run GetCharacterValue
                                   (input  'vcDateFormat' (icDataItemName), 
                                    output vcQueryDateFormat (ocValue), 
                                    output viFcReturnSuper (oiReturnStatus)) in Cacher>
                            end.
                            vtCustomValue = vhSourceField:buffer-value.
                            vcDateSeparator = (if length(vcQueryDateFormat,"CHARACTER":U) = 4
                                               then substring(vcQueryDateFormat,4,-1,"CHARACTER":U)
                                               else "/").
                            vcCustomValue = DisplayDateFormat (this-procedure, vtCustomValue, substring(vcQueryDateFormat,1,1,"character")) + vcDateSeparator
                                          + DisplayDateFormat (this-procedure, vtCustomValue, substring(vcQueryDateFormat,2,1,"character")) + vcDateSeparator
                                          + DisplayDateFormat (this-procedure, vtCustomValue, substring(vcQueryDateFormat,3,1,"character")).
                        end.
                        else if vhSourceField:data-type = "decimal"
                        then do:
                            if vcQueryNumericFormat = ""
                            then do:
                                run StartCacherInPool (output vhFcComponent).
                                <M-55 run GetCharacterValue
                                   (input  'vcNumericFormat' (icDataItemName), 
                                    output vcQueryNumericFormat (ocValue), 
                                    output viFcReturnSuper (oiReturnStatus)) in Cacher>
                            end.
                            if vcQueryNumericFormat = ",":U
                            or vcQueryNumericFormat = "european":U
                            then vcCustomValue = replace (vcCustomValue,".",",").
                        end.
                    end.
                    vhCustomField:buffer-value = (if viFcCount3 = 1
                                                  then ""
                                                  else vhCustomField:buffer-value + chr(2)) + vcCustomValue.
                end.
            end.
        end.
    end.
    
    vhQuery:query-close().

    publish "Logging.DatabaseAccess"
           ("query ":U + icQueryName + chr(10) +
            "read ":U + icTables + chr(10) +
            icPrepare + vcIndexInfo + chr(10) +
            "Record count=":U + string(viRQCount) + (if vlRQDistinct then " (distinct)":U else "") + chr(10) +
            "time(ms)=" + string(etime - vgDebugTime), ?).
end.
else do:
    publish "Logging.DatabaseAccess"
           ("query ":U + icQueryName + chr(10) +
            "read ":U + icTables + chr(10) +
            icPrepare + chr(10) + "FAILED":U, ?).

    <M-7 run ErrorMessage
       (input  #T-8'Invalid database query ($1).':255(88)T-8# (icMessage), 
        input  icPrepare (icArguments), 
        input  '' (icFieldName), 
        input  '' (icFieldValue), 
        input  '' (icRowid), 
        input  ? (ihClass)) in Progress>

    assign oiReturnStatus = -3.
end.

finally:
    if vhQuery <> ?
    then do:
        viFcCount2 = vhQuery:unique-id.
        delete object vhQuery.
        do viFcCount1 = 1 to viRQTables:
            if vlBufferCreated[viFcCount1]
            then delete object vhBuffer[viFcCount1].
            else do:
                <M-90 run ReleaseQBuffer
                   (input  entry(viFcCount1,icTables) (icBufferName), 
                    input  viFcCount2 (iiQueryID)) in Progress>
            end.
        end.
    end.
end finally.