comment on

Here is my shell/SQL execution script. It takes an input file that is a mix of DOS commands, SQL statements, and shell commands, generates a shell script to run them, sends it to a Linux server, opens a PuTTY session, runs the script, and retrieves the results. I run it from a Windows PC, and it uses pscp and plink to drive the remote session.

@rem = '--*-Perl-*--
@echo off
perl "%~dpnx0" %1 %2 %3 %4 %5 %6 %7 %8 %9
goto endofperl
@rem ';
#!perl
#line 8
use strict;
use warnings;
use Win32::Clipboard;

my $CLIP = Win32::Clipboard();

my $userpath = $ENV{"HOMEDRIVE"}.$ENV{"HOMEPATH"};

mkdir "$userpath\\sqlcmd";

my @date = localtime();
my $date = sprintf("%04d-%02d-%02d",$date[5]+1900,$date[4]+1,$date[3])
+;
my $time = sprintf("%02d-%02d-%02d",$date[2],$date[1],$date[0]);
my $ts = "${date}_${time}";

my $input = shift;
open IN, "<$input" or die "Can't open input $input\n";

my $output = "sqlout_${input}_$ts.txt";
my $outerr = "plinkerr_${input}_$ts.txt";
my $output1 = "psqlout.txt";

my $shell = "$userpath\\sqlcmd\\shell_$ts.txt";
open SQL, ">$shell" or die "Can't open output $shell\n";
binmode(SQL);

my $sh = "$userpath\\sqlcmd\\sh_$ts.txt";
open SH, ">$sh" or die "Can't open output $sh\n";

my @cmd = ();

print SQL ". `cat /.dshome`/dsenv\n";

# Special vars
my %svar = ();
$svar{"ts"}=$ts;
$svar{"output"}=$output;

# Shell vars
my %shellvar = ();

my $sqlline = "";

my $server;
my $user;
my $passwd;
my $dbconn;
my $dbuser;
my $dbpasswd;

my $commit = "-c";
my $fail = 0;
my $echo = 1;

while (<IN>) {
  next if /^#/;
  s/\s+$//;

  if ($. == 1) {
    my $ufile;
    if (/^\@(.+)/) {
      $ufile = $1;
    } else {
      $ufile = "user.txt";
    }
    print "Using $ufile\n";
    
    open USR, "<$userpath/$ufile" or die "Can't open user file $userpa
+th/$ufile";

    my $server = <USR>;
    my $user = <USR>;
    my $passwd = <USR>;
    my $dbconn = <USR>;
    my $dbuser = <USR>;
    my $dbpasswd = <USR>;
    my $shpath = <USR>;

    $server   =~ s/\s+.*//;
    $user     =~ s/\s+.*//;
    $passwd   =~ s/\s+.*//;
    $dbconn   =~ s/\s+.*//;
    $dbuser   =~ s/\s+.*//;
    $dbpasswd =~ s/\s+.*//;
    $shpath   =~ s/\s+.*// if $shpath;

    $svar{"server"}=$server;
    $svar{"user"}=$user;
    $svar{"passwd"}=$passwd;
    $svar{"shpath"}=$shpath if $shpath;

    print "Unix box   = $server\n";
    print "Unix user  = $user\n";
    print "DB conn    = $dbconn\n";
    print "DB user    = $dbuser\n";
    print "Shell path = $shpath\n" if $shpath;

    if ($shpath) {    
      print SH "chmod 700 $shpath/shell_$ts.sh\n";
      print SH "$shpath/shell_$ts.sh\n";
      print SH "rm $shpath/shell_$ts.sh\n";
      push @cmd, "pscp -pw \"${passwd}\" \"${shell}\" ${user}\@${serve
+r}:${shpath}/shell_$ts.sh\n";
      push @cmd, "plink -batch -pw \"${passwd}\" ${user}\@${server} -m
+ \"${sh}\" >> \"${output}\" 2>${outerr}\n";
    } else {
      push @cmd, "plink -batch -pw \"${passwd}\" ${user}\@${server} -m
+ \"${shell}\" >> \"${output}\" 2>${outerr}\n";
    }
    print SQL "db2 connect to $dbconn USER $dbuser USING $dbpasswd\n";

  }

  next if /^\@/;

  # Interpolate special vars into all commands
  foreach my $key (keys %svar) {
    s/\$\{$key\}/${svar{$key}}/g;
  }

  s/ +$//;
#  s/[^\x20-\x7f£]//; # remove any non-ASCII characters
  if (/^\*/) {        # If it begins with a *...
    s/^\*//;          # trim off the *
    print SQL "$_\n"; # ...and add it to the shell script
    if (/^(\w+)=(.+)/) {  # If it's a shell variable...
      $shellvar{$1}=$2;   # ...add it to a hash
    }
    next;
  } elsif (/^!/) {
    s/^!//;
    # Interpolate Unix shell vars into Command Prompt commands
    while (/\$\{(\w+)\}/ and defined $shellvar{$1}) {
      my $sv = $1;
      s/\$\{$sv\}/${shellvar{$sv}}/g;
    }
    s/\$\{output\}/${output}/g;
    if (/>/) {
      push @cmd, "$_\n";
    } else {
      push @cmd, "$_ >> ${output}\n";
    }
    next;
  } elsif (/^%/) {
    if (/%COMMIT ON/i) {
      $commit = "-c";
    } elsif (/%COMMIT OFF/i) {
      $commit = "+c";
    } elsif (/%FAIL ON/i) {
      $fail = 1;
    } elsif (/%ECHO ON/i) {
      $echo = 1;
    } elsif (/%ECHO OFF/i) {
      $echo = 0;
    }
    next;
  }
  
  $sqlline .= " " . $_;
  if (substr($sqlline,-1) eq ";") {
    unless ($sqlline =~ /^\s*--/) {
      chop $sqlline;
      $sqlline =~ s/^\s+//;
      $sqlline =~ s/\s+/ /g;
      my $rsql = 0;
      if ($sqlline =~ /^=/) {        # Convert the output to record vi
+ew if prefixed by =
        $sqlline =~ s/^=//;          # trim off the =
        $rsql = 1;
      }
      my $ssql = 0;
      if ($sqlline =~ /^\+/) {        # Store the result in ${result} 
+if prefixed by +
        $sqlline =~ s/^\+//;          # trim off the +
        $ssql = 1;
      }
      my $csql = 0;
      if ($sqlline =~ /^\,/) {        # Store the result comma-separat
+ed in ${result} if prefixed by ,
        $sqlline =~ s/^\,//;          # trim off the ,
        $ssql = 1;
        $csql = 1;
      }
      if ($rsql) {
        print SQL "db2 $commit -v \"$sqlline\"|perl -e 'while (\<\>) {
+ if (\$colseps eq \"\" and \/\^\-\/) { \$colseps = \$_; \$colnames = 
+\$prev; my \$col; my \$off = 0; foreach (split \/ \/,\$colseps) { my 
+\$len = length; push \@col_offs, \$off; push \@col_lens, \$len; my \$
+colname = substr(\$colnames, \$off, \$len); \$colname =\~ s\/ +\$\/\/
+; \$longname = length(\$colname) if length(\$colname) \> \$longname; 
+\$off += \$len + 1; } print; next; } if (\/\^\$\/) { \$colseps = \"\"
+; } if (\$colseps ne \"\") { chomp; ++\$rec; print \"Record \$rec\\n\
+"; for (my \$i=0; \$i\<\@col_offs; ++\$i) { \$val=substr(\$_, \$col_o
+ffs[\$i], \$col_lens[\$i]); \$val=substr((\" \"x20).\$val,-20) if len
+gth(\$val)\<20 and \$val=~/\^ /; printf \"\%\-\${longname}.\${longnam
+e}s = \%s\\n\", substr(\$colnames, \$col_offs[\$i], \$col_lens[\$i]),
+ \$val; } print \"\\n\"; } else { print; } } continue { chomp; \$prev
+ = \$_; }'\n";
      } elsif ($ssql) {
        print SQL "result=`db2 $commit -x \"$sqlline\"`\n";
        if ($csql) {
          print SQL "result=`echo \${result}|sed 's/ /,/g'`\n";
        }
      } else {
        print SQL "psqlout=`db2 $commit -v \"$sqlline\"`\n";
        if ($echo) {
          print SQL "echo \"\${psqlout}\"\n";
        }
#        if ($fail) {
#          print SQL "psqlcheck=\${psqlout}\n";
#          print SQL "if [ \"`expr match \\\"\${psqlcheck}\\\" '.*\\( 
+selected\\).*'`\" -ne \" selected\"]; then echo ERROR; exit 1; fi\n";
#        }
      }
    }
    $sqlline = "";
  }
}
print SQL "db2 connect reset\n";
close SQL;
close SH;
push @cmd, "cp -m \"${output}\" \"${output1}\"\n";
my $die;
foreach (@cmd) {
  #print "Executing $_\n";
  print "Executing $1\n" if /^(\S+)/;
  $die = 0;
  last if /^exit$/i;
  system($_);
  if (-e ${outerr}) {
    open ERR, "<${outerr}";
    while (<ERR>) {
      print;
      if (/^Access denied$/) {
        $die = 1;
      }
    }
    die if $die;
  }
}
unlink $outerr;

__END__

:endofperl
[download]

Here is an example script:

@uatuser.txt
set schema da03;

*data_src_id=40

SELECT * 
FROM BATCH_AUDIT 
WHERE data_src_id = ${data_src_id}
order by BATCH_ID
with ur;

*regions=1,2,3

,select region_name from regions where region_id in ( ${regions} );

*echo ${result}
[download]

* in front of a line designates a shell command. The * is removed and the remainder is executed.

, in front of an SQL command (terminated with a ;) instructs the script to convert the SQL results into a comma-separated list for use in a future IN list. Results are stored in the $results shell variable.

There is a risk of password leakage as it needs to write the DB2 user name and password into the script that is executed on the Linux machine, and it writes the Linux user name and password to a temporary file on the local PC (it writes it to the user's Documents and Settings so other users can't see it). It also picks up these user names and passwords from a config file, as I couldn't be bothered typing them in every time I want to run a script. This file is also in the user's Documents and Settings directory, here is an example:

cs-uat-etl02          <= Unix server name
username              <= put your user name here
password              <= put your password here
ZP_D2TB               <= DB2 connection name
DSXMUAT               <= put the database user name here
dbpassword            <= put the database password here
/data/scratch/phil    <= this is where the temporary shell script is w
+ritten
[download]

Critique and suggestions are welcome. The line after "if ($rsql) {" is pretty hideous, it pushes the results through a Perl one-liner that I reslly should push out to an external Perl script file, but I want the whole thing to be self-contained and be run from a Windows PC with no external dependencies.

To Do: Failure detection, the "%fail on" command is intended to abort if any SQL errors occur, perhaps with the exception of the "No rows to delete" error.

In reply to SQL script processor and executor by PhilHibbs

Are you posting in the right place? Check out Where do I post X? to know for sure.
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
Want more info? How to link or How to display code and escape characters are good places to start.


Think about Loose Coupling
	PerlMonks