[Purdue-pm] DBI Usage Question
Rick Westerman
westerman at purdue.edu
Fri Feb 22 08:41:43 PST 2008
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
More information about the Purdue-pm
mailing list