Short response: I don't like it.
Long response: I have been using DBI since 1997 and I have on many occasions wanted a simpler manner in which to interact with it. I have made various wrapping routines and what I don't like about the methods you present for the most part are the names and manner which the connects are handled. For me if DBI is to be Simple it needs to have the following:
1) Configurataion file based connect parameters. These are be predefined before a connect is called or they can be loaded if absent at the time a connection is requested.
2) The user should rarely have to explictly interact with the connect statement, individual methods should make the connection when they are called.
3) The names of the methods should state exactly what is being returned, this may increase the length of the name, but it will go a long way in making the script more mantainable.
Here is some code I have been using for some resent development work, it is not production grade, but hopefully illustrates my point. (disproves?)
our %dbh;
# by using a hash of connections, each database
# can have its own current connection, which allows
# for presistent connections and works fine with Apache::DBI
# code similar to this has been running in production code
# for the last two years with no ill side effects.
# dbhandle that is
sub dbhandle {
my $self = shift;
$self->error_to_log("db_access requested");
$self->error_to_log("Request for connect to: " .
$self->conf___database .
"Host Name: " . $self->conf___dbd_hostname );
if (defined $dbh{$self->conf___database()}) {
$self->error_to_log("Used *cached* handle.");
return ($dbh{$self->conf___database()})
} else {
my $dsn = "dbi:" . $self->conf___dbd_driver . ":" . $self->c
+onf___database .
";host=" . $self->conf___dbd_hostname .
";port=" . $self->conf___dbd_port;
$dbh{$self->conf___database()} = DBI->connect("$dsn",$self->co
+nf___dbd_user,$self->conf___dbd_password, {
PrintError => 1,
RaiseError => 1,
}
) || die $DBI::errstr;
# DBI->trace(2,"/tmp/dbitrace.log");
return($dbh{$self->conf___database()}) ;
}
}
sub db_tables {
my $self = shift;
my @tables = $self->dbhandle->func( '_ListTables' );
return(@tables);
}
sub db_add_row_to_table {
my ($self,$table,$args) = @_;
my @place = ();
my @field = ();
my @value = ();
foreach (keys %{$args}) {
push @place, '?';
push @field, $_;
push @value, $args->{$_};
}
my $string = qq[ insert
into $table
( ] . join(' ,', @field) . qq[ )
values ( ] . join(' ,', @place) . qq[ )
];
my $id = $self->db_do($string
, \@value );
return ($id);
}
sub db_do {
my ($self,$string,$placeholders) = @_;
my $id;
$self->error_to_log("$string");
my $cursor = $self->dbhandle->prepare($string);
$cursor->execute(@{ $placeholders });
if ($string =~ /^\s?insert/i) {
$id = $self->dbhandle->{'mysql_insertid'};
# ($id) = $self->db_row("SELECT MAX(page_id) FROM page");
}
return ( $id );
}
sub db_disconnect {
my $self = shift;
my $change = 1;
if ($self->dbhandle && $change == 1) {
$self->dbhandle->disconnect();
# $self->error_to_log("Disconnected from: $db_to_disco
+nnect");
}
}
sub db_list_of_databases_as_array {
my $self = shift;
my $drh = DBI->install_driver( $self->conf___dbd_driver() );
my @databases = $drh->func('127.0.0.1', '3306', '_ListDBs');
return(@databases);
}
sub db_single_column_as_array {
my ($self,$sql) = @_;
my (@list);
my $cursor=$self->dbhandle->prepare("$sql");
$cursor->execute;
while ( my ($tmp) = $cursor->fetchrow ) {
push @list,$tmp;
}
$cursor->finish;
return(@list);
}
sub db_single_row_as_array {
my ($self,$string,$placeholders) = @_;
$self->error_to_log("STRING: $string");
my $cursor = $self->dbhandle->prepare("$string");
$cursor->execute(@{$placeholders});
my @result = $cursor->fetchrow;
$cursor->finish;
return(@result);
}
sub db_all_rows_as_arrayref {
my ($self,$string) = @_;
my $cursor = $self->dbhandle->prepare("$string");
$cursor->execute;
my $results = $cursor->fetchall_arrayref;
$cursor->finish;
return($results);
}
sub db_one_row_as_hashref {
my ($self,$string,$placeholders) = @_;
my $cursor = $self->dbhandle->prepare($string);
$cursor->execute(@{$placeholders});
my $result = $cursor->fetchrow_hashref;
$cursor->finish;
return ($result);
}
