Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

Re^2: SQL::Abstract 'between' clause (about done)

by Anonymous Monk
on Nov 22, 2011 at 10:26 UTC ( #939418=note: print w/replies, xml ) Need Help??

in reply to Re: SQL::Abstract 'between' clause (about done)
in thread SQL::Abstract 'between' clause (about done)

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 ./ line 51.

Replies are listed 'Best First'.
Re^3: SQL::Abstract 'between' clause (about done)
by Anonymous Monk on Nov 22, 2011 at 10:53 UTC

    The problem in a nutshell

    $ cat junk use strict; use warnings; use diagnostics; my %where = (); $where{something} = 4; $where{something}{else} = 5; __END__ $ perl junk Can't use string ("4") as a HASH ref while "strict refs" in use at jun +k line 5 (#1) (F) Only hard references are allowed by "strict refs". Symbolic references are disallowed. See perlref. Uncaught exception from user code: Can't use string ("4") as a HASH ref while "strict refs" in us +e at junk line 5. at junk line 5

    Either $where{something} is a hashref or it isn't, it can't be both, so pick one

    You can pick

    #~ $where{$colname}=$colval; $where{$colname}{colval} =$colval;

    There are other problem, but I'll leave that to others

      From the POD I see
      my %where = ( user => 'nwiger', completion_date => { -not_between => ['2002-10-01', '2003-02-06'] } );
      I don't see where I can use this functionality with the two choices you mentioned to pick from. Thanks for the advice by the way. If I can get this part working I am keen to know what other problems you have found

        To create the data you see in the POD with scalar assignements you would have to write this:

        $where{user}= 'nwiger'; $where{completion_date}{-not_between}= ['2002-10-01', '2003-02-06'];

        What would be a problem is if you used this as well:

        $where{completion_date}= 5;

        Because $where{completion_date} can't be pointer to a hash and scalar value at the same time. But nothing prevents you from storing further scalar values or hash pointers in $where{thingy} or $where{stuff} or $where{completion_date}{depperstuff}

        A big help might be to watch Data::Dumper output of %where after you execute each of the above statements

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://939418]
and the monks are mute...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (9)
As of 2017-07-21 11:53 GMT
Find Nodes?
    Voting Booth?
    I came, I saw, I ...

    Results (321 votes). Check out past polls.