Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Comment on

( #3333=superdoc: print w/ replies, xml ) Need Help??

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

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}

* 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

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

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • Outside of code tags, you may need to use entities for some characters:
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others meditating upon the Monastery: (14)
    As of 2014-09-19 15:10 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      How do you remember the number of days in each month?











      Results (140 votes), past polls