Re: DBI inserts and what not
by BastardOperator (Monk) on Sep 09, 2000 at 01:43 UTC
|
straight from the DBI doc
$sth = $dbh->prepare("insert into table(foo,bar,baz) values (?,?,?)");
while(<CSV>) {
chop;
my ($foo, $bar, $baz) = split(/,/);
$sth->execute($foo, $bar, $baz);
}
$dbh->commit || die $dbh->errstr;
"The do() method can be used for non repeated non-select statements (or with drivers that don't support placeholders)" | [reply] [Watch: Dir/Any] [d/l] |
Re: DBI inserts and what not
by Fastolfe (Vicar) on Sep 09, 2000 at 01:40 UTC
|
From the DBI man page synopsis:
$rv = $dbh->do($statement, \%attr, @bind_values);
If you're going to be doing just a single insert, 'do' might be best. If you're going to be doing several, it'd be best to 'prepare' your insert statement and 'execute' it with your bind values for each row you're adding. Typical DBI considerations. | [reply] [Watch: Dir/Any] [d/l] |
RE: DBI inserts and what not
by ncw (Friar) on Sep 09, 2000 at 02:17 UTC
|
$dbh->do("update table set thing = ? where id = ?",
\%attr, $thing, $id);
What you pass in \%attr is interesting and not particularly
obvious, though. Provided you don't want to pass any
attributes (I've never needed to) you can pass undef or { } whichever
takes your fancy. Eg :-
$dbh->do("update table set thing = ? where id = ?",
undef, $thing, $id);
# OR
$dbh->do("update table set thing = ? where id = ?",
{}, $thing, $id);
I usually encapsulate it in a little function though
so I can just print out the values of the updates
in debug mode! | [reply] [Watch: Dir/Any] [d/l] [select] |
RE: DBI inserts and what not
by geektron (Curate) on Sep 09, 2000 at 04:49 UTC
|
avoid simple $sth->do(). the statements aren't cached, and this will slow down the app ( esp. if called in a loop ).
use $sth->prepare() and $sth->execute. you can also ( and should ) use the $sth->bind_param( :1, 'foo' ) (check your DBI driver for syntax) to bind the parameters for input (and use $sth->bind_col( 1, \$foo ) for quick output access.
DBI was designed for speed from the beginning (so says Tim Bunce), so use the features that are there. | [reply] [Watch: Dir/Any] [d/l] [select] |
|
| [reply] [Watch: Dir/Any] |
|
why would one use bind_param() and bind_cols()? speed. it might take a bit more programmer time, but it removes database transaction time - which is usually the bottleneck in scripts.
also, using bind_cols() saves a bit of time. the variables get their value assignments from the binding, so you don't need to use things like while ( my ($foo, $bar, $baz ) = $sth->fetchrow() )
UPDATE: i've double-checked some notes from a tutorial on Advanced DBI from Tim Bunce and re-consulted the Cheetah book (Programming the DBI), passing the values to $sth->execute( $foo, $bar ) on a statement prepared with placeholders explicitly calls bind_params, so there is little visible difference between explicity binding parameters ( that get into an SQL statement), but there is a visible difference for output columns ( that would use bind_col ).
bind_param IS needed to force datatype selection. and bind_param_inout is necessary for inout parameters.
using placeholders will ensure that bind_param is called anyway. one would save a check to see if the params were already bound by explicity binding them, and there may be ( this in speculation ) portability issues.
explicit binding would save on maintainability also.
and i still remember something about DB caching and DBI caching differences between explicit/implicit calls to bind_param, but i can't find the notes to back it up.
| [reply] [Watch: Dir/Any] [d/l] [select] |
Re: DBI inserts and what not
by agoth (Chaplain) on Sep 11, 2000 at 15:42 UTC
|
eval {
for (@row[3..8]) {
$_ = $oradbh->quote($_);
}
my $sql = qq%insert into DATA values
($rownum, '$owner', $page, SYSDATE,
$row[3],$row[5],$row[6],$row[7], $row[8],'','','','
+','' )%;
my $insth = $oradbh->prepare($sql);
$insth->execute;
$insth->finish;
};
$rownum++;
if ($@) {
print "$@";
$oradbh->rollback;
} else {
print "row done";
$oradbh->commit;
}
| [reply] [Watch: Dir/Any] [d/l] |
Re: DBI inserts and what not
by lindex (Friar) on Sep 09, 2000 at 01:34 UTC
|
| [reply] [Watch: Dir/Any] [d/l] |