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

Hello monks, I have a module that our company code uses to establish a database handle. It specifically checks to see which databases are available, etc.. example below... 
sub new_dbh {
    ....
    ....
    Establish the database handle
    Check other stuff, etc

}

[download]
Later we are establishing our statement handle and executing code.... example... 
$dbh = new_dbh({host=>'blablah'},{AutoCommit=>1});
$customer_handle="fred";
my $sth = $dbh->prepare(qq{select id from customer where handle=?});
$sth->execute($customer_handle);

[download]
So far, so textbook.....
What we are now needing to do is to append comments to our MySQL strings. Specifically the user id info

We can get that info by pulling out $ENV{USER} variable and the goal is to turn the SQL statement into one where the user id is is pre-pended to the statement... in our above example it needs to execute as ...
/* userid:jwilkie */ select id from customer where handle="fred";
We set the statement handle in about a million different places in our code and prepending the user id on every single statement handle is not realistic. Since the database handle is established in new_dbh, is there a way once the database handle is established that when the statement handle is executed, that we then pre-pend this user info in the new_dbh subroutine?

Definitely let me know if this isn't clear enough or if I can provide more info. The reason for this is so that we can get user id's in MySQL's slow query log. MySQL runs one user but we have users identifiable by their user id when they run scripts from the CLI.

Replies are listed 'Best First'.
Re: Perl DBI: Overloading statement handle
by moritz (Cardinal) on Nov 08, 2010 at 16:32 UTC
    Just write a subclass to the class on which you call the ->prepare method. In the subclass, add the prefix to the string, and re-dispatch to superclass method with $self->SUPER::prepare(@arguments)

    Then in the new_dbh sub you can rebless the created statement handle with bless.

    This should work, but it's an ugly hack, and should only temporarily used for debugging.

    Update: here's a code example (untested, but should work roughly like this): 

    #!/usr/bin/perl
use strict;
use warnings;
{
    package My::DBI::st;
    our @ISA = qw(DBI::st);

    sub prepare {
        my ($self, $str, @args) = @_;
        $self->SUPER::prepare("/* userid $ENV{USER} */ $str", @args);
    }
}

sub new_dbh {
    ...
    my $dbh = DBI->connect(...);
    return bless $dbh, 'My::DBI::st';
}

    [download]
    Perl 6 - links to (nearly) everything that is Perl 6.
[reply]
[d/l]
[select]
      Wow! Now that was totally a nice follow up! I also understand this more because of it, many many thanks! J
[reply]
Re: Perl DBI: Overloading statement handle
by kcott (Archbishop) on Nov 08, 2010 at 16:44 UTC

    If new_dbh is in a subclass of DBI, it's pretty straightforward. There's an example of overriding the prepare method in: Subclassing the DBI.

    -- Ken

[reply]
[d/l]
[select]
      Ken, this is a great beginning. new_dbh is not a subclass though, it's a subroutine (or maybe my knowledge of the terms is off, I admit to being fairly new with packages). new_dbh is a subroutine in MyModule::Database I can see how in the example given (thanks so much for this) where the differences are between ::db and ::st

      If I setup a prepare subroutine in MyModule::Database then that would take precedence over DBI's prepare? I would just prefer than any solution not have me tracking down every $sth in our codebase and fix it (which your example helps with greatly) or having to re-code the entire MyModule::Database module to a separation of ::db and ::st

      ......or do I need to just setup a subclass like this...
      package MyModule::Database::db;
  use vars qw(@ISA);
  @ISA = qw(DBI::db);

  sub prepare {
    my ($dbh, @args) = @_;
    my $sth = $dbh->SUPER::prepare(@args)
        or return;
    $sth->{private_mysubdbi_info} = { foo => 'bar' };
    return $sth;
  }

      [download]
      I understand my question might not even make sense. I do enjoy this the more I learn it.
[reply]
[d/l]

        sherab, your terminology seems fine - I think you just misread my post. I wrote: "... is in a subclass ..." not "... is a subclass ...".

        You have thrown me a bit with "I see I can't edit/delete the above. ...". I'm not sure if you're referring to the examlpe moritz provided or the one I linked to in the DBI documentation.

        Anyway, the basic idea is that you do not re-code the one million my $sth = $dbh->prepare(...) lines you currently have; nor do you re-code any existing $dbh = new_dbh(...) lines. Instead, you make a single change to your new_dbh routine which I envisage to be as simple as:

        # Old:
# return $dbh;

# New:
return bless $dbh, 'My::DBI::st';

        [download]

        Then add the code provided by moritz above that.

        Hopefully, that answers some of your questions. If you have more, feel free to ask.

        -- Ken

[reply]
[d/l]
[select]
        I see I can't edit/delete the above. The example that moritz gave at the top: WOW! It's just what I was after.
[reply]
Re: Perl DBI: Overloading statement handle
by lostjimmy (Chaplain) on Nov 08, 2010 at 16:41 UTC
    You already have a subroutine for getting a new database handle, so why not create a subroutine for getting a new statement handle? 
    sub new_sth {
   my $userid = ...;
   my $dbh = shift;
   my $statement = shift;

   $statement = "/* userid:$userid */ $statement";

   return $dbh->prepare($statement);
}

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom