perl DBI OO

[jtbutler78]

I have a module that I instantiate a DBI connection from. My question
is - I am doing the right way? It works but I dont know know I am
making a connection to the DB each time I call getResults or am I only
using the first connection I make. I call getResults inside loops or
make several calls to it within my application and its a waste to make
connections with each call.

sub new {

my ($pkg, $db_host, $db, $uid, $pwd) = @_;

my $app = {};
$app->{DB} = DBI->connect("DBI:mysql:$db:$db_host", "$uid",
"$pwd");

bless $app, $pkg;

return $app;

} #end new

sub getDB { return $_[0]->{DB} };

sub getResults {

my ($pkg, $query) = @_;

my $sth = $pkg->getDB()->prepare($query);
$sth->execute or die "Can't execute statement: $DBI::errstr";
return $sth->fetchall_arrayref();

} #getResults

 

[Jens Thoms Toerring]

I have a module that I instantiate a DBI connection from. My question
is - I am doing the right way? It works but I dont know know I am
making a connection to the DB each time I call getResults or am I only
using the first connection I make. I call getResults inside loops or
make several calls to it within my application and its a waste to make
connections with each call.

sub new {
my ($pkg, $db_host, $db, $uid, $pwd) = @_;
my $app = {};
$app->{DB} = DBI->connect("DBI:mysql:$db:$db_host", "$uid",
"$pwd");
bless $app, $pkg;
return $app;
} #end new

sub getDB { return $_[0]->{DB} };

sub getResults {
my ($pkg, $query) = @_;
my $sth = $pkg->getDB()->prepare($query);

I guess it's about this line. No, you don't create a new connection
every time since the getDB method returns a value you stored after
you called connect(). You call connect() only each time you create
a new object.

$sth->execute or die "Can't execute statement: $DBI::errstr";
return $sth->fetchall_arrayref();
} #getResults

What I would think about is why you writw your own class when
there are existing ones like e.g. DBIx::Class etc. - you don't
even have to write SQL anymore which makes writing programs a
lot simpler and less tedious.
Regards, Jens

 

[jtbutler78]

I have a module that I instantiate a DBI connection from. My question
is - I am doing the right way? It works but I dont know know I am
making a connection to the DB each time I call getResults or am I only
using the first connection I make. I call getResults inside loops or
make several calls to it within my application and its a waste to make
connections with each call.
sub new {
my ($pkg, $db_host, $db, $uid, $pwd) = @_;
my $app = {};
$app->{DB} = DBI->connect("DBI:mysql:$db:$db_host", "$uid",
"$pwd");
bless $app, $pkg;
return $app;
} #end new
sub getDB { return $_[0]->{DB} };
sub getResults {
my ($pkg, $query) = @_;
my $sth = $pkg->getDB()->prepare($query);

I guess it's about this line. No, you don't create a new connection
every time since the getDB method returns a value you stored after
you called connect(). You call connect() only each time you create
a new object.

$sth->execute or die "Can't execute statement: $DBI::errstr";
return $sth->fetchall_arrayref();
} #getResults

What I would think about is why you writw your own class when
there are existing ones like e.g. DBIx::Class etc. - you don't
even have to write SQL anymore which makes writing programs a
lot simpler and less tedious.
Regards, Jens

I will look into it - I didnt know about that module. I basically
want to have one place where I make/modify my connections.

 

[brian d foy]

I have a module that I instantiate a DBI connection from. My question
is - I am doing the right way? It works but I dont know know I am
making a connection to the DB each time I call getResults or am I only
using the first connection I make.

Although it looks like you're fine in this situation, if you're ever
curious what DBI is doing, you can use tracing feature:

DBI->trace($level);

See the DBI docs for details

 

[xhoster]

I have a module that I instantiate a DBI connection from. My question
is - I am doing the right way? It works but I dont know know I am
making a connection to the DB each time I call getResults or am I only
using the first connection I make. I call getResults inside loops or
make several calls to it within my application and its a waste to make
connections with each call.

You are making a new connection each time you call "new", and just re-use
that connection each time you call getResults on a given object obtained
by new. So if your class is only instantiated once per program, then
you only make one connection. But from the bigger picture, I'd have to
wonder what the point is. It looks like you just made a simple wrapper
around DBI which adds no new functionality but takes away a lot of existing
functionality.

Xho

 

[jtbutler78]

You are making a new connection each time you call "new", and just re-use
that connection each time you call getResults on a given object obtained
by new. So if your class is only instantiated once per program, then
you only make one connection. But from the bigger picture, I'd have to
wonder what the point is. It looks like you just made a simple wrapper
around DBI which adds no new functionality but takes away a lot of existing
functionality.

Xho

I made this because I am making the calls all the time and its much
cleaner code-wise.

 

[xhoster]

I made this because I am making the calls all the time and its much
cleaner code-wise.

It is cleaner than just turning on RaiseError and then
calling $dbi->selectall_arrayref as needed?

At least that way you will get the right error messages. With your code,
you're likely to get can't call method on undef value errors instead of the
correct error message, as you only do error checking at one out of 3
potential DBI-error points.

Xho

 

[jtbutler78]

It is cleaner than just turning on RaiseError and then
calling $dbi->selectall_arrayref as needed?

At least that way you will get the right error messages. With your code,
you're likely to get can't call method on undef value errors instead of the
correct error message, as you only do error checking at one out of 3
potential DBI-error points.

Xho

Good call I will make the change. I am still looking into DBIx::Class
as well.

 

[Ted Zlatanov]

jn> I will look into [DBIX::Class] - I didnt know about that module. I
jn> basically want to have one place where I make/modify my connections.

I have been happiest with Rose:B::Object (on CPAN). It uses the
Rose:B class to hold database connections, so you can at least use
Rose:B to cache and manage connections. I would consider RDBO as a
whole solution, though. It's very good.

Ted