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

Hi Monks,

Read some posts here that were very similar to my goal, so came here seeking enlightenment. In short, am trying to move some DBI calls (prepare, execute) into a module, but the execution fails when calling "execute" (please see below). Would like to have both $dbh and $sth available to the CGI (e.g. outside of the module). The module snippet looks like this: 

package My::DBU;
use strict;
use warnings;
use diagnostics;
use CGI::Carp;
use DBI;

sub new {
    my $class = shift;
    my $self = bless {}, $class;
    my $dbh = shift || return undef;
    $self->{dbh} = $dbh;
    return $self;
}

sub begin_work{
    my $self = shift;
    $self->{dbh}->begin_work;
}

sub execute {
    my $self = shift;
    my $query = shift;
    my $sth = $self->{dbh}->prepare($query);
    my $rows = $sth->execute or die $DBI::errstr;;
    return \$sth;
}

[download]

and the CGI snippet like this: 

my $dbh = DBI->connect('DBI:mysql:db:localhost',
  ${dbuser},${dbpass}) or confess "$DBI::errstr<br>";

my $dbu = new My::DBU $dbh or confess "$DBI::errstr <br>";

$dbu->begin_work();

print "BEFORE ERR<br>";
my $sth = $dbu->execute( \$dbh, \$query, \$errmsg );
print "NEVER GETS HERE<br>";

[download]

with the error

You have an error in your SQL syntax; check the manual that correspond
+s to your MySQL server version for the right syntax to use near 'REF(
+0xaaa3700)' at line 1 at /usr/lib/perl5/site_perl/5.8.8/My/DBU.pm lin
+e 96.\n

[download]

The query "select id from ${table}" is being used while attempting to resolve this issue. Links mentioned above are: 

http://www.perlmonks.org/?node_id=324671
http://www.perlmonks.org/?node_id=870145

[download]

Many thanks for your time / suggestions.

Replies are listed 'Best First'.
Re: passing objects as references ?
by mr.nick (Chaplain) on Dec 08, 2011 at 15:37 UTC
    The problem appears to be this line:

    my $sth = $dbu->execute( \$dbh, \$query, \$errmsg );

    My::DBU::execute is looking for one parameter (beyond $self), a scalar, $query. $dbh is being pulled from $self, so you don't need to pass it in. And I'm not sure what passing $errmsg into the method is supposed to do.

    This would probably work:

    my $sth = $dbu->execute( $query );

    Note that returning the statement handle is kind of odd since you retrieve the $rows inside the method.

    mr.nick ...

[reply]
[d/l]
[select]
      And it did - thank you for pointing that out mr.nick! Never would have guessed in a million years to pass only one arg to a function that shifts in two; still scratching my head on that one. Regarding $sth and $rows, yes, the code is both limited and premature. Am planning on writing it more efficiently, so am grateful that both you and sundialsvc4 mentioned some db items that got me thinking; have been doing the same thing, with limited knowledge about the API, for far too long. Need to dig in there: 
      http://search.cpan.org/~timb/DBI-1.616/DBI.pm

      [download]
[reply]
[d/l]
Re: passing objects as references ?
by Anonymous Monk on Dec 08, 2011 at 15:38 UTC
[reply]
      The error he showed seemed to say that REF(0xaaa3700) was actually part of the query string, no doubt because he passed the parameters as references.

      mr.nick ...

[reply]
[d/l]
Re: passing objects as references ?
by locked_user sundialsvc4 (Abbot) on Dec 08, 2011 at 22:13 UTC

    Much grief can occur by trying to assemble an SQL-string in order to insert parameter-values into the statement’s text.   What you should be doing, for a wide variety of “Bobby Tables reasons” is using placeholders ... parameters.   When parameters are substituted, the substitutions are treated as what they are:   variables, not text-strings.

    It ought to be fairly easy to adjust this class so that it accepts, not only the SQL that should be executed, but an arrayref of any parameters that need to be substituted into it.   (If there are no parameters, this is ignored.)   Not only is the entire immediate problem solved “for good and for always” in one stroke, but the SQL processing can be made much more efficient, too.   Once you have prepared the query-handle once, you can execute it as many times as you like, with any parameter-values you wish, without re-preparing it (because the SQL text and therefore the execution-plan is no longer changing).

      mysql supports placeholders for table names? You don't have to use DBI#quote_identifier?
[reply]

Back to Seekers of Perl Wisdom