database query with POE::Wheel::Run

[former33t]

I have an external program I am required to use to issue SQL statements to a database. The way the query works is that I issue a statement and when the result set changes, the program will dump back out to stdout. This keeps me from having to poll the database. I don't have direct access to the DB (that would be preferable) so I'm stuck with this.

The shell command looks like this:

MyDbQueryProgram "SELECT * FROM MY_TABLE;"

It runs fine from the command line, but I'm having a bear of a time getting it to work from POE. Any ideas on how to escape the string here? POE::Wheel::Run does a "sh -c" to run the command. Just running from the shell, this works:

sh -c 'MyDbQueryProgram "SELECT * FROM MY_TABLE;"'

Does anyone have any ideas? Thanks in advance for your help.

#!/usr/bin/perl

use warnings;
use strict;

use POE;
use POE::Wheel::Run;
use POE::Filter::Line;
use POE::Filter::Stream;

$| = 1;
my $outCT = 0;
my $program = "MyDbQueryProgram";
my @progargs = ("SELECT * FROM MY_TABLE;");
POE::Session->create(
 inline_states => {
  _start => \&startup,
  stdout => \&stdout,
  stdin => \&stdin,
  stderr => \&stderr,
 }
);
POE::Kernel->run();

sub startup {
 my $heap = $_[HEAP];
 
 my $wheel = POE::Wheel::Run->new(
  Program => $program,
  ProgramArgs => \@progargs,

  StdinEvent => 'stdin',
  StderrEvent => 'stderr',
  StdoutEvent => "stdout",     
  StdoutFilter => POE::Filter::Stream->new(),
 );


 print "Wheel ID is ".$wheel->ID."\n";
 print "Wheel's PID is ".$wheel->PID."\n";
 $heap->{program} = $wheel;
}


sub stdout {
 my ($heap, $input, $wheelID) = @_[HEAP, ARG0, ARG1];
 print "$input\n";
 $outCT++;
 print $outCT;
}

sub stderr {
 my ($op, $errnum, $errstr, $wheel_id) = @_[ARG0, ARG1, ARG2, ARG3];
 print "stderr\n";
 print "OP = $op\nERRNUM = $errnum\nERRSTR = $errstr\nID = $wheel_id\n
+";
}

sub stdin {
 print "stdin\n";
}

[Corion]

Looking at the relevant part of POE::Wheel::Run, it looks like the following:

    # $program is what you pass in as the Program key
    # $progargs is the array reference of arguments
    else {
      if (ref($program) eq 'ARRAY') {
        exec(@$program, @$prog_args)
          or die "can't exec (@$program) in child pid $$: $!";
      }
      else {
        exec(join(" ", $program, @$prog_args))
          or die "can't exec ($program) in child pid $$: $!";
      }
    }

So basically, your program gets run as one string, so you need to either supply the shell-safe quoting yourself (not so good idea) or avoid the shell alltogether by passing Program and ProgramArgs as array references (very good idea):

my $wheel = POE::Wheel::Run->new(
  Program => [ $program ],             # changed!
  ProgramArgs => \@progargs,

  StdinEvent => 'stdin',
  StderrEvent => 'stderr',
  StdoutEvent => "stdout",     
  StdoutFilter => POE::Filter::Stream->new(),
 );

should work, or the worse solution is to quote the program arguments twice:

$progargs = (q{"SELECT * FROM MY_TABLE;"});

See perlop for more about the q{} operator.

[rcaputo]

Try Program => [ $program ].

POE::Wheel::Run tries to preserve Perl's exec() semantics. So if Program is an arrayref, it's executed as exec(@$program). This bypasses the the shell -c (which is actually supplied by Perl's exec()).

This is documented under the Program parameter in POE::Wheel::Run.

I hope this helps.

[finidi20]

Hi I would like to see ur program that prints the result set to STDOUT if the results are different, do u still have it? Thanx

[rcaputo]

Sorry, I haven't logged in for years, so I missed this question. I don't recall the code in question, and I expect I'm too late anyway.

It may be another three years until I log back in. Please contact me directly if you need a response sooner.