is it possible to use two sth from same dbh at the same time ?
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 ?
Re: is it possible to use two sth from same dbh at the same time ?
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
Re: is it possible to use two sth from same dbh at the same time ?
mostly I use mysql, DBD::mysql, also mssql, ODBC and sybase driver.
Re: is it possible to use two sth from same dbh at the same time ?
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
Re: is it possible to use two sth from same dbh at the same time ?
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 pierceN 37, W 122
santa cruz ca mid-left coast
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;
};
RE: is it possible to use two sth from same dbh at the same time ?
What happens with Apache::DBI?
-Original Message-
From: David Nicol [mailto:davidni...@gmail.com]
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;
};
Re: is it possible to use two sth from same dbh at the same time ?
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
