Making DBI (results) more strict
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( $_ );
};
...
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.
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 });
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
Re: Making DBI (results) more strict
On Mon, 10 Feb 2014 19:57:41 +0100, Max Maischein
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/
Re: Making DBI (results) more strict
Max, you can distinguish a missing column from a null one quite easily in
regular Perl. If "exists $hash->{key}" is false then the column doesn't exist,
where if that is true but "defined $hash->{key}" is false then it exists but is
null. -- Darren Duncan
On 2/10/2014, 10:57 AM, Max Maischein 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( $_ );
};
...
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. 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 });
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
