Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation

DB search results using DBI...execute

by jamroll (Beadle)
on Mar 04, 2017 at 20:58 UTC ( #1183668=perlquestion: print w/replies, xml ) Need Help??

jamroll has asked for the wisdom of the Perl Monks concerning the following question:

The code should suffice (the following snippet is in a module i call "". i recognize the code is not "secured". That will come, please, avoid telling me it's insecure - i already know that. i just need this to first work as described in the comments of the subroutine. i wrote this code...and confused the bejeepers outta myself lol
######################## sub sql_execute($$) { #* # runs an SQL statement on the supplied db. # must be connected, and must disconnect to commit changes. # a reference to an array of hash references will be # returned unless there's only one item in the array, then # we give a hash reference instead of a one element array # reference (which would contain only one hash reference). # three different rvals: scalar, array ref, or hash ref # - dependent on # of results, or kind of sql statement (insert, c +reate, update, select, etc) #* my ($dbh, $sql) = @_; # the dbh && the SQL statement if (not sql_db_valid($dbh)) { # sql_db_valid is a VERY simple check +- it just checks if $db is a hash ref or not) return 0; # invalid dbh } my @arr; if ($sql =~ /^insert|update|delete/i) { my $rv = $dbh->do($sql); return $rv; # returns whatever $rv->do($sql) returns } else { my $rv = $dbh->prepare($sql); if ($rv) { $rv->execute(); # now, grab all the results from the query, and dump them into a +n array as hash references to each "hit" while (my $row = $rv->fetchrow_hashref) { push @arr, $row; } # if the array has only one element, then, it's kinda pointless +to return a ref to the array # so instead, let's just return a hash reference. if ($#arr eq 1) { my $hashRef = $arr[0]; # this ought to be a hash reference, no +? return $hashRef; # a hash reference when there is only one arr +ay element } else { if (not @arr) { return 0; # 0 on error } else { # and array reference when there is more than 1 element in t +he array # each element is a reference to a hash. my $arrayRef = \@arr; return $arrayRef; # an array reference when the array is > 1 } # if (not @arr) ... else ... } # if (@arr eq 1) ... else ... } else { return 0; # error in SQL statement! } # if ($rv) ... else ... } # is ($sql =~ /^insert|update|delete/i) #usage: my $rv = sql_execute($dbh, $sql); } my main script:
. . . use pm::search; my $rv = "content-type: text/html\n\n"; my $dbh = sql_connect("ns.db"); . . . my $st = "eye_clr"; # field to search for my $sv = 1; # value to search for $rv .= "Testing <i><b>search_item</b></i><br>\n"; my @users = get_users($dbh, 1); # gets JUST a list of user ID's - whic +h uses the above "sql_execute" subroutine and works perfectly! $rv .= "searching " . @users . " users: "; foreach my $uid (@users) { $rv .= "$uid, "; } $rv =~ s/, $/<br>\n/; # replace the last comma and space with a <br> a +nd a new line $rv .= ""; # the following should return JUST a hash reference! but +returns convoluted results which just baffle me my @results = search_item($dbh, \@users, $st, $sv) . "\n"; $rv .= "Number of array elements: <b>" . @results . "</b><br>\n"; foreach my $result (@results) { $rv .= "$result<br>\n"; } . . . print $rv; exit 1;
now, when i run this, the "Number of array elements:" says 1 (which is unexpected....i figured i would be getting a hash reference back - not an array reference with one stinkin element in it! and then, what's more confusing? The last "foreach" loop prints "3"! I expected it to print out three user ID's...not just a clearly there's something i'm missing. in some cases this works as I want it to. for instance, i have no issue getting a list of user ID's....that works perfectly. in other cases, though, i get these very strange and unexpected results. can anyone review the above code, and perhaps suggest why i get a "3" and not the expected 3 user ID's??? Jarett

Replies are listed 'Best First'.
Re: DB search results using DBI...execute
by huck (Prior) on Mar 04, 2017 at 21:44 UTC

    use strict; use warnings; use Data::Dumper; my @arr=({a=>1,b=>2},{a=>3,b=>4},{a=>5,b=>6}); print '$#arr :'.$#arr."\n"; print 'scalar(@arr):'.scalar(@arr)."\n"; print Dumper \@arr; my @results = rvar('hash') . "\n"; print Dumper \@results; print "Number of array elements: <b>" . @results . "</b><br>\n"; my @results2 = rvar('arr') . "\n"; print Dumper \@results2; print "Number of array elements: <b>" . @results2 . "</b><br>\n"; sub rvar { my $mode=shift; my @arr=({a=>1,b=>2},{a=>3,b=>4},{a=>5,b=>6}); if ($#arr eq 2) {print "text eq works but weird\n"; } if ($mode eq 'hash') { my $hashRef = $arr[0]; return $hashRef; } elsif ($mode eq 'arr') { my $arrayRef = \@arr; return $arrayRef; } return 0; }
    $#arr :2 scalar(@arr):3 $VAR1 = [ { 'b' => 2, 'a' => 1 }, { 'b' => 4, 'a' => 3 }, { 'a' => 5, 'b' => 6 } ]; text eq works but weird $VAR1 = [ 'HASH(0x3f7f2c) ' ]; Number of array elements: <b>1</b><br> text eq works but weird $VAR1 = [ 'ARRAY(0xb321bc) ' ]; Number of array elements: <b>1</b><br>
    First, $#arr returns the index of the last element, not the count.
    Second all you ever return from sql_execute is a scalar, it may be a hashref, or a arrayref, or a number but it is always a scalar.
    Third, in my @results = search_item($dbh, \@users, $st, $sv) . "\n"; you force scalar mode with the . for concatenation, and the "\n" gets placed into the @results array appended to scalar result as its only item.
    Fourth, you never showed us search_item so i am just sortof guessing but if search_item actualy did return an array the size of the array would be concatenated with the "\n" since scalar context was forced

    Edit: added "index of the" to "returns the index of the last element"

      my oops - here's the "search_item" code...
      sub search_item($$$$) { #* # to search for a list of user IDs which match one criteria (ie: eye +_clr=1) # searches can only happen with the logged in user # we must not include the logged in user in the search results! # nor should we include those that are banned or security level 888 +or higher #* my ($db, $usersRef, $name, $value) = @_; # a DBH && a reference to a + list of users to search && the name of the item to search for && the + value the named item must be my @searched; # a list of UIDs which match the given name=value pair +s my $query = "select ID from users where $name='$value';"; my $resultsRef = sql_execute($db, $query); if ($resultsRef) { if (ref $resultsRef eq "ARRAY") { # we have multiple hits! my @array = @$resultsRef; foreach my $hit (@array) { # $hit is a hash reference. my %hash = %$hit; push @searched, $hash{ID}; } } else { # we only have one hit! my %hash = %$resultsRef; push @searched, $hash{ID}; } } # it is very possible to have an empty "searched" array. # this is totally ok. return @searched; # an array of uid's which matched the specified cr +iteria, minus the logged in user's uid #usage: my @results = search_item($db, \@uList, "eyes", "1"); }
      reviewed my code, and sure enough, that damn "\n" is there! now i feel REALLY dumb! lol - TYVM, huck, for pointing that out! i don't have a CLUE how i did that. Likely due to commenting, and modifications, and just missed it. now, i get a list of UID's, and not just a 3. Thank you, thank you, thank you. I think i can finally move onto the next stage. MOST appreciated, Sire.

        Dont forget to fix the if ($#arr eq 1) { test too. its either if ($#arr eq 0) { or if (sclalar(@arr) eq 1) {

Re: DB search results using DBI...execute
by kcott (Archbishop) on Mar 05, 2017 at 06:40 UTC

    G'day jamroll,

    Welcome to the Monastery.

    In scalar context, @array evaluates to the number of elements. In list context, you'll get the actual elements. Instead of concatenating with '.' (which forces scalar context), you can just embed @array within interpolating quotes.

    Here's an example showing all three of those scenarios:

    $ perl -E 'my @x = ({}); say ">" . @x . "<"; say ">", @x, "<"; say ">@ +x<"' >1< >HASH(0x7fb6c68040b0)< >HASH(0x7fb6c68040b0)<

    — Ken

      ty, Ken. I was directed to that error. thought it was a typo when posting this msg, discovered it was in my code, and that fixed the problem. very much appreciate your input!
Re: DB search results using DBI...execute
by poj (Abbot) on Mar 04, 2017 at 21:25 UTC

    Use Data::Dumper to show the structure

    use Data::Dumper; print '<pre>'; print Dumper \@results; print '</pre>';

    The code for search_item() looks to be more relevant to your question than sql_execute().

      yes! ty for that. not entirely certain how that helps...perhaps i'm misguided. i'm asking why the code gives back a 3...not if search_item() is more relevant than sql_execute(). frankly, your reply, although highly appreciated, makes little sense to me.
        my @results = search_item($dbh, \@users, $st, $sv) . "\n"; is slightly incorrect, btw - and not sure how the "\n" got in's not there in my actual code. so, it should read just: my @results = search_item($dbh, \@users, $st, $sv);
Re: DB search results using DBI...execute
by clueless newbie (Curate) on Mar 04, 2017 at 23:34 UTC
      thanks for the link. packed with information! too much info, really. I'll stick with the prototyping. it would be just awful to have to recode my modules. it's working, and I see little need to do away with the prototypes.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1183668]
Approved by kevbot
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (2)
As of 2022-08-16 23:09 GMT
Find Nodes?
    Voting Booth?

    No recent polls found