Beefy Boxes and Bandwidth Generously Provided by pair Networks httptech
Problems? Is your data what you think it is?
 
PerlMonks  

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

by Anonymous Monk
on Nov 22, 2011 at 09:35 UTC ( #939404=perlquestion: print w/ replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I'm about done with my SQL::Abstract script, everything seems to be working apart from the following:
$colname="COLB"; $from="20110101"; $to="20110131"; $where{$colname}{'-between'} = [$from,$to];
This results in the error:
Can't use string ("20110101:20110131") as a HASH ref while "strict ref +s" in use at ./harrumble.pl line 81.
I don't understand what this means or what I need to do to fix it.

Comment on SQL::Abstract 'between' clause (about done)
Select or Download Code
Re: SQL::Abstract 'between' clause (about done)
by Anonymous Monk on Nov 22, 2011 at 09:50 UTC

    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

      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.

        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

Re: SQL::Abstract 'between' clause (about done)
by mrstlee (Beadle) on Nov 22, 2011 at 10:24 UTC
    Somewhere in your code
    $where
    is assigned to the string you see in the error message. It should be easy enough to spot - if not you could post more of the code so we can see how $where is set up.
      Sorry, I posted a script and the data and the output above
Re: SQL::Abstract 'between' clause (about done)
by ww (Bishop) on Nov 22, 2011 at 11:37 UTC

    In your reply to the initial critique of the OP, we find:

    "067: my $additional = "&Key1=VAL1&Key2=VAL2&DATE1=20111111:20111112";"

        ?

    I also don't understand why your OP quotes the error message from the snippet as coming from line 81, while your later post -- which you at least imply is the entire script -- puts the problem at 51.
      I don't understand what you mean, i posted a short script which reporduces the problem so someone else can copy and paste and run it to reproduce the error.
Re: SQL::Abstract 'between' clause (about done)
by TJPride (Pilgrim) on Nov 25, 2011 at 11:40 UTC
    Sorry for asking possibly stupid questions, but why use a complicated library for something like this when you can just write the query directly and insert safed values? Something like this:

    use strict; use warnings; ### Matches negatives, decimals sub isNum { return $_[0] =~ m/^-?(?:\d*\.)?\d+$/; } ### Converts non-safe characters in string sub safeString { my $val = $_[0]; $val =~ s/(['"\\\x00\x1a])/\\$1/g; return $val; } ### Determines type of value, converts string sub safeValue { my $val = $_[0]; return "'" . safeString($val) . "'" if length($val) == 0 || !isNum($val); return $val; } ### Safes all inserted values ### Inserts strings with quotes if {key} is used ### Inserts without quotes if [key] is used sub queryMysql { my ($query, $args) = @_; $query =~ s/\[(\w+)\]/safeString($args->{$1})/eg; $query =~ s/{(\w+)}/safeValue($args->{$1})/eg; return $query; } my %values = ( 'table' => 'tablename', 'x' => '123', 'y' => 'alpha', 'z' => -45.67, 'colname' => 'COLB', 'from' => '20110101', 'to' => '20110131' ); print queryMysql(' UPDATE [table] SET x = {x}, y = {y}, z = {z} WHERE [colname] BETWEEN {from} AND {to}', \%values); ### UPDATE tablename ### SET x = 123, y = 'alpha', z = -45.67 ### WHERE COLB BETWEEN 20110101 AND 20110131
    I hacked this together years ago for situations where I don't want to bother messing with the ? ? and separate array format of DBI. Could probably take a few minutes and write a function for generating inserts and updates as well.
Reaped: Re: SQL::Abstract 'between' clause (about done)
by NodeReaper (Curate) on Nov 25, 2011 at 12:58 UTC
Reaped: Re: SQL::Abstract 'between' clause (about done)
by NodeReaper (Curate) on Nov 28, 2011 at 12:36 UTC

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://939404]
Approved by Corion
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (13)
As of 2014-04-18 13:12 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (468 votes), past polls