FAQ
my $dbh = DBI->connect();
my $sth1 = $dbh->prepare( $sql_a );
my $sth2 = $dbh->prepare( $sql_b );

$sth1->execute;
$sth2->execute;

while ( my ($id) = $sth1->fetchrow_array ) {
$sth2->execute( $id )
}


# is it possible ?

Search Discussions

  • Martin J. Evans at Jul 12, 2011 at 8:57 am

    On 12/07/11 08:32, ZhangJun wrote:
    my $dbh = DBI->connect();
    my $sth1 = $dbh->prepare( $sql_a );
    my $sth2 = $dbh->prepare( $sql_b );

    $sth1->execute;
    $sth2->execute;

    while ( my ($id) = $sth1->fetchrow_array ) {
    $sth2->execute( $id )
    }


    # is it possible ?
    Sometimes. Depends on which DBD you are using and then sometimes it depends on which driver you are using under that DBD.

    You'll need to tell us which DBD (and possibly driver) you are using.

    Martin
    --
    Martin J. Evans
    Easysoft Limited
    http://www.easysoft.com
  • ZhangJun at Jul 12, 2011 at 12:04 pm
    mostly I use mysql, DBD::mysql,
    also mssql, ODBC and sybase driver.
  • Martin J. Evans at Jul 12, 2011 at 12:20 pm

    On 12/07/11 13:04, ZhangJun wrote:
    mostly I use mysql, DBD::mysql,
    also mssql, ODBC and sybase driver.
    I cannot comment on DBD::mysql (as it has been years since I last used it) but both MS SQL Server and Sybase (I believe) don't support multiple active statements on the same connection unless you enable something like the new MARS support in MS SQL Server (but it has other disadvantages). I wrote a small document for DBD::ODBC at http://www.easysoft.com/developer/languages/perl/multiple-active-statements.html which describes the issues and workarounds.

    Multiple Active Statements (MAS) are more than one statement in the same connection that have active work (or result-sets, Multiple Active Result Sets (MARS)).

    If you are writing code which may connect to multiple DBDs then I'd avoid multiple active statements. Often they can be avoided by a simple reorganisation of your SQL. If you absolutely have to have them then you can use multiple connections but this also has other disadvantages.

    Personally, I try to avoid them as much as possible and usually find the code using multiple active statements can be rewritten to use one.

    But basically, I'm just repeating what is in the document referenced above.

    Martin
    --
    Martin J. Evans
    Easysoft Limited
    http://www.easysoft.com
  • Wm Mussatto at Jul 12, 2011 at 9:59 pm

    On Tue, July 12, 2011 05:20, Martin J. Evans wrote:
    On 12/07/11
    13:04, ZhangJun wrote:
    mostly I use mysql, DBD::mysql,
    also mssql, ODBC and sybase driver.
    I
    cannot comment on DBD::mysql (as it has been years since I last used
    it)
    but both MS SQL Server and Sybase (I believe) don't support multiple
    active statements on the same connection unless you
    enable something like
    the new MARS support in MS SQL Server (but
    it has other disadvantages). I
    wrote a small document for
    DBD::ODBC at
    >
    http://www.easysoft.com/developer/languages/perl/multiple-active-statements.html
    which describes the issues and workarounds.
    Multiple Active Statements (MAS) are more than one statement in the
    same
    connection that have active work (or result-sets, Multiple
    Active Result
    Sets (MARS)).

    If you are
    writing code which may connect to multiple DBDs then I'd avoid
    >
    multiple active statements. Often they can be avoided by a simple
    reorganisation of your SQL. If you absolutely have to have them then you
    can use multiple connections but this also has other
    disadvantages.
    Personally, I try to avoid them as
    much as possible and usually find the
    code using multiple active
    statements can be rewritten to use one.
    But
    basically, I'm just repeating what is in the document referenced
    >
    above.
    Martin
    --
    Martin J. Evans
    Easysoft Limited
    http://www.easysoft.com
    DBD::mysql supports multiple open statements with a single
    connection.� Used to think this was the standard mSQL even did so I
    thought it was the standard <sigh />

    One of the great
    frustrations I had going between MySQL and MS SQL was I had to open a
    separate connection ($dbh) for each statement.� We gave up when we
    couldn't get any clients to sign releases which were the MS license
    translated to English.�� That and I got tired of coming in at 2
    am to reset the windows server because of Code Red Worm attacks, which MS
    had known about for two years.�

    ------
    William R.
    Mussatto
    Systems Engineer
    http://www.csz.com
    909-920-9154
  • John R Pierce at Jul 12, 2011 at 5:33 pm

    On 07/12/11 1:57 AM, Martin J. Evans wrote:
    On 12/07/11 08:32, ZhangJun wrote:

    my $dbh = DBI->connect();
    my $sth1 = $dbh->prepare( $sql_a );
    my $sth2 = $dbh->prepare( $sql_b );

    $sth1->execute;
    $sth2->execute;

    while ( my ($id) = $sth1->fetchrow_array ) {
    $sth2->execute( $id )
    }


    # is it possible ?
    Sometimes. Depends on which DBD you are using and then sometimes it
    depends on which driver you are using under that DBD.

    You'll need to tell us which DBD (and possibly driver) you are using.
    I might also comment that you can generally achieve that sort of
    operation via a single statement using a SQL JOIN, and do so far more
    efficiently than what you show above, as it will reduce round trips to
    the database.

    --
    john r pierce N 37, W 122
    santa cruz ca mid-left coast
  • David Nicol at Jul 12, 2011 at 7:00 pm
    when it isn't possible, you can create two database handles, and they can
    have different attributes.

    my $dbhA = DBI->connect();
    my $dbhB = DBI->connect();
    my $sth1 = $dbhA->prepare( $sql_a );
    my $sth2 = $dbhB->prepare( $sql_b );

    $sth1->execute;
    $dbhB->begin_work; END { $dbhB->commit}


    while ( my ($id) = $sth1->fetchrow_array ) {
    state $counter = 1;
    $sth2->execute( $id );
    $counter++ % 2000 or $dbhB->commit;
    };
  • Carlson, John W. at Jul 12, 2011 at 9:19 pm
    What happens with Apache::DBI?

    -----Original Message-----
    From: David Nicol
    Sent: Tuesday, July 12, 2011 12:00 PM
    To: ZhangJun
    Cc: dbi-users@perl.org
    Subject: Re: is it possible to use two sth from same dbh at the same time ?

    when it isn't possible, you can create two database handles, and they can
    have different attributes.

    my $dbhA = DBI->connect();
    my $dbhB = DBI->connect();
    my $sth1 = $dbhA->prepare( $sql_a );
    my $sth2 = $dbhB->prepare( $sql_b );

    $sth1->execute;
    $dbhB->begin_work; END { $dbhB->commit}


    while ( my ($id) = $sth1->fetchrow_array ) {
    state $counter = 1;
    $sth2->execute( $id );
    $counter++ % 2000 or $dbhB->commit;
    };

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedJul 12, '11 at 7:32a
activeJul 12, '11 at 9:59p
posts8
users6
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase