Conversion script: link fix

2015-02-26T23:48:56Z

link fix

New page

Glue script to take a sql stmt and pass back data via named pipe
<PRE>
#!/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;
}
}
</PRE>

UdoraSync - Revision history

Conversion script: link fix