Populating an array via a DBI call

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

This post is more about trying to find a cleaner way to accomplish my task. I use DBI a lot but I am curious if there is a better way to populate and array with the returned value when only a single field is returned. Example ---

SELECT `UserName` FROM Account a, asscAccountAccountGroup aaag, asscRoleAccountGroup arag, Role r WHERE a.Id = aaag.AccountId AND aaag.AccountGroupId = arag.AccountGroupId AND arag.RoleId = r.Id AND r.Name = 'ROLE_REVIEWER';

I have always acomplished this by doing the following --

 my @regUsers;
 
my $sth = $dbh->prepare("SELECT `UserName` FROM Account a, asscAccount
+AccountGroup aaag, asscRoleAccountGroup arag, Role r WHERE a.Id = aaa
+g.AccountId AND aaag.AccountGroupId = arag.AccountGroupId AND arag.Ro
+leId = r.Id AND r.Name = 'ROLE_REVIEWER';") || die "Cannot prepare th
+e query :$!";
$sth->execute() || die "Cannot execute the query :$!";

while ( my @userRows = $sth->fetchrow_array() ) { push @regUsers, @use
+rRows; }

print join("\n",@regUsers);
[download]

Which always works as expected but I'd like to simplify that code if possibe.

Doing the following seems to return a list of references but is close --

my @users = $dbh->selectall_array("SELECT `UserName` FROM Account a, a
+sscAccountAccountGroup aaag, asscRoleAccountGroup arag, Role r WHERE 
+a.Id = aaag.AccountId AND aaag.AccountGroupId = arag.AccountGroupId A
+ND arag.RoleId = r.Id AND r.Name = 'ROLE_REVIEWER'");

print join("\n",@users);
[download]

Thanks for the suggestions

Comment on Populating an array via a DBI call - simplified Select or Download Code

Replies are listed 'Best First'.
Re: Populating an array via a DBI call - simplified by Your Mother (Archbishop) on Mar 29, 2017 at 18:07 UTC
This is a great chance to bump one of the most classic nodes here: DBI recipes.	[reply]
Re^2: Populating an array via a DBI call - simplified by edimusrex (Monk) on Mar 29, 2017 at 19:27 UTC
Great thread. Seems the answer to my question is as followed --- my @accounts = @{$dbh->selectcol_arrayref("SELECT `UserName` FROM Acco +unt a, asscAccountAccountGroup aaag, asscRoleAccountGroup arag, Role +r WHERE a.Id = aaag.AccountId AND aaag.AccountGroupId = arag.AccountG +roupId AND arag.RoleId = r.Id AND r.Name = 'ROLE_REVIEWER'")}; print join("\n",@accounts); [download] Thanks again for pointing me in the right direction	[reply] [d/l]
Re^3: Populating an array via a DBI call - simplified by runrig (Abbot) on Mar 29, 2017 at 20:56 UTC
If you don't really need the entire list of results in an array, you can just fetch one at a time like so: `my $sth = $dbh->prepare($sql); $sth->execute(); $sth->bind_col( 1, \my $user_name ); while ( $sth->fetch() ) { print "$user_name\n"; }` [download]	[reply] [d/l]
Re^2: Populating an array via a DBI call - simplified by edimusrex (Monk) on Mar 29, 2017 at 19:22 UTC
Great! Thanks	[reply]
Re: Populating an array via a DBI call - simplified by Discipulus (Canon) on Mar 30, 2017 at 08:23 UTC
As YourMother said DBI recipes is a must read. I'd add others with some external tutorials by gmax Tricks with DBI very old but usefull and GMAX DBI tutorial GMAX The fine art of database programming also Databases made easy by GrandFather DBI placeholders wit multiple values handling dbi error good example L* There are no rules, there are no thumbs.. Reinvent the wheel, then learn The Wheel; may be one day you reinvent one of THE WHEELS.	[reply] [d/l]
Re: Populating an array via a DBI call - simplified by ablanke (Monsignor) on Apr 01, 2017 at 10:11 UTC
Hi, my personal choice for a easy to use DBI interface is DBIx::Simple. For your task i took the example from DBIx::Simple::Examples: `my @names = $db->query('SELECT name FROM people WHERE id > 5')->flat;` [download]	[reply] [d/l]
[OT] Re^2: Populating an array via a DBI call - simplified by erix (Prior) on Apr 01, 2017 at 12:55 UTC
Slightly `[OT]`... my @names = $db->query('SELECT name FROM people WHERE id > 5')->flat; I thought that looked nice so I had a look at it. But when compared to the 'normal' DBI way it turns out there is really not much of an advantage. The line is a little shorter (++) but you get a dependency (--) and lose quite a bit of speed (--). perl -MData::Dumper -MDBI -MDBIx::Simple -e ' my $db1 = DBI->connect; # (uses defaults to connect) my $db2 = DBIx::Simple->connect; use Benchmark qw( cmpthese ) ; my $COUNT = -20; print "-- COUNT [$COUNT]\n"; cmpthese( $COUNT, { selectrow_arrayref => sub { my @arr = @{ $db1->selectrow_arrayref( + "select i from generate_series(1,10) as f(i) where i > 5" ) }; }, dbix_query_flat => sub { my @arr = $db2->query( "select i from +generate_series(1,10) as f(i) where i > 5")->flat; }, }); ' -- COUNT [-20] Rate dbix_query_flat selectrow_arrayref dbix_query_flat 5608/s -- -23% selectrow_arrayref 7293/s 30% -- [download] As always when I look closer at ORMy things (mainly because many people seem to be fond of ORMs), I don't think it's worth it.	[reply] [d/l] [select]
Re: [OT] Re^2: Populating an array via a DBI call - simplified by ablanke (Monsignor) on Apr 02, 2017 at 12:08 UTC
Hi erix, first of all ++ for your post. That are the choices you have to made. Slightly OT... Yes, i tried to see the OP from a different angle. As always when I look closer at ORMy things... I don't think it's too ORMy.	[reply]

Back to Seekers of Perl Wisdom