Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
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 (Chancellor) 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 (Curate) 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 browsing the Monastery: (9)
As of 2015-07-04 15:05 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (60 votes), past polls