Output row as csv to stdout

[rhoelle]

Hi all,
I'm a newbie at 4GL and Progress and I have what is probably a very simple question to most of you. I would like to format a row of data to csv using the EXPORT function but still have it output to stdout. My understanding of EXPORT is that it creates a .csv file and we are trying to bypass that but still be able to use this function. Is this possible?

Here's my code:

define variable cParamList as character no-undo.
define variable pbuilding as character no-undo.
define variable punit as character no-undo.
define variable pcharge-date as date no-undo.

assign  cParamList  = session:parameter
        pbuilding    = entry(1,cParamList,"|")
        punit        = entry(2,cParamList,"|")
        pcharge-date = 01/01/2007.

def temp-table tt 
    field t-bldg like rm-res.building
    field t-unit like rm-res.unit
    field t-res  like rm-res.resident
    field t-date as date.

for each rm-chg no-lock where
    rm-chg.building    = pbuilding AND
    rm-chg.unit        = punit AND
    rm-chg.chg-date >= pcharge-date:

    create tt.

    assign t-bldg = rm-chg.building
           t-unit = rm-chg.unit
           t-res  = rm-chg.resident
           t-date = rm-chg.chg-date.
    
    /* create csv of this row and send to stdout*/
    display tt.
end.

Thanks,
Rick

 

[Casper]

Try this:

define variable cParamList as character no-undo.
define variable pbuilding as character no-undo.
define variable punit as character no-undo.
define variable pcharge-date as date no-undo.
 
define stream sOut.
 
 
assign  cParamList  = session:parameter
        pbuilding    = entry(1,cParamList,"|")
        punit        = entry(2,cParamList,"|")
        pcharge-date = 01/01/2007.
 
def temp-table tt 
    field t-bldg like rm-res.building
    field t-unit like rm-res.unit
    field t-res  like rm-res.resident
    field t-date as date.
 
Output stream sOut to /path/to/where/you/want/name.csv.
 
for each rm-chg no-lock where
    rm-chg.building    = pbuilding AND
    rm-chg.unit        = punit AND
    rm-chg.chg-date >= pcharge-date:
 
    create tt.
 
    assign t-bldg = rm-chg.building
           t-unit = rm-chg.unit
           t-res  = rm-chg.resident
           t-date = rm-chg.chg-date.
 
    export stream sOut delimiter ';' rm-chg.
    display tt.
end.
 
output stream sOut close.

Casper.

 

[rhoelle]

Casper,
Thanks for the reply.

Ince you have: Output stream sOut to /path/to/where/you/want/name.csv. , won't that still output to a csv file instead of stdout?

Rick

 

[Casper]

Sorry,

misread your question.
What do you want to do with the csv file? Display it?

Casper.

 

[rhoelle]

Casper,
Instead of outputting the raw data using "display tt.", What I would really like to do is something like:

display EXPORT DELIMITER "|" tt

I know that's not right but it's the jest of what I am trying to do.

I'm calling the .p file using mbpro and want the output of that to be returned to my calling program. It does that with "display" but is not formatted in a way that can be parsed.

Thanks,
Rick

 

[TomBascom]

You want your EXPORT line to look something like:

export delimiter "," tt.

But EXPORT cannot output to the screen and a redirected "mpro" session will also fail. You need to start Progress like so:

_progres dbname -b -p programName.p > someFile

(or use a pipe instead of ">" if that's what you're really doing...)

 

[rhoelle]

Thanks Tom,
What I'm trying to do is eliminate the files all together. I'm assuming that what "display" is doing is writing the output to the default stream or stdout because when the above code is executed, I get the output like this:

Building Unit Resident t-date
-------- ---- -------- --------
L1-LD    091A 01       01/01/07
L1-LD    091A 01       02/01/07
L1-LD    091A 01       03/01/07
L1-LD    091A 01       03/09/07
L1-LD    091A 01       03/13/07

That would be fine if all I wanted to do is display it but I am trying break it up into an array in PHP. We are using php to display the data, and do additional formatting, in html. So, it would be best if it was delimited in some way to make it easy to parse.

We have a lot of these .p files and would prefer not to have to add too much code to each one to do a simple formatting routine on the output. I was hoping to be able to use a built-in function rather than have to create one.

Maybe a seperate .i file with some type of csv function that I could include would be a better answer?

Thanks,
Rick

 

[Casper]

Why not use webservices with php?
Easy to create from Progress (If you're on OpenEdge) and easy to use in php.

Casper.

 

[TomBascom]

There's no need for a file. That's why I wrote:

(or use a pipe instead of ">" if that's what you're really doing...)

IOW:

_progres dbname -b -p programName.p | something.php

Or, if you're calling Progress by shelling out of PHP:

_progres dbname -b -p programName.p

 

[rhoelle]

Just a note to let you know what we wound up with.

Here's the php5 execution code:

<?php
try{
    $pscript = null;
    $paramlist = null;
    $base = dirname(__FILE__);
    $scriptDir = "$base/progress/";
    chdir($scriptDir);
    
    if(!empty($_GET['script'])){
        $pscript = trim($_GET['script']);
    }
    if($pscript && file_exists($pscript)){
        
        if(!empty($_GET['params'])){
            // build params
            $params = explode(',',urldecode($_GET['params']));
            if(count($params) > 0){
                $paramlist = '-param "' . join('|',array_values($params)) . '"';
            }
        }
        
        $cmd  = "/bin/sh $scriptDir/mbpro -pf /db/xxxxxx.pfu -p $pscript";
        if($paramlist){
            $cmd .= " $paramlist";
        }
        
        $return = trim(shell_exec($cmd));
    }
    else{
        $return = "Error: $scriptDir/$pscript file does not exist.";
    }
}
catch (Exception $e){
    if($e->getMessage() != ""){
        $return = "Error: " . $e->getMessage();
    }
}

print $return;
?>

.p code:

define variable cParamList as character no-undo.
define variable pbuilding as character no-undo.
define variable punit as character no-undo.
define variable cOutput as character format "x(320)".

/*parse session params:*/

assign  cParamList  = session:parameter
        pbuilding    = entry(1,cParamList,"|")
        punit        = entry(2,cParamList,"|").


/* do all your progress code here */


/* prepare output */

    / * header */
    display "building|unit|chg-code|chg-date".

    assign cOutput = 
        string(building) + "|" + 
        string(unit) + "|" + 
        string(chg-code) + "|" + 
        string(chg-date). 

    display cOutput with frame f-rm-chg width 320 no-box no-labels no-attr-space.

This works well and requires no csv files. All progress output is passed back to the calling script and parsed into an array for display in php. It's very fast. Have tested with around 30K records with no issues. Of course the row is not very big but for us this works very well. It allows each program to do what it does well.

Rick

 

[lord_icon]

Re: Output

Greetings,
If you output your data as a STREAM your target can be anywhere; a file (CSV), printer or even VDU.

Look into using STREAM

 

[rhoelle]

Thanks Lord Icon,
It would be awsome if you could show me an example. Also, I'm not sure what you mean by VDU as I am still a newbie at this...

Rick