[Purdue-pm] DBI Usage Question

[Rick Westerman]

Dave Jacoby wrote:
> Assume a database table Test with a self-incrementing primary key field 
> and two other fields, USERID and ABCD. You want to put user-input data
> into USERID and ABCD. Assume this is the SQL string you want.
>
>    INSERT INTO Test ( USERID , ABCD ) VALUES ( 'random' , 'text' ) ;
>
> http://www.stonehenge.com/merlyn/UnixReview/col58.html
> Randal Schwartz codes his example using this syntax:
>
>    my $sth = $dbh->prepare(
>      q{ INSERT INTO Test ( USERID , ABCD ) VALUES ( ? , ? ) }
>      ) ;
>    $sth->execute( $a , $b ) ;
>
> I'd rather do it more like this:
>
>    $a = $dbh->quote($a) ;
>    $b = $dbh->quote($b) ;
>    my $query = qq{INSERT INTO Test {USERID,ABCD} VALUES ($a,$b);};
>    my $sth = $dbh->prepare($query) ;
>    $sth->execute() ;
>
> Because then I know that I've explicitly and clearly quoted the inputs.
>
> I know TMTOWTDI. I like that TMTOWTDI. But I believe that some ways are 
> better than others, and Randal Schwartz might know some of the better 
> ways. Is there an overriding, understandable reason why my way is worse?
>   
    The use of '?' marks (placeholders) in DBI is two fold -- First for 
speed since  the prepare statement only has to be done once instead of 
each time for each new set of variables.  Second it automatically quotes 
the parameters so that you do not need to do so.

    As Michael pointed out, since you are not looping in the above code 
then there is no performance gain.  If you were looping then using 
placeholders would be faster.  Also I point out that using placeholders 
results in smaller code.

     Quoting from the DBI manual:

*Performance*

Without using placeholders, the insert statement shown previously would 
have to contain the literal values to be inserted and would have to be 
re-prepared and re-executed for each row. With placeholders, the insert 
statement only needs to be prepared once. The bind values for each row 
can be given to the |execute| method each time it's called. By avoiding 
the need to re-prepare the statement for each row, the application 
typically runs many times faster.

-- 
Rick Westerman westerman at purdue.edu Bioinformatics specialist at the 
Genomics Facility. Phone: (765) 494-0505 FAX: (765) 496-7255 Department 
of Horticulture and Landscape Architecture 625 Agriculture Mall Drive 
West Lafayette, IN 47907-2010 Physically located in room S049, WSLR 
building