Question: Redirection of stdout to scalar variable

jujiro_eb has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks, A few days ago monk Kyle helped me out with redirecting stdin to Oracle's sql*plus program. It worked like a charm. Now I am stuck about capturing the stdout messages generated by sql*plus to a scalar variable. I used IO::String and it did not work. Then I tried the following code, which also failed to capture the stdout from sql*plus. Interestingly enough, I was able to capture simple print statement's output using both IO::String as well as the code below.

use strict;
use warnings;

my $TEMP1;
my $OLD_STDOUT_HANDLE;
my $NEW_STDOUT_HANDLE;
open NEW_STDOUT_HANDLE, ">&STDOUT";
close STDOUT;
open STDOUT, ">", \$TEMP1;

open my $pipe_fh, '|-', "sqlplus.exe -s scott/tiger\@butthead"
    or die "Can't open pipe: $!";
print {$pipe_fh} <<"END_OF_SQL"
set echo off
set lines 1000
set trims on
set serverout on size 999999
set feed off
exec dbms_output.put_line('Hello There');
exit;
END_OF_SQL
;
close $pipe_fh;

close STDOUT;
open STDOUT, ">&NEW_STDOUT_HANDLE";
print "6.\n";
print $TEMP1;
[download]

I would once again greatly appreciate your help.

Regards.

Ash

Comment on Question: Redirection of stdout to scalar variable Download Code

Replies are listed 'Best First'.
Re: Question: Redirection of stdout to scalar variable by ikegami (Patriarch) on Mar 20, 2009 at 21:41 UTC
IPC::Run provides an easy solution. `use strict; use warnings; use IPC::Run qw( run ); my @cmd = qw( sqlplus.exe -s scott/tiger@butthead ); my $in = <<'__END_OF_SQL__'; set echo off set lines 1000 set trims on set serverout on size 999999 set feed off exec dbms_output.put_line('Hello There'); exit; __END_OF_SQL__ run \@cmd, \$in, \my $out; print $out;` [download]	[reply] [d/l]
Re^2: Question: Redirection of stdout to scalar variable by jujiro_eb (Sexton) on Mar 20, 2009 at 23:01 UTC
Thank you Ikegami! Since I am using Active Perl (Win32), IPC::Run did not show up in my package manager. I managed to use IPC::Run3 for the same. Thanks so much for the help. By the way the reason for all this is that dbms_output.put_line adds an extra LF at the end. I needed to strip it out. So the final code looks like this: `use strict; use warnings; use IPC::Run3; my @cmd = qw( sqlplus.exe -s scott/tiger@butthead ); my $in = <<'__END_OF_SQL__'; set echo off set lines 1000 set trims on set serverout on size 999999 set feed off exec dbms_output.put_line('Hello There'); exit; __END_OF_SQL__ run3 \@cmd, \$in, \my $out; print $out;` [download] Thanks again everybody! Ash	[reply] [d/l]
Re^3: Question: Redirection of stdout to scalar variable by syphilis (Archbishop) on Mar 21, 2009 at 02:24 UTC
IPC::Run did not show up in my package manager `ppm install http://www.bribes.org/perl/ppm/IPC-Run.ppd` Cheers, Rob	[reply] [d/l]
Re: Question: Redirection of stdout to scalar variable by almut (Canon) on Mar 20, 2009 at 21:34 UTC
`open STDOUT, ">", \$TEMP1;` See my reply to Using a tied scalar as an in memory file for why this generally cannot be used to capture the stdout/stderr output of external programs. You need to setup pipes both ways to the I/O of the external program, which can for example be achieved with IPC::Open3. But even in this case it's sometimes non-trivial to get right (due to potential buffering and deadlock problems, depending on how the external program is behaving...).	[reply] [d/l]
Re: Question: Redirection of stdout to scalar variable by merlyn (Sage) on Mar 20, 2009 at 21:13 UTC
IO::String cannot capture child process stdout. -- Randal L. Schwartz, Perl hacker	[reply]
Re: Question: Redirection of stdout to scalar variable by Illuminatus (Curate) on Mar 20, 2009 at 21:14 UTC
The problem is that the pipe open that you are doing actually forks off another process to do the sqlplus. The output of sqlplus is in that process, not yours. Take a look at IPC::Open3. It allows pipe opens where you get to control stdin and stdout of the pipe.	[reply]
Re: Question: Redirection of stdout to scalar variable by kyle (Abbot) on Mar 23, 2009 at 15:11 UTC
If you're going to be doing a lot of work with your SQL server from Perl, you'd be doing yourself a favor to learn about DBI (with DBD::Oracle) rather than trying to do everything through pipes to and from sqlplus. Here's a short example of how to query with DBI: `my $dbh = DBI->connect( "dbi:Oracle:host=$host;sid=$sid", $user, $passwd ); # The "?" is what's called a placeholder my $sql = 'SELECT * FROM table WHERE x = ?'; my $sth = $dbh->prepare( $sql ); $sth->execute( $search_value_of_x ); while ( my $row = $sth->fetchrow_hashref() ) { # $row->{field} has the value in that field for this row }` [download]	[reply] [d/l]