Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

DBI sth can't run simultaneously for SQLServer 2008

by Thai Heng (Beadle)
on Sep 17, 2013 at 00:41 UTC ( #1054365=perlquestion: print w/replies, xml ) Need Help??
Thai Heng has asked for the wisdom of the Perl Monks concerning the following question:

I learing DBI. Connect to SQLServer 2008 is sucessful,but one sth(statement handle) is active ,and another stn execut,there is something wrong as follows:
DBD::ODBC::st execute failed: [Microsoft][SQL Server Native Client 10. +0] busy connection result another command (SQL-HY000) at dumpTableInf line 33
my script as follows:
#!/usr/bin/perl -w # # ch06/tabledump: Dumps information about all the tables. use strict; use DBI; ### Connect to the database my $dbh = DBI->connect( "dbi:ODBC:SQLSERVER2008R2","sa","7354500", { RaiseError => 1 }); ### Create a new statement handle to fetch table information my $tabsth = $dbh->table_info(); ### Iterate through all the tables... while ( my ( $qual, $owner, $name, $type ) = $tabsth->fetchrow_array() + ) { ### The table to fetch data for my $table = $name; ### Build the full table name with quoting if required $table = qq{"$owner"."$table"} if defined $owner; ### The SQL statement to fetch the table metadata my $statement = "SELECT * FROM $table"; print "\n"; print "Table Information\n"; print "=================\n\n"; print "Statement: $statement\n"; ### Prepare and execute the SQL statement my $sth = $dbh->prepare( $statement ); $sth->execute(); #Attention the error my $fields = $sth->{NUM_OF_FIELDS}; print "NUM_OF_FIELDS: $fields\n\n"; print "Column Name Type Precision Scale Nul +lable?\n"; print "------------------------------ ---- --------- ----- --- +------\n\n"; ### Iterate through all the fields and dump the field information for ( my $i = 0 ; $i < $fields ; $i++ ) { my $name = $sth->{NAME}->[$i]; ### Describe the NULLABLE value my $nullable = ("No", "Yes", "Unknown")[ $sth->{NULLABLE}->[$i +] ]; ### Tidy the other values, which some drivers don't provide my $scale = $sth->{SCALE}->[$i]; my $prec = $sth->{PRECISION}->[$i]; my $type = $sth->{TYPE}->[$i]; ### Display the field information printf "%-30s %5d %4d %4d %s\n", $name, $type, $prec, $scale, $nullable; } ### Explicitly deallocate the statement resources ### because we didn't fetch all the data $sth->finish(); } exit;

Replies are listed 'Best First'.
Re: DBI sth can't run simultaneously for SQLServer 2008
by ig (Vicar) on Sep 17, 2013 at 03:43 UTC

    From DBI:

    Portable applications should not assume that a new statement can be prepared and/or executed while still fetching results from a previous statement.

    I don't know about SQL Server 2008, but based on your result, it seems it is one of those that does not support this. I never do this myself.

    You might be able to use multiple database handles.

    You could read all the table details into an array then iterate over the array, so that you have only one active statement at a time.

      I have sovled this problem according to your reply. Thank you very much.
Re: DBI sth can't run simultaneously for SQLServer 2008
by NetWallah (Canon) on Sep 17, 2013 at 05:26 UTC
    UNTESTED: (I dont have access to a SQL server at the moment):

    Try appending ";MultipleActiveResultSets=True" (enable MARS) to the connection data source in your connection. i.e

    my $dbh = DBI->connect( "dbi:ODBC:SQLSERVER2008R2;MultipleActiveResult +Sets=True","sa","7354500", { ...
    There may be alternative ways to set that option...

    Another thing you can try is to do a "FETCH ALL" in the first sth, then do a selective fetch in the subsequent statements.

                 My goal ... to kill off the slow brain cells that are holding me back from synergizing my knowledge of vertically integrated mobile platforms in local cloud-based content management system datafication.

Re: DBI sth can't run simultaneously for SQLServer 2008
by mje (Curate) on Sep 17, 2013 at 08:54 UTC

    All the other answers you've received are good ones but I'd think twice before attempting to use multiple active statements (even though you now can with MS SQL Server). See the following article Multiple Active Statements (MAS) and DBD::ODBC

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1054365]
Approved by sundialsvc4
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (6)
As of 2018-05-25 21:24 GMT
Find Nodes?
    Voting Booth?