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:[email protected]
>
> 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
>
>
>