UdoraSync

Glue script to take a sql stmt and pass back data via named pipe

#!/usr/local/bin/perl

# 11/07/2001 Ian  Add write output to debug file, move open pipe into loop

use POSIX;
use DBI;
use DBD::Oracle;
use strict;

$ENV{[[ORACLE_BASE]]}="/info/app/oracle";
$ENV{[[ORACLE_HOME]]}="/info/app/oracle/product/8.1.5";
$ENV{[[NLS_LANG]]}="american_america.[[WE8ISO8859P1]]";
$ENV{[[ORA_NLS33]]}="/info/app/oracle/product/8.1.5/ocommon/nls/admin/data";

my $fld_delim = chr(253);
my $crlf_delim = chr(252);
my $debug = 1;
my $data;
my $dbh;
my $i;
my $infile;
my $j;
my $outfile;
my $pidfile;
my $rc;
my $req;
my $sid;
my $statement;
my $sth;
my $table;

$sid=$ARGV[0];
if ($sid eq "") {
   print "Usage: /usr/local/bin/udora_sync.pl <sid>\n";
   exit (1);
}

$infile = "/samba_share/oracle/$sid/in";
$pidfile = "/samba_share/oracle/$sid/pid";
open (PID, ">$pidfile") or die "Cannot open $pidfile for writing: $!\n";
print PID $$;
close (PID);

my $USER="scott";
my $PASS="tiger";

if( !( $dbh = DBI->connect($sid,$USER,$PASS, 'Oracle') ) )
{
   die "Cannot connect database\n$DBI::errstr";
}

# This is needed for those long sql returns...
$dbh->{[[LongReadLen]]}=4094;

open(INF, "<$infile") or bug ("Cannot open $infile: $!\n");

while(1) {
   # Read the request as 1 line.  Note: if the request if > 512 bytes
   # and multiple processes are writing the pipe there is potential
   # for "scrambling" requests that are received at the same time
   $req=<INF>;
   next unless defined $req;
   bug ("request=$req");
   chomp($req);

   # Break the request into two pieces file-name__sql-request
   if ($req =~ /(.*)__(.*)/) {
        $outfile=$1;
        $statement=$2;
        next unless defined $statement;
        open(OUTF, ">$outfile") or bug ("Cannot open $outfile\n");
        process();
        close(OUTF);
   } else {
        bug ("Malformed request: $req\n");
   }
}

sub process {
   if( !( $sth = $dbh->prepare("$statement") ) )
   {
      output("0\nCannot prepare statement\n$DBI::errstr\n");
      return;
   }

   if ($statement =~ /select/i) {
      # Select statements return rows
      $rc=$sth->execute;
      if( ! $rc )
      {
         output("0\nCannot process statement\n$DBI::errstr\n");
         return;
      }
      my $names = $sth->{NAME};

      $table = $sth->fetchall_arrayref;
      if( $#{$table} < 0 )
      {
         output("0\nNo rows returned:$#{$table}\n");
         return;
      }

      # Output number of rows
      output($#{$table}+1);
      output("\n");

      for ($i=0 ; $i <= $#$names ; $i++)
      {
         output($names->[$i]);
         if ($i != $#$names) {output($fld_delim);}
      }
      output("\n");

      for $i ( 0 .. $#{$table} )
      {
         for $j ( 0 .. $#{$table->[$i]} )
         {
            $data = $table->[$i][$j];
            $data =~ s/\r\n/$crlf_delim/g;
            output($data);
            if ($j != $#{$table->[$i]}) {output($fld_delim);}
         }
         if ($i != $#{$table}) {output("\n");}
      }
   } else {
      # It's an update or insert statement
      $rc=$dbh->do($statement);
      if ($rc eq "0E0") {
         output("0\n0 rows updated\n");
         return;
      } else {
         output("$rc\n$rc rows updated\n");
         $dbh->commit;
      }
   }

   $sth->finish;
}

sub output {
   my ($string) = @_;
   print OUTF $string;
   #bug ($string."\n");
}

sub bug {
   my ($msg) = @_;
   if ($debug) {
       print localtime() . ":" . $sid . ":" . $msg;
   }
}