In the set of scripts I'm working on now I often have to interrogate a database to get three records from three different tables, in the form of three hashrefs, which I then do a wide range of different operations on. The main place for code re-use here is the getting of the data from the db, not what I do with it. Now the problem is, my subroutine needs to return a hashref which I can then use freely after the subroutine is done. One way I tried to do it was:

First I have a module, let's call it booker.pm, which looks like this:

package booker;

require Exporter;
our @ISA = ("Exporter");

sub
Get_Booking_Course_Event
{
    my $BookingID = shift;

    my $dbh = DBI->connect("DBI:mysql:database=foo", "bar", "baz") or 
+die $DBI::errstr;
    my $sth;

    $sth = $dbh->prepare("SELECT * FROM booking WHERE BookingID = $Boo
+kingID") or die $dbh->errstr;
    $sth->execute();
    $ref_booking = $sth->fetchrow_hashref;

    $sth = $dbh->prepare("SELECT * FROM event WHERE EventID = $ref_boo
+king->{'EventID'}") or die $dbh->errstr;
    $sth->execute();
    $ref_event = $sth->fetchrow_hashref;

    $sth = $dbh->prepare("SELECT * FROM course WHERE CourseID = $ref_b
+ooking->{'CourseID'}") or die $dbh->errstr;
    $sth->execute();
    $ref_course = $sth->fetchrow_hashref;
}

our @EXPORT = qw/Get_Booking_Course_Event/;

1;
[download]

And use booking; in each of the scripts.

My first attempt at calling this subroutine was

    my $ref_booking;
    my $ref_event;
    my $ref_course;
    &Get_Booking_Course_Event($ref->{'BookingID'});
    # the arg in this sub cd be one of a number of 
    # things depending on where in the script we are
    do_stuff_with($ref_booking, $ref_course, $ref_event);
[download]

Now, the main problem with that is it doesn't work. Because the three variables I declare before calling the subroutine don't get into the namespace of the subroutine... let alone out of it again with something useful in them.

My next try was to call it without declaring any variables and then use the variables from the module's namespace, with fully-qualified variable names:

    &Get_Booking_Course_Event($ref->{'BookingID'});
    # the arg in this sub cd be one of a number of 
    # things depending on where in the script we are
    do_stuff_with($booker::ref_booking, $booker::ref_course, $booker::
+ref_event);
[download]

That seems to work. But I still don't totally like it. For one thing, I quite often call this sub more than once in the same script. And even though I have error checking, I'm slightly worried that something might go wrong later on, and then the second time I do my operations with $booker::ref_booking etc, I'm still working on the first ones.

What I'd really like is to be able to localise the variables that the subroutine returns. Or even better, have them returned ready-localised. Which is what finally led me to the desire to be able to make my subroutine be in the same name space as the calling scope. Then I'd declare $ref_booking and the others in the subroutine. And I also wouldn't have to keep creating and destroying my database handle, because I could use one I created earlier, which is in scope at the point where I call the sub.

So that's what I'd like to be able to do. And I don't think I can. So... tell me I can; or tell me I'm crazy to want to.