On 03/01/2011 13:08, Tim Bunce wrote:
On Mon, Jan 03, 2011 at 11:52:45AM +0000, Martin J. Evans wrote:
Ok, as I suspected, this was sort of my fault. You can indeed do:
$h = DBI->Connect;
$s = $h->prepare(q/select * from sometable/);
$s->{ChopBlanks} = 1;
and it chops blanks but you cannot do:
selectall_arrayref(q/select * from sometable/, {ChopBlanks => 1});
or
prepare(q/select * from sometable/, {ChopBlanks => 1});
Don't know if you should be able to do this but it seems you cannot.
Generally "method attributes" and "handle attributes" are quite
distinct. The one place they're not is the connect() methods that create
a statement handle.
It's an unfortunate historical accident that methods returning statement
handles can't also take handle attributes in the way you describe.
and IIRC, this is not the first time in the last few months you've told
me this so perhaps this time I'll remember. I think the first was DBDs
setting method attributes in private_attribute_info.
I think it would be nice especially in the selectall methods as you
do not see the sth. I tried to locate the code to do this which I
thought was somewhere under _new_handle (probably dbih_setup_handle)
but there is magic in there I don't get right now.
If you're interested in tackling this (for which I'd be delighted) then
we should start by defining the semantics and considering any backwards
compatibility issues.
Tim.
Well, I seem to keep hitting this problem so I guess I am interested.
The priciple places where I'm having problems are the selectall_*
methods as you don't see the sth so there is nowhere to set sth attributes.
The selectall_* methods are convenience methods but if you need to set
ChopBlanks, LongReadLen or LongTruncOk (as examples of commonly used
statement attrs affecting the results) it is not possible to do it
without setting these attributes on the dbh first or you have to resort
to preparing the statement yourself, set the attributes you need then
pass the prepared statement handle to the selectall_* methods.
So for selectall_arrayref for example you need to do:
{
local $dbh-{ChopBlanks} = 1;
my $res = $dbh->selectall_arrayref($sql);
}
or
my $sth = $dbh->prepare($sql);
$sth->{ChopBlanks};
my $res = $dbh->selectall_arrayref($sth);
However, I believe these all end up calling prepare and prepare is
documented as taking a %attr although not what this %attr is used for
(what is it used for?). Also, prepare, creates a new sth so it seems
logical that this is where any sth attributes might want to be set.
Since sths inherit attributes from the dbh, making prepare take sth
attributes would also be the logical place since DBI must already be
copying attributes from the dbh to the new sth and I'd expect attributes
set in the prepare to override those inherited from the dbh.
So the above examples could just become:
my $res = $dbh->selectall_arrayref($sql, {ChopBlanks => 1});
or if you really wanted to get hold of the sth (and the above would end
up like this anyway):
my $sth = $dbh->prepare($sql, {ChopBlanks => 1});
I particularly wanted ChopBlanks, LongReadLen and LongTruncOk as they
affect the result-set which is what the selectall_* methods are about
but I guess someone might want TaintOut as well or any of the PrintWarn,
PrintError, RaiseError (the latter in particular since it particularly
applies to the selectall_* methods) or the NAME_*. Most of the other
attributes are read only with the exception of Callbacks but if you are
going this far you'd probably do it yourself anyway. So, I guess I'm
saying you might want to set any of the attributes which affect the
result-set itself, or what happens when something goes wrong.
As for backward compatibility issues I'm not sure if there are any -
sounds like you might know something here I don't.
prepare already has a \%attr but what it is used for currently is
unclear to me.
selectall_arrayref also has a \%attr which I believe only supports Slice
at the moment so no conflicts there.
selectall_arrayref
-> prepare($sql, \%attr)
-> execute(@parameters)
->fetchall_arrayref()
selectall_hashref also has a \%attr but I'm not sure how it is used
selectall_hashref
-> prepare($sql, \%attr);
-> execute(@parameters);
->fetchall_hashref($key_field);
It is not killing me not being able to do this but I think it would be a
neat and useful addition.
Martin