in reply to Multiple DB Queries using DBI

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.

Replies are listed 'Best First'.
Re^2: Multiple DB Queries using DBI
by dragonchild (Archbishop) on Nov 18, 2004 at 19:17 UTC
    *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]
        I know that for Oracle and MS SQL (which is the same as Sybase, for all practical purposes), you can have multiple sths executing at once. Heck, without that for Oracle, a lot of Perl code would mysteriously break. :-)

        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]
      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]
Re^2: Multiple DB Queries using DBI
by CountZero (Bishop) on Nov 18, 2004 at 19:49 UTC
    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]

In Section Seekers of Perl Wisdom