Description
Read data for ReadQuery in reverse order
Parameters
| icQueryName | input | character | |
| icPrepare | input | character | |
| icTables | input | character | |
| icFieldConvert | input | character | |
| oiReturnStatus | output | integer | Return status of the method. |
Internal usage
BLF
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.