DBI and do
Hello folks, Is there are reason why the 'select(all|col)_*' methods support the handover of statement handles, but 'do' doesn't? Is it possible to change the statement assignment? Current: my $sth = $dbh-prepare($statement, $attr) or return undef; Suggested: my $sth = (ref $stmt) ? $stmt : $dbh-prepare($stmt, $attr) or return; The suggested solution makes it possible to work with cached statements and would therefore, in my opinion, improve performance. With best regards, Thomas Knoerl Siemens AG Siemens IT Solutions and Services SIS IT SM CP Wuerzburger Str. 121 90766 Fuerth, Germany Tel.: +49 (911) 978-3034 Fax: +49 (911) 978-2037 Mobile: +49 (1522) 2795353 mailto:thomas.kno...@siemens.com Siemens Aktiengesellschaft: Chairman of the Supervisory Board: Gerhard Cromme; Managing Board: Peter Loescher, Chairman, President and Chief Executive Officer; Wolfgang Dehen, Heinrich Hiesinger, Joe Kaeser, Barbara Kux, Hermann Requardt, Siegfried Russwurm, Peter Y. Solmssen; Registered offices: Berlin and Munich, Germany; Commercial registries: Berlin Charlottenburg, HRB 12300, Munich, HRB 6684; WEEE-Reg.-No. DE 23691322
Re: DBI package problem?
On Tue, Mar 30, 2010 at 12:42:44AM +0200, rosenfield.alb...@gmail.com wrote: • dependencies I still think that DBI should be XS, and people should really try very very hard to make that work. Pure-perl DBI won't fly. Modules and applications that are now depending on DBI do not want to be depending on an extra layer, however thin it might be. Ah yes, I think we can easily agree that there should not be an extra layer. Just a proper selection mechanism that imports the best available module at compile time. As it is right now, PurePerl DBI is very hard to use because it doesn't install, except in cases where XS works (and PurePerl is thus pointless). I'm curious about the environment you're targeting that doesn't have a compiler. Can you enlighten me? DBI is Tim's product, but getting more and more a community effort. I bet he'd love your work on getting DBI::PurePerl working [...] Tim, what say ye? I'd happily take a patch to Makefile.PL that detects the lack of a C compiler and then alters %opts so WriteMakefile() writes a Makefile that won't try to compile DBI.xs etc etc. Tim.
Re: Thread safety for DBD::File and DBD::DBM's inheritance
On Mon, Apr 12, 2010 at 12:55:28PM +0200, Jens Rehsack wrote: So I did last days when I played around with threads and Log::Log4perl::Appender::DBI. I've seen, than (even if in my simply example everything works fine), a cloned DBD::File still uses the same file handles for reading/writing. I talked with Merijn about this situation in #dbi on irc.perl.org and we decided, that it would be better, to croak an error when a $dbh is used in another thread than the owning one. So I started with some hacking this morning to implement that and did How? Via a CLONE method? 'make test' on DBI (as teached by Merijn). I got errors from DBD::DBM and after a short analyze I've seen, that it derives from DBD::File, but doesn't inherit the entire behavior, just a subset. This caused DBD::DBM::db::prepare to fail in my tests. Now I'm unsure how to step forward. I can hack DBD::File to handle those incomplete inheritance by ignoring them, or I can fix DBD::DBM to full inherit and modify the behavior it needs to change (instead of ignoring parent's methods). I would prefer the second way, because I think it's the cleaner one. It sounds good (but you've not really given enough background information for someone who's forgotten all he ever knew about DBD::File DBD::DBM, like me). Tim.
Re: DBI and do
Knoerl, Thomas wrote:
Hello folks,
Is there are reason why the 'select(all|col)_*' methods support the handover of
statement handles, but 'do' doesn't?
$dbh-do() is what I would call a 'short hand' or 'short cut' method.
It was included for the CUD (Creation Update Delete) operations of SQL
as any of these operations do not involve the return of a 'Result Set'.
All it is doing is wrapping the the 'prepare' and 'execute' into the
'do' and it does not bother to return a 'Statement' handle. It is not a
'Magic' function the eliminates a step or two.
In fact there is a big performance hit when using 'do' repeatedly, say
in the insertion of may row into a db, You would be doing a prepare many
hundreds of times over when you could just
do it once and just change the params ie
Very slow
foreach my $i (1...100){
$dbh-do(insert into bla (id) values ($i));
}
much faster
my $sth =$dbh-prepare(insert into bla (id) values (?));
foreach my $i (1...100){
$sth-execute($i);
}
Even faster the execute_array
my @in_values=(1...1000)
my @status;
my $sth = $dbh-prepare(qq{ INSERT INTO foo (id, bar) VALUES ( ?) });
$sth-bind_param_array(1,\...@in_values);
$sth-execute_array({ArrayTupleStatus=\...@status}) or die error inserting;
Is it possible to change the statement assignment?
Current:
my $sth = $dbh-prepare($statement, $attr) or return undef;
Suggested:
my $sth = (ref $stmt) ? $stmt : $dbh-prepare($stmt, $attr) or return;
The suggested solution makes it possible to work with cached statements and
would therefore, in my opinion, improve performance.
You will have to ask Tim Bunce or maybe Alligator Descartes for that
change but I do not see any use in having it.
A statement handle without 'SQL' ( a statement) would be meaningless,
something like a sentence with only punctuation.
cheers
John Scoles
With best regards,
Thomas Knoerl
Siemens AG
Siemens IT Solutions and Services
SIS IT SM CP
Wuerzburger Str. 121
90766 Fuerth, Germany
Tel.: +49 (911) 978-3034
Fax: +49 (911) 978-2037
Mobile: +49 (1522) 2795353
mailto:thomas.kno...@siemens.com
Siemens Aktiengesellschaft: Chairman of the Supervisory Board: Gerhard Cromme;
Managing Board: Peter Loescher, Chairman, President and Chief Executive
Officer; Wolfgang Dehen, Heinrich Hiesinger, Joe Kaeser, Barbara Kux, Hermann
Requardt, Siegfried Russwurm, Peter Y. Solmssen; Registered offices: Berlin and
Munich, Germany; Commercial registries: Berlin Charlottenburg, HRB 12300,
Munich, HRB 6684; WEEE-Reg.-No. DE 23691322
Re: connecting()?
On Wed, Mar 31, 2010 at 10:14:04AM -0700, David E. Wheeler wrote:
Fellow DBIers,
I was just discussing writing a callback on connect() to change
authentication (my correspondent wants to use realm files). But then I
discovered to my disappointment that there is no support for callbacks
on connect(). This makes sense, frankly, since the method is called
before the callbacks are applied, and callbacks execute before the
method, not afterward (at least for now).
Yeap.
But I've found great use by adding callbacks on connected(), so that
they execute just after a connection. So I was wondering how folks
felt about the idea of adding a connecting() method that executes just
before the DBI tries to connect to the database? I think this would be
a nice complement to connecting(), and instantly make a simple
interface for adding a pre-connection callback, too.
DBI-connect effectively does:
$drh = DBI-install_driver($driver);
$dbh = $drh-connect(...);
Assuming we added a $dbh-connecting() method, how would it be called,
and how would the callback have been enabled prior to calling it?
Why not register the callback on the driver:
DBI-install_driver('Pg')-{Callbacks}{connect} = ...;
That'll affect all subsequent connects using that driver and won't work
with proxy drivers like Gofer, which is why DBI docs discourage messing
with driver handles, but it may fit your needs.
Tim.
p.s. On a slight tangent: most drivers implement $dbh-connect with code like:
my $this = DBI::_new_dbh($drh, ...);
It's long bugged me that this isn't a method call.
In http://cpansearch.perl.org/src/TIMB/DBI-1.609/TODO_2005.txt
(which is old but still has useful information) I suggested:
Rework handle creation to use methods:
Maybe $h-new_child(\%handle_attr)
dr::connect =
$dbh = $drh-new_child(\%attr);
$dbh-connect(...) - calls $dbh-reset()
db::prepare =
sub ...::db::prepare {
my ($dbh, $sql, $attr) = @_;
$sth = $dbh-new_child($attr)
my @statements = $dbh-preparse($sql);
$sth-{PendingStatements} = \...@statements if @statements 1;
$sth-prepare( shift @statements ) or return;
return $sth;
}
If we did that (and handled backwards compatibility somewhow)
then presumably new_child() could apply the Callbacks attribute
which would then fire on the subsequent $dbh-connect call.
DBD::mysql 4.014 released
It's been an extremely busy year thus far with all the great work we're doing at NorthScale as well as the release of CaptTofu 2.0 (My son Kiran!). With the new UI on rt.cpan.org, in my spare time, I went through and closed some bugs, hence a new release of DBD::mysql, 4.014. In this release: * BUG 30033 Fixed handling of comments to allow comments that contain characters that might otherwise cause placeholder detection to not work properly * BUG 53844, Fix for memory leak in stats. (Gregory Burmistrov) * BUG 49719, Fix for handling of NULLs in prepared statements (Gert Pache) * BUG 55627, Fix for testing failure due to strict mode (Yves) * BUG 51784, Fix for mysqladmin on Windows in Makefile (Zeeshan Muhammad) * BUG 41630, Typo in Makefile There are other bugs in rt.cpan.org, hence a pending release in the next week or two. I like an empty bug list! Thank you to Gregory Burmistrov, Gert Pache, Yves, Zeeshan Muhammad for your patches! You can find the code at: http://search.cpan.org/~capttofu/DBD-mysql-4.014/ The file: file: $CPAN/authors/id/C/CA/CAPTTOFU/DBD-mysql-4.014.tar.gz size: 131270 bytes md5: 74f118a4984e6a49f8ece28e68caf543 Also, I have moved the source repository from Subversion to Git (Github) git clone git://github.com/CaptTofu/DBD-mysql.git Why Github? I've really grown to like Git once my brain wrapped around it. No slight to any other system. I have DBD::drizzle hosted at Launchpad. At least now, I have to only concentrate on remembering how to use two revision control systems!
Re: connecting()?
On Apr 15, 2010, at 5:17 AM, Tim Bunce wrote:
$drh = DBI-install_driver($driver);
$dbh = $drh-connect(...);
Assuming we added a $dbh-connecting() method, how would it be called,
and how would the callback have been enabled prior to calling it?
In connect(), before $connect_meth, just as connected() is called in that
method after $connect_meth.
$dbh-connected(@orig_args);
unless ($dbh = $drh-$connect_meth($dsn, $user, $pass, $attr)) {
Why not register the callback on the driver:
DBI-install_driver('Pg')-{Callbacks}{connect} = ...;
That'll affect all subsequent connects using that driver and won't work
with proxy drivers like Gofer, which is why DBI docs discourage messing
with driver handles, but it may fit your needs.
It's not my needs. Someone is creating a CPAN module I think. His needs were
met by connected(), but I think that connecting() would be useful as well.
If we did that (and handled backwards compatibility somewhow)
then presumably new_child() could apply the Callbacks attribute
which would then fire on the subsequent $dbh-connect call.
That would be cool. I, alas, have a very short supply of tuits these days.
Best,
David
