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

[Anonymous Monk]

This results in the error:

The code you posted does not result in that error

I don't understand what this means or what I need to do to fix it

If you're going to be using strict (and you should), you should read perldoc strict and Why you should use strict, 101 reasons to use strict;...

In short

$ perl -Mdiagnostics -Mstrict -e " my $f = 'asdf'; $$f{3}++;
Can't use string ("asdf") as a HASH ref while "strict refs" in use at 
+-e line 1 (#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 ("asdf") as a HASH ref while "strict refs" in
+ use at -e line 1.
 at -e line 1

[Anonymous Monk]

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.

[Anonymous Monk]

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

[Anonymous Monk]

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