Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery

SQL script processor and executor

by PhilHibbs (Hermit)
on Jul 27, 2012 at 10:17 UTC ( #984012=CUFP: 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_$\n"; print SH "$shpath/shell_$\n"; print SH "rm $shpath/shell_$\n"; push @cmd, "pscp -pw \"${passwd}\" \"${shell}\" ${user}\@${serve +r}:${shpath}/shell_$\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.

Replies are listed 'Best First'.
Re: SQL script processor and executor
by hbm (Hermit) on Jul 27, 2012 at 13:38 UTC

    A few formatting comments:

    I think qq and combined statements are clearer here:

    if ($shpath) { #print SH "chmod 700 $shpath/shell_$\n"; #print SH "$shpath/shell_$\n"; #print SH "rm $shpath/shell_$\n"; #push @cmd, "pscp -pw \"${passwd}\" \"${shell}\" ${user}\@${serv +er}:${shpath}/shell_$\n"; #push @cmd, "plink -batch -pw \"${passwd}\" ${user}\@${server} - +m \"${sh}\" >> \"${output}\" 2>${outerr}\n"; print SH "chmod 700 $shpath/shell_$\n", "$shpath/shell_$\n", "rm $shpath/shell_$\n"; push @cmd, qq[pscp -pw "${passwd}" "${shell}" ${user}\@${server} +:${shpath}/shell_$\n], qq[plink -batch -pw "${passwd}" ${user}\@${server} -m + "${sh}" >> "${output}" 2>${outerr}\n]; } else { ...

    You have many "if it begins with X, trim off X" cases that can be simplified:

    #if ($sqlline =~ /^=/) { # Convert the output to record v +iew if prefixed by = # $sqlline =~ s/^=//; # trim off the = if ($sqlline =~ s/^=//) { # trim off '=' and convert recor +d view if prefixed by = $rsql = 1; }

    And maybe:

    #$server =~ s/\s+.*//; #$user =~ s/\s+.*//; #$passwd =~ s/\s+.*//; #$dbconn =~ s/\s+.*//; #$dbuser =~ s/\s+.*//; #$dbpasswd =~ s/\s+.*//; #$shpath =~ s/\s+.*// if $shpath; map{$_ && s/\s.*//} $server,$user,$passwd,$dbconn,$dbuser,$dbpassw +d,$shpath;
      map{$_ && s/\s.*//} $server,$user,$passwd,$dbconn,$dbuser,$dbpasswd,$s +hpath;

      Map goes through the trouble of creating and returning a result for you. If you don't intend to use that result, you should probably not use map for the purpose.

      I would do this instead:

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

      I'm on the fence about treating $shpath separately. There's no reason to test all the variables (according to the OP code), just the $shpath variable. So testing all the other values is wasted CPU cycles. On the other hand, CPU cycles may not be important in this situation.

        "Map goes through the trouble of creating and returning a result for you."

        That has not been true for a while now. "map in void context is no longer expensive. map is now context aware, and will not construct a list if called in void context." -- perl581delta

      Ooh I like the map thing. And... I've been aware for some time now that I should get used to qw and qq. I've kind of ignored them because they just look a little bit odd to me.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: CUFP [id://984012]
Front-paged by Arunbear
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (8)
As of 2016-10-24 06:45 GMT
Find Nodes?
    Voting Booth?
    How many different varieties (color, size, etc) of socks do you have in your sock drawer?

    Results (303 votes). Check out past polls.