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

DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt

by blackadder (Hermit)
on Nov 11, 2009 at 12:02 UTC ( #806468=perlquestion: print w/ replies, xml ) Need Help??
blackadder has asked for the wisdom of the Perl Monks concerning the following question:

Buongiorno beloved Monks,

I'm connecting to M$$QL2008 server! using DBI,DBD::ODBC I have this bit of code that searches for Constraints in a given table and then deletes them;
my $sth = $dbh->prepare("SELECT constraint_name FROM information_s +chema.constraint_column_usage WHERE TABLE_NAME = '$tbl'"); $sth->execute; my $temp = $sth->fetchrow_arrayref; foreach my $rel (@$temp) { next unless ($rel =~ /ID/); print "Relationship: $rel\n"; my $th = $dbh->prepare("ALTER TABLE Data_CentersTEST DROP CONS +TRAINT $rel"); $th->execute; }
All works well untill I actually try to delete the constraint! Perl throughs this error
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Conne +ction is busy with results for another hstmt (SQL-HY000) at U:\Doccuments\Scripts\Test\sql_do3.pl line 78.
searched google but couldn't find anything definite! Can someone shine a light on this please?

Thanks a lot!
Blackadder

Comment on DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt
Select or Download Code
Re: DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt
by roboticus (Canon) on Nov 11, 2009 at 12:07 UTC
    blackadder:

    Not all databases like to have two active statements at the same time. For this type of problem, I find it better to create two connections to the database.

    my $db1= first connection my $db2= second connection ... my $sth = $db1->prepare("SELECT constraint_name FROM information_schem +a.constraint_column_usage WHERE TABLE_NAME = '$tbl'"); $sth->execute; my $temp = $sth->fetchrow_arrayref; foreach my $rel (@$temp) { next unless ($rel =~ /ID/); print "Relationship: $rel\n"; my $th = $db2->prepare("ALTER TABLE Data_CentersTEST DROP CONSTRAI +NT $rel"); $th->execute; }
    ...roboticus
      Thanks,...It worked!
      Blackadder
Re: DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt
by mje (Deacon) on Nov 12, 2009 at 09:17 UTC

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (7)
As of 2014-04-16 06:25 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (416 votes), past polls