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;
        
    }

[download]
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.

[download]
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

Replies are listed 'Best First'.
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;
}

    [download]
    ...roboticus
[reply]
[d/l]
      Thanks,...It worked!
      Blackadder
[reply]
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
[reply]

Back to Seekers of Perl Wisdom