Progress Dynamic Query

[ajaysheth]

Hello All:

I am trying to run a generic program that accepts a DB table name (mfg/pro) and copies its record to a dynamic temp-table through buffer-copy. I am able to do this and it works fine.

Whats strange is that the double underscore fields like pt__dte01 whose value in DB table is "?" (question mark) are not getting picked from the table during buffer-copy.

Is this a bug in the buffer-copy process or the way I am picking the records. Below is the code. Thanks in advance.

-------------------------------------------------------------------
/* Dynamic Query to buffer-copy records from */
/* Dynamic DB Table to Dynamic Temp-table */

define variable qry_hndl as handle no-undo.
define variable qry_hndl2 as handle no-undo.
define variable tablbuf_hndl as handle no-undo.
define variable tablbuf_hndl2 as handle no-undo.
define variable v_tablename as char format "x(10)" no-undo.
define variable v_space as char init " " no-undo.
define variable v_fldcnt as inte init "1" no-undo.
define variable i as integer no-undo.

create query qry_hndl.
create query qry_hndl2.

/* Temp-table Definition */
define variable tt_target as handle.
create temp-table tt_target.

/* Accept input DB Table */
update v_tablename.

/* Buffer for DB Table */
CREATE BUFFER tablbuf_hndl FOR TABLE v_tablename.
qry_hndl:SET-BUFFERS(tablbuf_hndl).
qry_hndl:QUERY-PREPARE("for each" + v_space + v_tablename).
qry_hndl:QUERY-OPEN().

/* Create Dynamic Temp-table like the Input table */
tt_target:CREATE-LIKE(v_tablename).
tt_target:TEMP-TABLE-PREPARE(v_tablename).

/* Buffer for Temp-Table */
tablbuf_hndl2 = tt_targetEFAULT-BUFFER-HANDLE.

/* main logic */
output to "source.prn".
mainloop:
repeat:
qry_hndl:GET-NEXT().
IF qry_hndl:QUERY-OFF-END THEN LEAVE.
DO i = 1 to tablbuf_hndl:NUM-FIELDS:
put unformatted
tablbuf_hndl:BUFFER-FIELD(i):NAME v_space + " " +
tablbuf_hndl:BUFFER-FIELD(i):BUFFER-VALUE() skip.
end.
/* Buffer-copy */
tablbuf_hndl2:BUFFER-CREATE.
tablbuf_hndl2:BUFFER-COPY(tablbuf_hndl).
leave. /* need just one record */
end. /* repeat */
output close.
/* Show records from Temp-table */
qry_hndl2:SET-BUFFERS(tablbuf_hndl2).
qry_hndl2:QUERY-PREPARE("for each" + v_space + v_tablename).
qry_hndl2:QUERY-OPEN().

i = 0.
output to "target.prn".
repeat:
qry_hndl2:GET-NEXT().
IF qry_hndl2:QUERY-OFF-END THEN LEAVE.
DO i = 1 to tablbuf_hndl2:NUM-FIELDS:
put unformatted tablbuf_hndl2:BUFFER-FIELD(i):NAME + " " +
tablbuf_hndl2:BUFFER-FIELD(i):BUFFER-VALUE() skip.
end.
leave.
end.
output close.

qry_hndl:QUERY-CLOSE().
qry_hndl2:QUERY-CLOSE().
DELETE OBJECT qry_hndl.
DELETE OBJECT qry_hndl2.
DELETE OBJECT tablbuf_hndl.

-------------------------------------------------------------------

 

[dgstoker]

It is always tricky working with unknown values. Adding one character value to another unkown value will result in an unknown value. That is probably why your code will fail at this point:

put unformatted tablbuf_hndl2:BUFFER-FIELD(i):NAME + " " +
tablbuf_hndl2:BUFFER-FIELD(i):BUFFER-VALUE() skip.
​

I would suggest putting the BUFFER-VALUE in a variable first. If it contains an unknown value, you could replace it with something else, before you use it in the PUT statement.