Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

DBI inserts and what not

by lindex (Friar)
on Sep 09, 2000 at 01:32 UTC ( [id://31681]=perlquestion: print w/replies, xml ) Need Help??

lindex has asked for the wisdom of the Perl Monks concerning the following question:

Ok, here it is:
  • What is the best way to call to an insert statment with DBI
  • Is there a way to use placeholders with $dbh->do



lindex
/****************************/ jason@gost.net, wh@ckz.org http://jason.gost.net /*****************************/

Replies are listed 'Best First'.
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)"
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.
RE: DBI inserts and what not
by ncw (Friar) on Sep 09, 2000 at 02:17 UTC
    As noted above, you call
    $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!
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.

      I've never understood the point to 'bind_param()', unless you really need to tell the driver what datatype the binding needs to be. I've always passed the parameters in during execute(), and haven't had any trouble (yet).

      I usually DO use bind_columns() for the select statements, although this particular question was about an insert statement.

      Update: Regarding geektron's comment below, my only point was about using bind_param() explicitly as opposed to binding the parameters at execute time. I believe it does the same thing (please correct me if I'm wrong), and the only reason to use bind_param explicitly is to change the default bind_type.

      (7 years later)Update: Well, I've finally gotten around to needing bind_param...now that I'm using DBI with Sybase (it just doesn't seem to like the default bindings sometimes).

        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.

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; }
Re: DBI inserts and what not
by lindex (Friar) on Sep 09, 2000 at 01:34 UTC
    Slight Update more like best way to call a statement other than a select :)


    lindex
    /****************************/ jason@gost.net, wh@ckz.org http://jason.gost.net /*****************************/

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://31681]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others surveying the Monastery: (4)
As of 2024-03-29 01:57 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found