project BLF > class Persistence (Progress) > method ReadQuery

Description

Read data from the database, and return all retrieved data in a single table.

PreCondition

This method is used in query calls and should not be used anywhere else.


Parameters


icQueryNameinputcharacter
icPrepareinputcharacterFor each statement for reading the data, excluding the no-lock option (this is implied automatically), excluding break by (not allowed, only normal by is allowed).
icTablesinputcharacterComma seperated list of all database tables in the for each statement.
ihDestinationBufferinputhandleHandle to the temp-table that will receive the data.
icLastRowidinputcharacterComma seperated list of database record rowids of last record in previous ReadQuery call, contains one rowid for each table in input parameter icTables. Is empty for first call.
iiLastRownuminputinteger
iiMaxNumberinputintegerMaximum number of records (in the temp-table) to retrieve.
Zero if the amount is unlimited.
A negative value if exactly one record must be loaded (the record with rowid found in icLastRowid).
Remark: if value is +1, also one record will be returned, but this will be the record following record found with icLastRowid.
icFieldConvertinputcharacterContains the information to know in which temp-table field the database field must be loaded (as opposed to class temp-tables, the field name will be different).
Format: '<buffer>.<db-field-1>,<tt-field-1>|<buffer>.<db-field-2>,<tt-field-2>|...'
icCustomFieldsinputcharacter
ilDistinctinputlogicalLoad only distinct values ?
ilForwardReadinputlogicalIndicates query should read forward.
olEofoutputlogicalWhen iiMaxNumber > 0, this parameter will be true when the last record of the query is included in the result set, or when no record was found at all.
When iiMaxNumber <= 0, this parameter will be only true when no record was found at all.
oiReturnStatusoutputinteger


Internal usage


unused


program code (program1/progress.p)

assign vhRQRowidField        = ?
       vhRQSequenceField     = ?.

assign vhRQRowidField        = ihDestinationBuffer:buffer-field("tc_Rowid":U)
       vhRQSequenceField     = ihDestinationBuffer:buffer-field("ti_Sequence":U)  no-error.

assign vgDebugTime           = etime
       vhRQDestinationBuffer = ihDestinationBuffer
       vcRQCustomFields      = icCustomFields
       vcRQLastRowid         = icLastRowid
       viRQMaxNum            = (if iiMaxNumber = 0
                                or iiMaxNumber = ?
                                then 50000
                                else iiMaxNumber) + iiLastRownum
       vlRQDistinct          = ilDistinct
       vlRQEof               = yes
       vlRQSkip              = (icLastRowid <> "" and icLastRowid <> ? and vhRQRowidField <> ?)
       viRQCount             = iiLastRownum
       vhRQtarget            = {&TARGETPROCEDURE}
       vhDataSet             = ihDestinationBuffer:dataset
       vcQueryDateFormat     = ""
       vcQueryNumericFormat  = ""
       ihDestinationBuffer:fill-mode = "APPEND":U.
       
if ilForwardRead = no
then do:
    <M-27 run ReadQueryBackward
       (input  icQueryName (icQueryName), 
        input  icPrepare (icPrepare), 
        input  icTables (icTables), 
        input  icFieldConvert (icFieldConvert), 
        output oiReturnStatus (oiReturnStatus)) in Progress>
    olEof = vlRQEof.
    return.
end.

/* ================================================================================= */
/* Create a progress dynamic query to read the database                              */
/* ================================================================================= */
for each tBufferFields where tBufferFields.thTempBuffer = ihDestinationBuffer:
    delete tBufferFields.
end.

if ihDestinationBuffer:data-source = ?
then do:
    <M-25 run QueryPrepare
       (input  icTables (icTables), 
        input  icFieldConvert (icFieldConvert), 
        input  ihDestinationBuffer (ihDestinationBuffer), 
        output viFcReturnSuper (oiReturnStatus)) in Progress>
    if viFcReturnSuper <> 0
    then oiReturnStatus = viFcReturnSuper.
    if viFcReturnSuper < 0
    then return.
    assign vlSingleRun  = yes
           vhDataSource = ihDestinationBuffer:data-source
           vhQuery      = vhDataSource:query.

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

    assign vlSingleRun  = no
           vhDataSource = ihDestinationBuffer:data-source
           vhQuery      = vhDataSource:query
           viRQTables   = vhQuery:num-buffers.
    do viFcCount1 = 1 to viRQTables:
        vhRQBuffer[lookup(entry(viFcCount1,icTables),vcFixedTables)] = vhQuery:get-buffer-handle(viFcCount1).
    end.
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 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 = ihDestinationBuffer:buffer-field(vcTTField)
                   tBufferFields.thTempBuffer       = ihDestinationBuffer
                   tBufferFields.tcFieldName        = vcTTField.
        end.
    end.
end.

vlFcOk = vhQuery:query-prepare (icPrepare) 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.

    if vlRQSkip
    or vlRQDistinct
    or iiMaxNumber <> 0
    then ihDestinationBuffer:set-callback-procedure("BEFORE-ROW-FILL":U,"ReadQueryBefore":U,this-procedure).

    ihDestinationBuffer:set-callback-procedure("AFTER-ROW-FILL":U,"ReadQueryAfter":U,this-procedure).

    /* reposition */
    if num-entries (vcRQLastRowid) = viRQTables
    then do:
        do viFcCount1 = viRQTables to 1 by -1:
            if entry (viFcCount1,vcRQLastRowid) = ""
            or entry (viFcCount1,vcRQLastRowid) = "?"
            then if viReposition = 0
                 then.
                 else do:
                     viReposition = 0.
                     leave.
                 end.
            else if viReposition = 0
                 then viReposition = viFcCount1.
                 else.
        end.
        
        if viReposition = 0
        then vhDataSource:restart-row = viRQCount.
        else do viFcCount1 = 1 to viReposition:
            vhDataSource:restart-rowid(viFcCount1) = to-rowid (entry(lookup(entry(viFcCount1,icTables),vcFixedTables),vcRQLastRowid)).
        end.
    end.
    
    vhDataSet:fill().

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

    assign oiReturnStatus = -3.
end.

assign olEof = vlRQEof.
if vlSingleRun
then do:
    <M-26 run QueryDestroy  (input  ihDestinationBuffer (ihDestinationBuffer)) in Progress>
end.

end procedure.


procedure ReadQueryBefore:
    define input parameter dataset-handle ihDataSet.

    define variable vcRQRowid as character no-undo.
    define variable vhTQuery as handle no-undo.
    define variable vcTTField as character no-undo.
    
    define variable ih_Target as handle no-undo.
    ih_Target = vhRQtarget.

    /* 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.
        return no-apply.
    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-20 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.
            return no-apply.
        end.
    end.

    if  viRQMaxNum <> 0
    and viRQMaxNum <= viRQCount
    then do:
        assign vlRQEof = no.
        return error.
    end.

end procedure.


procedure ReadQueryAfter:
    define input parameter dataset-handle ihDataSet.

    define variable vhCustomField as handle no-undo.
    define variable vhSourceField as handle no-undo.
    define variable vcTTField as character no-undo.
    define variable vcTTRowid as character no-undo.
    define variable vcCustomValue as character no-undo.
    define variable vtCustomValue as date no-undo.
    define variable vcDateSeparator as character no-undo.

    assign viRQCount = viRQCount + 1.

    if vhRQSequenceField <> ?
    then assign vhRQSequenceField:buffer-value = viRQCount.

    if vhRQRowidField <> ?
    then do:
        do viFcCount2 = 1 to viRQTables:
            vcTTRowid = (if viFcCount2 = 1
                         then ""
                         else vcTTRowid + ",":U)
                      + (if vhRQBuffer[viFcCount2]:available
                         then string(vhRQBuffer[viFcCount2]:rowid)
                         else "?":U).
        end.
        vhRQRowidField:buffer-value = vcTTRowid.
    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-89 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-17 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.