RE: DBI question

[Hanson, Rob]

> Basically, i'm trying to write a little abstraction layer

Someone already did the work for you, check out Class::DBI.

Here is a good article on it, it might be all you need.
http://www.perl.com/pub/a/2002/11/27/classdbi.html

Rob

-----Original Message-----
From: Peter Kappus [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 14, 2003 3:59 PM
To: CGI
Subject: DBI question


Hi List,
        Sorry if this is a little offtopic but I'm trying to learn DBI for a
CGI I'm writing.  Been reading http://www.perldoc.com/cpan/DBI.html but
seems a little over complex for what I need...just inserts, updates, and
queries really. Portability isn't too important.  Probably going to use
DBD::ODBC on top of it anyway. Also trying to learn about packages and OOP
with perl at the same time...yikes.

Basically, i'm trying to write a little abstraction layer so that I can say
things like:


$dbh = MyPackages::DB->new();  #handles my connection stuff.

#and then
my @values = $dbh->getvalues("username","users");

#or maybe,
my @rowsHashRefs = @{$dbh->query("select username,email from users")};

#and then
foreach my $hashRef (@rowsHashRefs){
        my %row = %{$hashRef};
        #some html...
        print "user $row{'username'}'s email is $row{'email'}"; 
        #or whatever
}



Not sure how much code I'm actually saving by doing this...Does anybody have
experience using DBI or any tricks for keeping things simple and avoiding
excessive prepare, execute, selectall_hashrefs...etc.


Secondly, I thought I had something working (see my "package" below) but
it's complaining that I'm only passing one argument to selectall_hashref
despite the documentation saying you can use just a simple statement.

here's the error: Usage: $h->selectall_hashref($statement, $keyfield [,
\%attr [, @bind_params ] ]) at Score/DB.pm line 56.


Thanks for any pointers...


-peter





here's the package that I'm trying to write:  (be warned:  It's ugly)



use strict;
use DBI;
use DBD::ODBC;
package MyPackages::DB;


sub new {
        #my $class=shift;
        my $self={};
        bless $self;
        #go ahead and connect
        $self->connect();
        return $self;
}

sub connect{
        my $self = shift;
        $self->{dbh} =
DBI->connect("dbi:ODBC:myDSN","username","sekretpassword",{
                AutoCommit=>0,
                RaiseError=>1}) or die("Can't connect! $!");
}

sub getValue{
        my ($self,$field,$table,$condition) = @_;
        return if($#_<2);
        
        my @rowHashRefs;

        if($condition){
                @rowHashRefs = @{$self->query("Select $field from $table
where $condition")};
        }else{
                @rowHashRefs = @{$self->query("select $field from $table")};
        }

        #get at our hash from our arrayref [0] and pull out the value of
field:$field
        #if ($#rowHasheRefs == 0){
        #       return ${${$arrayRef}[0]}{$field};
        #}
        
        my @fieldValues;
        foreach my $rowHashRef (@rowHashRefs){
                push(@fieldValues,${$rowHashRef}{$field});
        }

        #give back the array of values ...or do I want a reference to it?
        return @fieldValues;
        
}

sub query{
        my ($self,$qry) = @_;
        return unless($qry);
        return $self->{dbh}->selectall_hashref($qry);  #why does it complain
here????????
        
}

1;



-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]