On Mon, 17 Mar 2003 05:07:36 -0600 Moritz von Schweinitz <[EMAIL PROTECTED]> wrote:

> i'd just like to recommend some more hash-friendly routines for these 
> kind of operations. i know that they are relativly tricial to code, but 
> would make it all seem more "natural" for the beginners, methinks.
> 
> something like
> 
> $dbh->hash_do("INSERT INTO table (?) VALUES (?)", \%hash);
> 
> (where the first ? would be expanded to something along the lines of
> 'join(',', keys %hash)'
>   and the second ? would become something like
> 'join(',', map($dbh->quote($_), values %hash))'

That may be too specialized for DBI, but you could use a locally
maintained module that provides the subroutines.

Spliting into separate prepare and execute stages and using
placeholders would allow prepare once and execute many times and allow
the subroutines to be used for SELECTs.  I'd also sort the hash keys
so the order of the keys and values would be consistant.

# All untested

   # call as: my $sth = hash_prepare( $dbh, $sql, \%hash );
   # %col% = place to insert column list
   # %ph%  = place to insert placeholder list
   # %val% = place to insert value list
   # %eqp% = place to insert col=? pairs
   # %eqv% = place to insert col=val pairs
   sub hash_prepare {
      my ( $dbh, $sql, $rhash ) = @_;
      my @col = sort keys %$rhash;
      $sql =~ s/%(col|ph|val|eqp|eqv)%/
         if    ( "col" eq $1 ) { join ", ", @col }
         elsif ( "ph"  eq $1 ) { join ", ", ("?") x @col }
         elsif ( "val" eq $1 ) { join ", ",
            map { $dbh -> quote( $$rhash{$_} ) } @col }
         elsif ( "eqp" eq $1 ) { join ", ", map { "$_ = ?" } @col }
         elsif ( "eqv" eq $1 ) { join ", ",
            map { "$_ = " . $dbh -> quote( $$rhash{$_} } @col }
               /xeg;
      return $dbh -> prepare( $sql );
   }

   sub hash_execute {
      my ( $sth, $rhash ) = @_;
      return $sth -> execute( @$rhash{sort keys %$rhash} );
   }

   sub hash_do {
      my ( $dbh, $sql, $rhash ) = @_;
      my $sth = hash_prepare( $dbh, $sql, $rhash )
         or die "hash_prepare of '$sql' failed, $DBI::errstr\n";
      return hash_execute( $sth, $rhash );
   }

-- 
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.

Reply via email to