At 1:36 AM +0000 7/8/05, Greg Sabino Mullane wrote:
I don't know about easier. I like the question mark for quick little
queries, and you can simply call execute with a single argument, for
example. Can't get easier than that. :) I also would probably prefer
the $1,$2,$3,..$N style over the :foo style myself. I can see room for all
of them, but all of this is really a DBD, not a DBI, issue. The DBI
module could certainly more strongly recommend one of the '?' alternatives.
Actually, DBI already mandates support for the "?" in all DBDs, as
far as I know, and some of them have to resort to search-n-replace in
the SQL string at execute() time since the database doesn't have
native support. So if that's indeed the case, then I believe that
":foo" etc should get the same treatment.
I can also think of several ways that named is easier.
As an analogy, say you've written a Perl 5 function multiple
arguments. Would you rather use those argument values in the
function by way of a named intermediary variable, or would you rather
just use @_ subscripts everywhere instead? I see bind parameters to
be exactly the same as function arguments, conceptually speaking.
Your $1, $2 etc is like using @_ subscripts.
Then there's the classic example of ...
prepare( "insert into foo (a,b,c,d,e,f,g,h,i,j) values
(?,?,?,?,?,?,?,?,?,?)" );
execute( $z,$y,$x,$w,$v,$u,$t,$s,$r,$q );
It's just so easy to lose track of what ? goes with what. Here, and
in many other cases.
Then there's the time you want to use the same bind variable in
several places in the same query. With the ? approach, you have to
bind it multiple times; being limited in that way doesn't seem right.
Sam Vilain wrote:
So, effectively the prepare can happen at any time, and it's up to the
DBD to decide whether to actually do anything with it immediately or not.
ie, on Pg the STHs would be built before the DB is connected, and on Oracle
they are built the first time they are used (and then cached).
Actually, Pg uses server-side prepares whenever possible, so this would be
of limited use to it as well. I know mysql is going that way as
well, so I'm not
sure how useful all of this will be. I also don't know if all these
contortions will really save all that much: if performance is that much of an
issue, you can certainly use mod_perl to prepare them once (with or without
a database) in your BEGIN block, and execute thousands of times after that.
MySQL uses server side prepares since version 4.1, and the new driver
lets you access that functionality.
As for contortions, well I am thinking of large program environments
here where data dictionaries and generated SQL are the norm. Having
this feature allows for more elegant yet fast generated SQL. And
even speed aside, it gives users some more flexability of how they
want to organize their programs.
Darren Duncan again:
Each DBI driver can worry less about that its input is correct and
focus more
on its actual work.
Can you expand on this a little? Not sure I understand: DBI already validates
the number and type of the common methods. I *know* we aren't talking about a
global SQL parser - that way lies madness. :)
Are you sure about that? I am making such a thing right now. I
certainly don't expect DBI to have that built in, but it would be
nice for DBI to be structured that it is easier to build one on top.
And Tim explicitly said he wants input towards making DBI effective
for people putting layers on top of it.
> A $sth should not contain any methods for fetching the result of
an executed statement;
...
my $rlh = $sth->execute();
my $rowset = $rlh->fetchrow_arrayref();
This approach is a lot more flexible.
This seems like an unnecessary step. Fetching data seems like a normal
method for a statement handle.
Not to me. I see a statement as a program function. You prepare it,
invoke it with arguments, and it returns a result. The result is
separate from the function. In programs you often do "$foo = bar(
$baz )"; the result of bar() is put into something the moment it
executes.
-- Darren Duncan
