The code I had was an extract from the entire script
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Data::Dumper;
use SQL::Abstract;
my %where = ();
sub process(){
my $processed = $_[0];
return sub {
if ( @_ ){
my ($colname,$colval) = @_;
$colval =~ s/^&//; # strip leading & - this only occurs in
+ the 'additional field'
print "Colname: $colname\n";
print "Colvalue: $colval\n";
unless($colval =~ m/[&|*]/){
print "normal value: $colval \n";
$where{$colname}=$colval;
}
# detect ors
if ( index($colval,'|') >= 0 ){
print "$colval contains pipe character for ORs\n";
#$where{'-or'}{ {$colname=$colval}
}
# detect wildcards
if ( index($colval,'*') >= 0 ){
# convert * Wildcard to Oracle wildcard
$colval =~ s/\*/%/g;
$where{$colname}{'-like'} = $colval;
}
# detect date range
if( index($colval, ':') > 0 ){
print "found date: key: $colname - value: $colval\n";
my ( $from, $to ) = split(':', $colval);
print "from: $from\n";
print "to: $to\n";
$where{$colname}{'-between'} = [$from,$to];
}
}
}
}
my $cnt=0;
my $sql = SQL::Abstract->new;
my $table_name = "TABNAME";
my $facility = "FAC1";
my $col_type = "BEG";
my $col_stype = "FOO|BAR";
my $rec_number = "BAZ/*";
my $additional = "&Key1=VAL1&Key2=VAL2&DATE1=20111111:20111112";
process->("col_type",$col_type) if defined($col_type);
process->("col_stype",$col_stype) if defined($col_stype);
process->("rec_number",$rec_number) if defined($rec_number);
# process additional:
$additional =~ s/^&//;
if ( index($additional,'&') >= 0 ){
print "$additional contains & character - so it's the additional f
+ield with multiple key value pairs\n";
my @additional = split('&',$additional);
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]);
}
}else{
print "Single value in additional:\n";
my @addpair = split('=',$additional);
print "$addpair[0] | $addpair[1]\n";
process->($addpair[0], $addpair[1]);
}
my($stmt, @bind) = $sql->select($table_name, 'count(rowid)', \%where);
print Dumper $stmt;
print "\n";
print Dumper @bind;
print "\n";
The output is (I sware)
Colname: col_type
Colvalue: BEG
normal value: BEG
Colname: col_stype
Colvalue: FOO|BAR
FOO|BAR contains pipe character for ORs
Colname: rec_number
Colvalue: BAZ/*
Key1=VAL1&Key2=VAL2&DATE1=20111111:20111112 contains & character - so
+it's the additional field with multiple key value pairs
additional has pair: Key1=VAL1
Key1 | VAL1
Colname: Key1
Colvalue: VAL1
normal value: VAL1
additional has pair: Key2=VAL2
Key2 | VAL2
Colname: Key2
Colvalue: VAL2
normal value: VAL2
additional has pair: DATE1=20111111:20111112
DATE1 | 20111111:20111112
Colname: DATE1
Colvalue: 20111111:20111112
normal value: 20111111:20111112
found date: key: DATE1 - value: 20111111:20111112
from: 20111111
to: 20111112
Can't use string ("20111111:20111112") as a HASH ref while "strict ref
+s" in use at ./short.pl line 51.