icQueryName | input | character | |
icPrepare | input | character | For 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). |
icTables | input | character | Comma seperated list of all database tables in the for each statement. |
ihDestinationBuffer | input | handle | Handle to the temp-table that will receive the data. |
icLastRowid | input | character | Comma 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. |
iiLastRownum | input | integer | |
iiMaxNumber | input | integer | Maximum 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. |
icFieldConvert | input | character | Contains 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>|...' |
icCustomFields | input | character | |
ilDistinct | input | logical | Load only distinct values ? |
ilForwardRead | input | logical | Indicates query should read forward. |
olEof | output | logical | When 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. |
oiReturnStatus | output | integer |
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.