How to get the field-name's value automatically!

[sunnyleung]

Hello there,
I would like to get the tables contents automatically through the following two programs. Program one(exportdata.p) is used to get table lists from the silverdb.list text file and then call program two(exportdata2.p) to generate all records through the "field-name", but I don't know how to do it because it can't allow to get the value of field inside the "_file" block, please pay attention to " PUT value(_field-name)" on program two. Thanks.

silverdb.list
-----------
pt_mstr
ps_mstr
wo_mstr
wod_det
..
..

Program one:exportdata.p
define variable filepath as char format "x(40)"
initial "/silverdb.list".
define variable table_name as char format "x(30)".
define variable filename as char format "x(15)" initial "/exportdata2.p".
INPUT from value(filepath) NO-ECHO.
main-loop:
repeat:
set table_name with no-box no-attr-space.
RUN value(filename) table_name.
end.
INPUT CLOSE.

Program two:exportdata2.p
output to {1}.sql.
FOR EACH {1} no-lock:
PUT "INSERT INTO".
PUT "{1}".
PUT " VALUES(".

FOR EACH _file no-lock where _file-name = "{1}", each _field of _file:
PUT value(_field-name).
END.
END.
OUTPUT CLOSE.

 

[DevTeam]

You may achieve this with the use of dynamic buffer & query :

DEF INPUT PARAMETER mytable AS CHAR NO-UNDO.

OUTPUT TO VALUE(mytable + ".sql").

DEF VAR bh AS HANDLE NO-UNDO.
DEF VAR qh AS HANDLE NO-UNDO.
DEF VAR lstChp AS CHAR NO-UNDO.
DEF VAR cpt AS INT NO-UNDO.

CREATE BUFFER bh FOR TABLE mytable.
CREATE QUERY qh.

qh:ADD-BUFFER(bh).

qh:QUERY-PREPARE("PRESELECT EACH " + mytable).
qh:QUERY-OPEN().
qh:GET-FIRST().

FOR EACH _file no-lock where _file-name = mytable, each _field of _file:
  lstChp = lstCHp + _field-name + ",".
END.

lstChp = SUBSTRING(lstChp, 1, LENGTH(lstChp) - 1).

DO WHILE NOT qh:QUERY-OFF-END :
  PUT "INSERT INTO " mytable " VALUES(".

  DO cpt = 1 TO NUM-ENTRIES(lstChp) :
    PUT bh:BUFFER-FIELD(ENTRY(cpt, lstChp)):BUFFER-VALUE.
  END.

  PUT ")".

  qh:GET-NEXT().
END.
qh:QUERY-CLOSE().
OUTPUT CLOSE.

But you may also need to test the data-type of your column, in order to surround it with quotes (for character data-types) by example.