use strict;
use SQL::Library;
use SQL::Statement;
use SQL::Parser;
use SQL::Eval;
use Data::Dumper;

#text file which has the SQL statements
my $sqlfile =q{TEST_SQL.TXT};
#use SQL::Library::BEGIN to read in the statements
my $sqllib = new SQL::Library { lib => $sqlfile };
#create a new SQL::Parser object
my $parser = SQL::Parser->new();
$parser->{RaiseError}=0;
$parser->{PrintError}=0;
#process each SQL Statement in turn (to test which ones do or do not work)
for my $sql_stmt_name($sqllib->elements){
    print "Processing $sql_stmt_name\n";
    my $sql_stmt=$sqllib->retr($sql_stmt_name);
    next if($sql_stmt=~/\[\%/); #some statement use template variables and are therfore not ANSI standard
    #parse the statement
    my $stmt;
    eval{
    	$stmt = SQL::Statement->new($sql_stmt,$parser)
    }; next if $@; #some SQL statements can't be parsed (e.g BEGIN TRAN )
    next if(! scalar($stmt->params)); # we are not intersted if there are no placeholders
    my @bind_field_names=(); #array to hold the field names which map to the placeholders
    if($sql_stmt=~m{^\s*INSERT\s*INTO | #insert statement
                    ^\s*UPDATE\s*       #update statement
                    }ixms){
       my @icolumns=map{$_->name}$stmt->columns();
       my @ivalues=$stmt->row_values();
       for my $n(0..$#ivalues){
           if($ivalues[$n] eq q{?}){
             push @bind_field_names, $icolumns[$n];
           }
       }
    }

    if(scalar($stmt->params) == scalar(@bind_field_names)){
    	# we got all the bind field names
    	print q{OK: SQL Statement }.$sql_stmt_name.q{ has the following fields requiring data binding:}.join(q{,},@bind_field_names).qq{\n};
    }else{
        print q{FAILED: SQL Statement has }.scalar($stmt->params).q{ bindings and we have }.scalar(@bind_field_names).qq{ fields!\n };
     	# the number of bind fields is less than the place holders - we probably have some
     	# placeholders in the 'WHERE' clause(s).
        my $where = $stmt->where();
        print Dumper($where);  #how do I extract the fields with parameter bindings???
   }
}

exit;