Extract SQLS from PowerBuilder DBTrace

# pb_sqls.pl - Script to extract SQLs from dbtrace file generated by PB application
# Usage: pb_sqls.pl -f <dbtrace file name> > <output-file>
#           Note: dbtrace.log needs to be saved to a ANSI format first,.

# Author: Sam Varadarajan
# Date: 10/04/2010
# Purpose: This script extracts the SQLs from a dbtrace file. dbtrace.log file is created when a PB application is
#           run with TRACE on for the DBMS.

# Modification History:
# Copyright, Sam Varadarajan, 2010 - 2014
#use warnings;
#use strict;

#globals
use vars qw/ %opt /;

my $file = "c:\\windows\\dbtrace.log";
#main()

init(); # get command line options

if ($opt{'f'}) {
   $file = $opt{'f'};
}
process_file($file, 'SELECT,INSERT,DELETE,UPDATE');

#end main


#
# Command line options processing
#
sub init()
{
    use Getopt::Std;
    my $opt_string = 'f:';
    getopts( "$opt_string", \%opt ) or usage();
    usage() if $opt{h};
}


#
# Message about this program and how to use it
#
sub usage()
{
  print STDERR << "EOF";

    This program extracts SQLs from a DBTrace file generated by PB application

    usage: $0 [-h] [-f <file>]

    -h          : this (help) message
   -f          : File name to extract from
    example: $0 -f <filename>

EOF
    exit;
}

sub process_file()
{
 my $path = @_[0];
 my $SQLOP= ();
 @SQLOP = split(',', @_[1]); # SELECT/INSERT/UPDATE/DELETE
 #print join('|', @SQLOP);
 my $sql_block_found = 0;
 my @lines = ();
 my $fcount = 0;
 my $line = '';
 my $result = '';
 #open (LOGFILE, $path)  or die "$0: Cannot open $path: $!\n";
 open LOGFILE, "<:encoding(UCS-2LE)", $path or die "Can't open $path $!\n";
 print "--".$path."\n";
 while (<LOGFILE>) {
  chomp;
  my $str = $_;
  #print $str;

   if ($str =~ /PREPARE:/) {
      #print $str;
      $sql_block_found = 1;
      next;
   }
   elsif (($sql_block_found == 2) &&
               ($str =~ /GET AFFECTED ROWS:/ || /BIND SELECT OUTPUT BUFFER/ || /DESCRIBE:/ || /EXECUTE:/)) {
      #print $str;
      $result = join("\n", @lines);

      $result =~ s/FROM/\nFROM/;
      $result =~ s/VALUES/\nVALUES/;
      $result =~ s/SET/\nSET/;
      $result =~ s/WHERE/\nWHERE/;
      $result =~ s/AND/\nAND/;
      $result =~ s/ORDER BY/\nORDER BY/;
      $result =~ s/GROUP BY/\nGROUP BY/;
      $result =~ s/\([0-9.]+ MS \/ [0-9.]+ MS\)//;
      if ($result && $result !~ /FN_SYSDATE/ && $result !~ /NAV_/)
      {
          print $result.";\n\n";
      }
      $sql_block_found = 0;
      @lines = ();
      $fcount = 0;
      next;
   }
   elsif ($str =~ "^\(.*\):")
   {
      $str =~ s/^\(.*\):[ ]*//;
      OP: foreach $op(@SQLOP) {
         if ($str =~ /^$op/i) {
            $sql_block_found = 2;
            last OP;
         }
      }
   }

   if ($sql_block_found == 2)
   {
      #print $str."\n";
      push(@lines, $str);
   }
   elsif ($str =~ /\/\*[ ]*[0-9]{2}.*\*\//)
   {
      #remove /* */
      $str =~ s/\/\*[ ]*/-- /;
      $str =~ s/\*\///;
      push(@lines, $str);
   }
 }
 close(LOGFILE);
}