I would like to thank you all for nice suggestions on improving this. I especially liked the chanio's reply (I like it!) :)

Didn't have much time to play on this as I'm in the middle of the project, but I have lots of ideas for future.

Actually I'm using this AS IS in this project - it has lot's of simple SQL select/update which are suitable for this... 

package DBIx::Handy;

use strict;
use DBI;

sub new {
    my ($class, %config) = @_;
    
    # Set default value for auto_connect
    # lazy     - Auto connect on first execute method call         DEF
+AULT VALUE
    # startup     - Right now ...
    # manual     - You should take care of calling connect method
    $config{auto_connect} = $config{auto_connect} || 'lazy';
    
    my $self = {'_CONFIG' => \%config};
    
    bless ($self,$class);
    $self->connect() if ($config{auto_connect} eq 'startup');
    
    return $self;    
}

sub DESTROY {
  my $self = shift;
  
  $self->disconnect();
} 

sub connect {
    my $self = shift;
  
    # Connection configs
    my $c = $self->{_CONFIG};

    unless (defined $self->{_DBH}){
    $self->{_DBH} = DBI->connect('dbi:' . $c->{driver} .
                     ':database=' . $c->{database} .
                     ';host=' . $c->{host},
                     $c->{username}, $c->{password},
                     {'RaiseError' => 1}) or die "Could not connect to
+ database. Error message: $!";
    } else {
    warn "Trying to connect but already connected!";
    }
    
    return $self->{_DBH};
}

sub disconnect {
    my $self = shift;
    
    if (defined $self->{_DBH}){
    # Finish the STH if needed.
    $self->{_STH}->finish() if defined $self->{_STH};
    
    $self->{_DBH}->disconnect();
    undef $self->{_DBH};
    } else {
    warn "Trying to disconnect but already disconnected!";
    }
    
    # If it fails, it's already disconnect ...
    return 1;
}

sub dbh {
    my $self = shift;
    
    if(defined $self->{_DBH}){
    return $self->{_DBH};
    } else {
    warn "Trying to get DBH but not connected to database!";
    return;
    }
}

sub prepare {
    my $self = shift;
    
    unless (defined $self->{_DBH}){
    die "You need to be connected to database to prepare the queries!"
+;
    }
    
    return $self->{_DBH}->prepare(shift);
}

sub do {
    my $self = shift;
    
    return $self->execute(sql => shift);
}

sub execute {
    my $self = shift;
    my %params = @_;
    
    # Check - should we connect
    unless(defined $self->{_DBH}){
    $self->connect() if $self->{_CONFIG}->{auto_connect} eq 'lazy';
    }

    # If we received sth in params - it's prepared earlier so we dont 
+do it now.
    $self->{_STH} = $params{sth} || $self->prepare($params{sql});
    
    $self->{_STH}->execute(@{$params{data}});

    if(defined $params{method}){
    my $method = $params{method};    
    return $self->{_STH}->$method(  @{ $params{method_params} }  );
    } else {
    return $self->{_STH};
    }
}

sub insert {
    my $self = shift;
    my %params = @_;
    
    my @fields = $self->_GET_FIELDS($params{table});
  
    my $data = $params{data};
  
    my $sql = 'INSERT INTO ' . $params{table} . ' (';
    
    my ($sql_part1, $sql_part2, @data);
  
    foreach (@fields){
    if(defined($data->{$_}) && (length($data->{$_}) >= 1) && ($data->{
+$_} ne '')){
        $sql_part1 .= "$_,";
        $sql_part2 .= '?,';
        push (@data,$data->{$_});
    }
    }
  
    chop($sql_part1);
    chop($sql_part2); # to remove last ,
  
    $sql_part1 .= ')'; 
    $sql_part2 .= ')'; 
    $sql .= $sql_part1 . ' VALUES (' . $sql_part2;

    return $self->DB_execute(sql => $sql,
                 data => \@data,);    
}


sub update {
    my $self = shift;

    my %params = @_;

    my @fields = $self->_GET_FIELDS($params{table});

    my $data = $params{data};

    my $sql = 'UPDATE ' . $params{table} . ' SET ';
    my @data;

    foreach (@fields){
    if(defined($data->{$_}) && (length($data->{$_}) >= 1) &&
      ($data->{$_} ne '') && ($_ ne $params{id_field})  ){
        
        $sql .= $_ . ' = ?,';
        push @data,$data->{$_};
    }
    }

    chop($sql); # to remove last ,
    $sql .= ' WHERE ' . $params{id_field} . ' = ?'; # where id_field =
+ id_value

    push @data, $data->{$params{id_field}};

    return $self->DB_execute(sql => $sql,
                 data => \@data,); 
}

sub _GET_FIELDS {
    my $self = shift;
    
    my $results = $self->execute(sql => 'SHOW COLUMNS FROM ' . shift ,
                 method_params => 'Field');
    
    return(keys %{$results});
}

1;

[download]
And a sample of how it's being used. 
#!perl
use strict;
use warnings;

use DBIx::Handy;

my $DB = DBIx::Handy->new(driver    => 'mysql',
                          database  => 'test',
                          host      => 'localhost',
                          username  => 'alex');

my $res = $DB->execute(sql    => 'SELECT * FROM tabela WHERE id = 1',
                       method => 'fetchrow_hashref');

print <<EOL;
Record number   : $res->{id}
Email           : $res->{email}
Password        : $res->{password}
EOL

[download]
Of course the new method is actualy in the cgiapp_init of my main module (I'm using CGI::App), it's parameters come from CGI::App::Plugin::Config::Simple - so I just type : $self->{_DB}->execute(...) in some RunMode ... and ussualy pass the result to HTML::FillInForm and HTML::Template ...

One thing I didnt understand is this last (The continual linkage between $self and the %params might ...). Would you explain a bit better.

Also if any body has any other idea I would appreciate it. Thanks!

In reply to Re^2: Comments on my code, advices how to improve it. by techcode
in thread Comments on my code, advices how to improve it. by techcode

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.