| 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.