For Each Statement Populated By Variable

[Phillip]

I'm trying to dump text files for use in our business intelligence software and, rather than modifying the program for each new table they want added, would like to import the list of tables to a temp table and run from there. I dumped all the tables in the database to a csv and had our Network Administrator put a "Y" next to each table he wanted dumped in column B. What I will do is import them into a temp table and run as such:

DEF TEMP-TABLE sysTables
    FIELD tablename AS CHAR
    FIELD toberun AS CHAR.

FOR EACH sysTables WHERE sysTables.toberun = "Y":
    /*Run Loop for Dump of Selected Tables*/
END.

Obviously the temp table would be populated from a csv import statement, I just didn't want to fill more space where it wasn't needed. The problem I have is I would like to populate a FOR EACH statement with the sysTables.tablename value as follows:

DEF VAR cTable AS CHAR.
cTable = sysTables.tablename.

FOR EACH {&cTable} NO-LOCK:
    EXPORT DELIMITER ","
       /*dump each field of cTable in DB Symix*/
END.

I have never used dynamic queries so this is proving somewhat difficult for me to learn out of the gate. I saw a &scoped-define function which I haven't used much but I wasn't sure if that would work or not? Any help is greatly appreciated.

Thanks!

 

[oli]

Hi Phillip,

You can use dynamic objects, of course. But unfortunately there is no dynamic equivalent to the EXPORT statement. You'll have to dump data field by field rather than record by record, and this will generate a serious overhead.
I would rather recommend you to use the program behind the Data Administration's "Dump data" utility (dump_d.p). For more information, take a look at these articles:

How to programmatically dump and load database definitions (.df's) and data (.d's) files using Data Administration routines ?

How to Dump and load .df and .d from outside the Data Administration tool?

This is the header comments of dump_d.p, where you get an explanation of the input parameters.

/* file-name may be either a specific file or ALL */
/*
File:  prodict/dump_d.p

Description:
  This was changed to that callers can call it persistently, so
  that we provide support for setting other settings that were
  not available before, such as the LOB-DIR. We can't change
  the signature of this for backward compatibility reasons, so
  we are providing another way for setting it. See Other-settings
  for the new settings that can be set. Each one of them can be
  set by calling the corresponding setter internal procedure.
  
  NOTE that this can still be called as a regular procedure, in
  which case we will run it as previously with no change in behavior.
  
IN:
  file-name  : "ALL" or "<file-name>"
  dot-d-dir  : directory relativ to working-directory
  code-page  : ?, "", "<code-page>"
  
  
Other-settings:  
  cLob-Dir  : ?, "", directory
  map-option  : "MAP <name>" or "NO-MAP" OR ""

 

[Rob Fitzpatrick]

What is your OE version?

 

[Phillip]

What is your OE version?

We're running 9.1E in an all-Windows environment (yes, I know it's massively outdated - one of the gripes I have but gotta work with what you have unfortunately)

 

[Rob Fitzpatrick]

I was going to say that you could put your "to be run" flag in _File._Category, so that the schema could be somewhat self-documenting. But that field was added in OE v11.

 

[GregTomkins]

Depending on your Progress license (not version - I mean, your ability to run compiled-on-the-fly code), you could do this:

/* main.p */
{include.i &file = orders}
{include.i &file = customers}
etc.

/* include.i */
for each {&file}:
export {&file}.

Dynamic queries are much cooler, though, and work with any license.