in reply to Can get values from db but cannot get related values from the relevant lookup table

Not sure about your primary question (though it seems like it's a db issue, not perl, from first glance of the error message) ... but you might be interested in SQL::Abstract
use SQL::Abstract; my $sa = SQL::Abstract->new; # in _lookup(): my $query = "SELECT $indy->{string} FROM $indy->{table} ". "WHERE $indy->{index}=$row->{$fieldName}"; my ($sql, @bind) = $sa->select( $indy->{table}, [$indy->{string}], { + $indy->{index} => $row->{$fieldName} } ); my ($val) = $db->selectrow_array( $sql, {}, @bind ); return $val; # in _show_bugs(): my ($query,@bind) = $sa->select('Bug', \@fieldset, {bugid=>[split( +/,/,$args)]} ); my $sth = $db->prepare( $query ); eval { $sth->execute(@bind); };
Hmm.. actually, rewriting both (and maybe this auotmagically fixes your problem?):
sub _show_bugs(){ my $args = shift; my @headers = map { "*$_*" } qw/ ID Title Priority Assignment Stat +e /; my $fmt = "| %s | %s | %s | %s | %s |\n"; my $sql =<<EOF; SELECT 'fog' || b.bugid, b.sTitle, p.sPriority, u.sFullName, s.sStatus FROM Bug b LEFT JOIN Priority p ON p.ixPriority = b.bugpri LEFT JOIN Persion_l u ON u.ixPerson = b.bugassign LEFT JOIN Status s ON s.ixStatus = b.bugstate WHERE b.bugid IN ($args) EOF my $db = _connect(); my $rows = $db->fetchall_arrayref($sql); # note: is slurping in a +ll rows return join '', map { sprintf $fmt, @$_ } \@headers, @$rows; }
This way should be much more efficient -- let the database do the lookup work for you instead of making a bunch of calls (note that _lookup() isn't needed)... It's also a lot clearer -- you can see just from reading the SQL what it's doing..
note: you may or may not want LEFT joins.. (probably not, but i put them so as not to modify functionality)
note: it would be better to do the $args w/placeholders
  • Comment on Re: Can get values from db but cannot get related values from the relevant lookup table
  • Select or Download Code