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

I posted a while ago, but did not post sufficent data or a proper explanation of what I was doing. Sorry I'm new to this. We have a stand alone product which is close source and want to do some reporting on it which the software doesn't cater for. Rows within a database contain column value pairs, I need to use these values to build up a SQL query. I had started to develop a script using SQL::Abstrat, but it got bloated pretty quickly as there are many columns.

1. There are multiple COL* columns.
2. Columns may contain wildcards or pipes to designate logical or searches.
3. 'ADDITIONAL' column can contain multiple key value pairs.
4. Dates in the format yyyymmdd

Some example data:
TABNAME COL1 ADDITIONAL + __________________________________ TableA FOO TableA F* TableA FOO|BAR TableB &COLNAME=VALUE TableB &COLNAME=V* TableB &COLNAME=VALUE&COL2=VALU +E TableB &COLNAME=VALUE&COL2=VAL* TableB &COLNAME=VALUE|VALUE2&CO +L2=VALUE TableB Foo|BAR &COLNAME=VALUE|VALUE2&COL2=VA +LUE TableB Foo &DATERAGE=19910101:199101 +31 TableB Foo &DATERAGE=<19910101

I started by writing a script which processed one field at a time, checked for each condition and attempting to build the $where condition based on the results. Since the number of COL columns is actually quite large this copying and pasting approach resulted in a bloated script, very long. I did some reading in the tutorials section and found a note in 'subroutines' about higher order functions (http://perlmonks.com/?node_id=492651) and decided to investigate. I tried to start of a sub to call with each the contents of each COL and process that way. Example code is below, I've excluded the SQL to fetch the data, I have no problems there. I've yet to try the SQL abstract magic within this sub since I'm having some problems:
#!/usr/bin/perl use strict; use warnings; use DBI; use Data::Dumper; use SQL::Abstract; # Subroutine to call sub process(){ my $processed = $_[0]; return sub { if ( @_ ){ #@_ =~ s/^&//; # strip leading & - this only occurs in the + 'additional field' print "recieved: @_\n"; # detect additional with multiple key value pairs. if ( index(@_,'&') >= 0 ){ print "@_ contaisn & character - so it's the additiona +l field with multiple key value pairs"; } # detect ors if ( index(@_,'|') >= 0 ){ print "@_ contains pipe character for ORs"; } # detect wildcards if ( index(@_,'*') >= 0 ){ print "@_ contains whidcard character"; } } } } # skip the actual fetching of the data via SQL query, I have no proble +ms with that part process->($TABNAME) if defined($TABNAME); process->($COL1) if defined($COL1); process->($ADDITIONAL) if defined($ADDITIONAL);

Questions.
1. Why can I print what it has recieved but not detect characters within it using index?
2. Does using a sub routine seem like a sensible approach?
3. I'm having difficulty solving the problem for the ADDITIONAL field where it could contain multiple key value pairs.
All constrictive advice appreciated.

Replies are listed 'Best First'.
Re: Sub Routines and Building SQL statement
by Anonymous Monk on Nov 21, 2011 at 15:03 UTC

    Read perlintro, you're missing a lot of basics , like @_ is an array, not a scalar, and lists are flattened

    Like

    use Data::Dumper; sub fudge { print scalar Dumper( \@_ ); } my @bar = ( 6,6,6 ); fudge(1); fudge(@bar, 1 ); __END__ $VAR1 = [ 1 ]; $VAR1 = [ 6, 6, 6, 1 ];
      Ok that was a very helpful. I've updated my sub and call like so
      sub process(){ my $processed = $_[0]; return sub { if ( @_ ){ print "Sub Process called\n"; my ($colname,$colval) = @_; $colval =~ s/^&//; # strip leading & - this only occurs in + the 'ADDITIONAL field' print "Colname: $colname\n"; print "Colvalue: $colval\n"; # detect ADDITIONAL with multiple key value pairs. if ( index($colval,'&') >= 0 ){ print "$colval contaisn & character - so it's the ADDI +TIONAL field with multiple key value pairs"; my @additional = split('&',$colval); foreach my $addval (@additional){ print "additional has pair: $addval\n"; my @addpair = split('=',$addval); print "$addpair[0] | $addpair[1]\n"; process->($addpair[0], $addpair[1]); # this doesn' +t get called. } } # detect ors if ( index($colval,'|') >= 0 ){ print "$colval contains pipe character for ORs"; } # detect wildcards if ( index($colval,'*') >= 0 ){ print "$colval contains whidcard character"; } } } }

      Calling it now like
      process->("TABLENAME",$TABLENAME) if defined($TABLENAME);

      But, now that I'm calling split for each additional key/value pair in the additional column, I see the values get displayed by the print statement, but the sub doesn't get called.
      is this because I'm calling a sub within the defining sub?

        Sort of. The sub is getting run, or the print statement wouldn't be executed. But you're not doing quite what you're trying to do with the higher-order stuff. Your outer sub returns the inner sub, but you aren't assigning it to anything, just executing it. You never call it directly. So you might as well just use an ordinary subroutine.

        To return an inner sub and call it later, you do something like this:

        sub get_a_processor { # will return a reference to a sub that processes return sub { # your processing stuff } } my $processor = get_a_processor(); # $processor points to inner sub $processor->($TABNAME); #calls the inner sub on $TABNAME

        Aaron B.
        My Woefully Neglected Blog, where I occasionally mention Perl.