How I return sql in perl

Posted by Chris on July 15th, 2009 filed in programming

I often don’t want to instantiate a whole dbi stack just for some quick work.  So in the include module I tend to haul around, there’s a “return_sql” function.  It takes 5 or 6 arguments (the last being an optional debug): the sql statement itself, db,host,user,pass in that order.  After that, it will execute the sql.  Fun use:  if there’s only one row returned, it just returns that one as a scalar – otherwise it returns an array of comma-seperated values (like I said, this is for small quick stuff).

sub  return_sql()
{
    my $sql=shift || return;  #the or is super useful for this!
    my $db=shift || return;
    my $host=shift || '';
    my $user=shift || '';
    my $pass=shift || '';
    my $debug=shift || 0;  #for optional debugging, default is 0
    print "SQL=$sql\n" if $debug;
    #from here I (depending on the host) either use the dbi, or simply echo to mysql's path and get the result
    my $dbh=DBI->connect("DBI:mysql:$db:$host",$user,$pass)
            or die "Couldn't connect to $db:$host,$user: $!\n";
    my $sth=$dbh->prepare($sql)
            or warn "Couldn't prepare sql: $!\n";
    $sth->execute()
            or warn "Couldn't execute sql\n$sql\nReason: $!\n";
    my @ret_ary=();
    #could do this a bunch of other ways, but this is really really easy to explain.  :)
    #while you're fetching a row (i.e. for each row you get) that's put into @val
    #join it into a temp line seperated by commas, and push that onto an array
    while( my @val=$sth->fetchrow_array()){push(@ret_ary,join(",",@val));}
    #then, if there's only one row returned (if the length of the new array is 1), return only it
    return $ret_ary[0] if scalar(@ret_ary)==1;
    #otherwise, return the whole array and use away!
    return @ret_ary;
}

Use in practice:

use include;
my @db=('db','host','user','pass',$debug); #debug being 0 or non-0 :)
#first, let's try just one row
my $name=&include::return_sql("select name from mytable limit 1",@db);
#then, let's get a list
my @names=&include::return_sql("select name from mytable where 1",@db);
#a little more complicated
foreach my $row(&include::return_sql("select name,id from mytable where 1",@db))
{
  my ($name,$id)=split(",",$row);
  print "name=$name,id=$id\n";
}

And that’s my useful snippet for the day!

Leave a Comment