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

Within one script I want to handle many different DB queries, update, insert, delete. Do I only have to open the DB connection once and then execute a prepare and execute statement everytime I want to do a DB function?

Can I make unlimited $sql_statement declarations and the execute each statement when needed like seen in the code below?


####################
#Doing stuff in my Perl script
#opening DB connection and other functions
#Now I need to execute some DB query
####################

$sth = $dbh->prepare($sql_statment_1) or die "Couldn't prepare the que
+ry: $sth->errstr";

$rv = $sth->execute or die "Couldn't execute query: $dbh->errstr";

$rc = $sth->finish;

################
#Go back to doing other Perl routines & stuff here
#Now I need to do more DB stuff
###############

$sth = $dbh->prepare($sql_statment_2) or die "Couldn't prepare the que
+ry: $sth->errstr";

$rv = $sth->execute or die "Couldn't execute query: $dbh->errstr";

$rc = $sth->finish;

[download]
I would call the above code at any random time in my script at the place I want to execute the query. Would that work?

Thanks

Update: Removed place holder info in "$sth->execute" section.

Replies are listed 'Best First'.
Re: Multiple DB Queries using DBI
by herveus (Prior) on Nov 18, 2004 at 18:29 UTC
    Howdy!

    The short answer is "yes".

    ...however, your sample code appears to misunderstand the execute method. 

    $sth = $dbh->prepare($statement);
$sth->execute(<placeholder values here>);
# retrieve results here
$sth->finish; # if there is any risk of not having fetched all the val
+ues

    [download]

    Note that if you prepare a statement that needs values filled in on the fly, you can usually replace them with a question mark (no quotes). When you execute the statement, you provide values for each placeholder to the execute statement. DBI takes care of quoting things that need quoted for you, as well as taking advantage of not having to re-parse the statement each time you want to execute it (if the DBD supports doing this).

    The Cheetah book (Programming the Perl DBI) from O'Reilly, is probably worth looking into if you expect to be doing any significant amount of database programming (which almost certainly will involve using DBI).

    yours,
    Michael
[reply]
[d/l]
      Yes you're right, that snippet of code was from something I used that had place holders. I'll update it.

      Thanks

[reply]
Re: Multiple DB Queries using DBI
by thor (Priest) on Nov 18, 2004 at 20:03 UTC
    I know it's not the situation that you have, but I've run into a problem where you can't be in the middle of a fetch and try to alter the database. Like-a-so: 
    $select = $dbh->prepare(q(select foo, bar from baz));
$delete = $dbh->prepare(q(delete from baz where foo=? and bar=?));
$select->execute();
$select->bind_columns(\($foo, $bar));
while($ref = $select->fetchrow_arrayref()) {
   $delete->execute($foo, $bar) if $foo == 3; #<----this won't work
}

    [download]
    If you want to do something like that, you need a separate database handle for your "change" handle. So, I would need to make a second connection to the database to prepare the delete statement in this example.

    thor

    Feel the white light, the light within
    Be your own disciple, fan the sparks of will
    For all of us waiting, your kingdom will come

[reply]
[d/l]
Re: Multiple DB Queries using DBI
by pg (Canon) on Nov 18, 2004 at 18:31 UTC

    Yes, you only need to open the connection once.

    However if this is some sort of daemon, then maybe you want to create/drop the connection when requests come and go. Remember that connection is a kind of resource. This depends on your planning, how many connections are allowed, and how they should be used.

    Also for things like GUI interface, you don't want to keep the connection as long as the GUI is running. Instead you want to create the connection when the user requests some actions by clicking a button etc.

    But for a batch job, for sure, open it once, use it, and close it.

[reply]
Re: Multiple DB Queries using DBI
by ikegami (Patriarch) on Nov 18, 2004 at 19:01 UTC

    Update: Apparently, this isn't a problem for any popular database.

    A related note...

    Some database (MySQL) allow you to have mutliple $sth open for given $dbh:

    $sth1 = $dbh->prepare($sql_statment_1);
$sth1->execute();
...
$sth2 = $dbh->prepare($sql_statment_2);
$sth2->execute();
...
$sth2->finish();
...
$sth1->finish();

    [download]

    Some don't. (MS SQL? Oracle?). In other words, it's important to know whether a given $dbh is currently associated with a $sth if you want to be portable. The easiest way to do that is to use a new $dbh for each $sth.

[reply]
[d/l]
      *blinks* I've used all the databases mentioned and have never come across a limit on the number of open $sth's. I don't think any modern database cares about that. Maybe something like FoxPro or DB2 ...

      Being right, does not endow the right to be rude; politeness costs nothing.
      Being unknowing, is not the same as being stupid.
      Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
      Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

[reply]

        It's straight out of the DBI docs:

        Portable applications should not assume that a new statement can be prepared and/or executed while still fetching results from a previous statement.

        I do not have personal experience with using Perl to talk with MS SQL or Oracle databases, but I know for sure that only one query can be active at a time per MS SQL database connection in VB. My coworker has said the same for Oracle (using an unspecified language other than VB and Perl).

[reply]
        A quick Google search found at least one case: Can't have multiple statement handles on a single database handle when AutoCommit is OFF at /usr/local/lib/perl5/site_perl/5.8.0/sun4-solaris/DBD/Sybase.pm line 105

        Apparently this is because "DBD::Sybase silently opens up another database handle for you when you try to use multiple statement handles concurrently from a single database handle." <a href="http://www.cwinters.com/programming/yapc_dbi.html">*</a>

[reply]
[d/l]
      Everything will depend on the implementation of the DBD. For some databases the prepare doesn't do anything (other than saving the info about the prepare) and delays the actual database-interaction to the execute. In those cases the number of "active" statement handles is only limited by the memory of your computer.

      That being said, I have never experienced any problems with multiple statement handles for the same database handle. As a matter of fact, modules such as Class::DBI only use one connection for all their database work and I haven't heard anyone complaining that this is broken on some databases.

      CountZero

      "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

[reply]
[d/l]
[select]
Re: Multiple DB Queries using DBI
by mbeast (Beadle) on Nov 19, 2004 at 16:02 UTC
    If this is a web app run on apache, you might also look into Apache DBI. It's got some tasty bits.
[reply]

Back to Seekers of Perl Wisdom