dhudnall has asked for the wisdom of the Perl Monks concerning the following question:

Hello, My problem that I have involves searching SQL statements to see if there is possibly a NESTED SQL statement within another statement. What I want my program to do is to be able to call the program and enter the file to be parsed along with a keyword (such as SELECT or DELETE). The program will open the file that will contain a list of SQL statements that I have pulled using grep. There will be one SQL statement per line. I already have that functionality in another program. What I NEED is to be able to call my program, give it the name of the SQL file containing the SQL statements, and enter a keyword (such as SELECT) and it either return a statement saying "Yes there is a nested SQL in this file" or "No the file is fine". The following code doesn't work but I quickly just jotted down some notes to give you an idea. Please help if you can. I ran the program below and it was stuck in a loop. 
#!/usr/bin/perl

use warnings;
#use strict;

#This is where we read in the arguments from the command line
my $file_name = shift;
my $String = shift;
my $CharPos = 0;
my $TheLine = 0;
my $LineLen = 0;
my $counter = 0;
#my $nestedSelects = false;

#This is where we read in the file and output to the file
open(INFILE, $file_name) or die "Can't open file\n";

#while reading from the file...
while(<INFILE>)
{

        #make all lowercase characters uppercase
        tr/a-z/A-Z/;

        #Save the line's contents
        $TheLine = $_;

        #may need to convert the line to a list??? HERE

        #Gets the length of the line
        $LineLen = length($TheLine);

        #until we reach the end of the line
        until($CharPos == $LineLen)
        {
                while($counter!=2)
                {
                        #for every element in the line
                        foreach $x ($TheLine)
                        {
                                #if the string equals any of the eleme
+nts in the
 line
                                if($String eq $x)
                                {
                                        #add one to the counter
                                        $counter++;
                                }#end if
                        }#end of the for statement

                        #if two of the same words were found in the se
+ntence (ne
sted selects)
                        if($counter >=2)
                        {
                                print "There is a nested SQL in this f
+ile\n";
                                #nestedSelects = true;
                                exit;
                        }#end if
                        else
                        {
                                $CharPos = $CharPos +1;
                        }
                }#end of second while loop
        }#end of the until

        #sets the counter back to 0 for the next line
        $counter=0;
}#end of the first while

[download]

Replies are listed 'Best First'.
Re: searching for multiple strings in a line
by toolic (Bishop) on Jul 10, 2007 at 16:14 UTC
    I'm not sure why the script is stuck in a loop, but I do see one problem in: 
    foreach $x ($TheLine)

    [download]
    This loop will execute exactly once, setting $x to the value $TheLine. As your comment hinted at, you definitely want $TheLine to be an array, not a scalar variable.

    If you can provide a sample of what your input file looks like, and an example input $String, that would make debugging a lot easier.

[reply]
[d/l]
      Well, my input file would consist of pulled SQLs such as the following: 
      Select LTrim(RTrim(a.eeds_emp_id)) from whatever && Select Max(campaig
+n_key) from whatever......

      [download]
      THEN: I would enter a KEYWORD such as SELECT and it would search a line such as teh one above....say that it found the word SELECT...continue searching the line to see if there are any OTHER select statements in the line...if it is...then it count the line as having TWO Select statements and return a message saying...."this file contains SQLs with nested statements...etc. I know this may seem useless but for my project it is very necessary. With GREP, it is pulling the entire line and saying it is ONE SQL whereas I NEED it to pull that SQL and then run this program through the file to make sure I didn't miss any NESTED SQLS.
[reply]
[d/l]
        split will separate your input line based upon the input keyword (such as SELECT). If SELECT appears more than once in the line, then the @things array will have more than one element, and you will get your message output. 
        #!/usr/bin/perl

use warnings;
use strict;

#This is where we read in the arguments from the command line
my $file_name = shift;
my $String    = shift;

#This is where we read in the file and output to the file
open(INFILE, $file_name) or die "Can't open file\n";

#while reading from the file...
while (<INFILE>) {
    $_ = uc();
    my @things = split(/$String/);
    if ($#things > 0) {
        print "There is a nested SQL in this file\n";
        exit;
    }
}

        [download]
[reply]
[d/l]
[select]

        One problem is that you can have multiple SELECT statements without having subselects, like so:

        SELECT A, B FROM FOO WHERE ...
UNION
SELECT C as A, D as B FROM BAR WHERE ...

        [download]

        (Unless, of course, you consider the select statements as subselects of the union statement.) There may be other examples.

        Depending on your needs, you may need a more exhaustive parser--SQL::Parser might assist, but I can't comment any further as I've never used it.

        ...roboticus

[reply]
[d/l]
[select]
Re: searching for multiple strings in a line
by swampyankee (Parson) on Jul 10, 2007 at 16:30 UTC

    My first question is "Why are you doing this?"

    My first suggestion is to read The Perl Regular Expression Tutorial. Obviously, you would do this with a regexp, possibly something like*:

    @cmd = ($statement =~ m/\b(SELECT|UPDATE|DELETE)\b/);

    [download]
    Then, @cmd would have to be checked by your rules defining "nested."

    * "Something like this" means that this is 1) not tested and 2) not guaranteed to even vaguely resemble what you want. I've been reading too many software licenses...

    emc

    Any New York City or Connecticut area jobs? I'm currently unemployed.

    There are some enterprises in which a careful disorderliness is the true method.

    —Herman Melville
[reply]
[d/l]
Re: searching for multiple strings in a line
by roboticus (Chancellor) on Jul 11, 2007 at 10:39 UTC

    dhudnall:

    If your needs are particular, and your database supports it, you might have better luck submitting your statements to the SQL engine for parsing only, and then parse the execution plan. The (few) SQL engines I'm familiar with return plans with a syntax that's a bit simpler to parse through than SQL is.

    ...roboticus

[reply]
Re: searching for multiple strings in a line
by benmaynard (Initiate) on Jul 10, 2007 at 17:12 UTC
    I am assuming there is only 1 statement per file? You are just looking for more than one control statement? ie 2 selects, two deletes or one of each? I would most likely take a slightly different approach to most, I am sure you will get a dozen replies on the benefits of using maps for this, but a simple method could be something like this: 
    open(INFILE, "filename.txt");

my %hash;

while(<INFILE>)
{
   chomp;
   foreach my $e (split / /, $_) {
      defined($hash{uc($e)}) ? $hash{uc($e)} = 1 :              
                               $hash{uc($e)} += 1;
   }
}

if ( ($hash{'SELECT'} > 1) || ($hash{'DELETE'} > 1) )
{
   print "There is a nested SQL in this file\n";
}
elsif ( ($hash{'SELECT'} >= 1) && ($hash{'DELETE'} >= 1) )
{
   print "There is a nested SQL in this file\n";
}
else
{
   print "File is fine\n";
}

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom