Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

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


Comment on Re^2: SQL::Abstract 'between' clause (about done)
Select or Download Code
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?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://939418]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (7)
As of 2015-07-04 17:08 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (60 votes), past polls