@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 $userpath/$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}\@${server}:${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 view 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-separated 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_offs[\$i], \$col_lens[\$i]); \$val=substr((\" \"x20).\$val,-20) if length(\$val)\<20 and \$val=~/\^ /; printf \"\%\-\${longname}.\${longname}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