Re: Making DBI (results) more strict

On Mon, 10 Feb 2014 19:57:41 +0100, Max Maischein <[email protected]>
wrote:

> Hi all,
> 
> I recently discovered the greatness that is Hash::Util::lock_ref_keys , 
> when used together with ->fetchall_arrayref() like this:
> 
>      ...
>      my $rows= $sth->fetchall_arrayref( {} };
>      for( @$rows ) {
>          lock_ref_leys( $_ );
                    k
>      };
>      ...
> 
> This prevents me from accessing hash keys that don't exist. Usually, 
> this is inconvenient, but with SQL query results, I (too) often 
> encounter different case for the column names, or other inconsistencies.

connect with { FetchHashKeyName => "NAME_lc" }

> Especially when columns are allowed to be NULL, it may take me a while 
> to figure out that I'm looking at the wrong key.
> 
> I'd like to enable this returning of locked hashes from within DBI, 
> preferrably on a per-$dbh-level:
> 
>      my $dbh= DBI->connect( $dsn, 'scott', 'tiger', { RaiseError => 1, 
> StrictResults => 1 });

I can see use in that
On the or hand I'm unlikely to use it.  quite often use

my $dbh = DBI->connect (…);
my $sth = $dbh->prepare (…);
   $sth->execute;
my %rec;
$sth->bind_columns (\@rec{@{$sth->{NAME_lc}}});
while ($sth->fetch) {
    $rec{new_field} = function (@rec{qw( key field other_field )});
    $csv->print ($ofh, [ \@rec{@wanted} ]);
    }

where wanted is a combination of fields from the database (lower case
of course) and added/generated fields

> Alternatively, I'm also open to suggestions on how to best implement 
> this feature in a separate module, tentatively named DBI::strict. I've 
> thought about doing some AUTOLOAD reflection onto the real $dbh, but I'm 
> unsure about how to best approach wrapping arbitrary DBD 
> handles/statement handles with my DBI::strict::st without interfering. 
> Also, I'd appreciate hints on what subroutine(s) would be the most 
> appropriate to enable locking the hashes, as I want to write as little 
> new code for such a feature as necessary.
> 
> Thanks for reading,
> -max


-- 
H.Merijn Brand  http://tux.nl   Perl Monger  http://amsterdam.pm.org/
using perl5.00307 .. 5.19   porting perl5 on HP-UX, AIX, and openSUSE
http://mirrors.develooper.com/hpux/        http://www.test-smoke.org/
http://qa.perl.org   http://www.goldmark.org/jeff/stupid-disclaimers/