re-blessing a blessed reference

[Jon]

Hiya,

I'm trying to tidy up my code a bit by creating an 'SQL' package. At the
moment to interface with my db I'm doing something like..

sub checkpass
{
my ($username, $password) = @_;

my $dbh = DBI->connect("DBI:mysql:database", "username", "password")
unless ($dbh) { die($DBI::errstr); }

my $sql = "SELECT id FROM users WHERE username=? AND password=?";
my $sth = $dbh->prepare($sql);
$sth->execute( $username, $password) || die($sth->errstr());

my $rows = $sth->rows;

$sth->finish();
$dbh->disconnect();

return $rows;
}

I'd like to be able to create a package so I only need to connect the
database once and I can disconnect when the script has finished. eg:

my $dbh = SQL->new();

if ($dbh->checkpass()) { #authed } else { #notauthed }

I've tried the code below but get it dies with "Can't locate object method
"prepare" via package "SQL" at /SQL.pm line 19. I'm pretty new to OO so I
can't figure out what I'm doing wrong. I understand that its trying to call
on SQL:repare but I don't know how I can make it not do that. I have a
feeling its got something to do with me re-blessing $dbh which has already
been blessed but would like someone with more experience to confirm that if
possible. If I'm wrong could someone please point me in the right
direction? Thanks for your time.

=test.pl=
#!/usr/bin/perl

use lib './';
use SQL;
use strict;

my $dbh = SQL->new();
if ($dbh->checkpass( "username", "password" ))
{
print "authed";
}
else
{
print "not authed";
}

=SQL.pm=
Package SQL;

use DBI;
use strict;

sub new
{
my $dbh = DBI->connect("DBI:mysql:database", "username", "password")
unless ($dbh) { die($DBI::errstr); }

bless $dbh, 'SQL';
return $dbh;
}

sub checkpass
{
my ($dbh, $username, $password);

my $sql = "SELECT id FROM users WHERE username=? AND PASSWORD=?";
my $sth = $dbh->prepare($sql);
$sth->execute( $username, $password ) || die($sth->errstr));
my $rows = $sth->rows;

return $rows;
}

1;

 

[Tassilo v. Parseval]

Also sprach Jon:

I'm trying to tidy up my code a bit by creating an 'SQL' package. At the
moment to interface with my db I'm doing something like..

sub checkpass
{
my ($username, $password) = @_;

my $dbh = DBI->connect("DBI:mysql:database", "username", "password")
unless ($dbh) { die($DBI::errstr); }

my $sql = "SELECT id FROM users WHERE username=? AND password=?";
my $sth = $dbh->prepare($sql);
$sth->execute( $username, $password) || die($sth->errstr());

my $rows = $sth->rows;

$sth->finish();
$dbh->disconnect();

return $rows;
}

I'd like to be able to create a package so I only need to connect the
database once and I can disconnect when the script has finished. eg:

my $dbh = SQL->new();

if ($dbh->checkpass()) { #authed } else { #notauthed }

I've tried the code below but get it dies with "Can't locate object method
"prepare" via package "SQL" at /SQL.pm line 19. I'm pretty new to OO so I
can't figure out what I'm doing wrong. I understand that its trying to call
on SQL:repare but I don't know how I can make it not do that. I have a
feeling its got something to do with me re-blessing $dbh which has already
been blessed but would like someone with more experience to confirm that if
possible. If I'm wrong could someone please point me in the right
direction? Thanks for your time.

After re-blessing, the thing returned by DBI->connect is no longer an
instance of DBI::db. That's ok as long as your SQL package inherits
from DBI::db. See "Subclassing the DBI" in DBI's documentation.

So make SQL a subclass of 'DBI::db':

=test.pl=
#!/usr/bin/perl

use lib './';
use SQL;
use strict;

my $dbh = SQL->new();
if ($dbh->checkpass( "username", "password" ))
{
print "authed";
}
else
{
print "not authed";
}

=SQL.pm=
Package SQL;

use DBI;
use strict;

use Carp; # better use Carp::croak() instead of die()

@SQL::ISA = qw/DBI::db/; # make SQL a subclass of DBI::db

sub new
{
my $dbh = DBI->connect("DBI:mysql:database", "username", "password")

Don't retype code: This statement is missing a semicolon.

unless ($dbh) { die($DBI::errstr); }

bless $dbh, 'SQL';
return $dbh;
}

The way 'new' is written forbids further subclassing of SQL. You might
not want to do that anyway, but it can't hurt to do it properly:

sub new {
my $class = shift;
my $dbh = DBI->connect("DBI:mysql:database", "username", "password")
or croak $DBI::errstr;
bless $dbh => $class;
}

sub checkpass
{
my ($dbh, $username, $password);

Don't retype code: This statement is missing the assignment of @_.

my $sql = "SELECT id FROM users WHERE username=? AND PASSWORD=?";
my $sth = $dbh->prepare($sql);
$sth->execute( $username, $password ) || die($sth->errstr));
my $rows = $sth->rows;

return $rows;
}

1;

Tassilo

 

[Jon]

Also sprach Jon: [ snip ]
After re-blessing, the thing returned by DBI->connect is no longer an
instance of DBI::db. That's ok as long as your SQL package inherits
from DBI::db. See "Subclassing the DBI" in DBI's documentation.

So make SQL a subclass of 'DBI::db': [ snip ]

sub new
{
my $dbh = DBI->connect("DBI:mysql:database", "username",
"password")

Don't retype code: This statement is missing a semicolon.

unless ($dbh) { die($DBI::errstr); }

bless $dbh, 'SQL';
return $dbh;
}

The way 'new' is written forbids further subclassing of SQL. You might
not want to do that anyway, but it can't hurt to do it properly:

sub new {
my $class = shift;
my $dbh = DBI->connect("DBI:mysql:database", "username", "password")
or croak $DBI::errstr;
bless $dbh => $class;
}

Thanks for your suggestions Tasillo, I'll look into them. My apologies for
retyping code, it seemed easier at the time but in hindsight probably
wasn't.