Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

Can i execute two queries connecting to same DB at a time.

by pawank86 (Novice)
on May 07, 2009 at 03:53 UTC ( #762484=perlquestion: print w/ replies, xml ) Need Help??
pawank86 has asked for the wisdom of the Perl Monks concerning the following question:

I have connected to SQL DB and i want to execute two queried at a time. For single query i execute it works fine but when i execute another query it gives error "DBD::ODBC::st execute failed: MicrosoftODBC SQL Server DriverConnection is busy with result Use of uninitialized value in concatenation (.) or string at Y:\read_mdt.pl line 26, <> line 1." Please let me know if i am doing something wrong.

20100304 Janitored by Corion: Restored content, as per Writeup Formatting Tips

Comment on Can i execute two queries connecting to same DB at a time.
Re: Can i execute two queries connecting to same DB at a time.
by Corion (Pope) on May 07, 2009 at 06:32 UTC

    How would we know without seeing your code?

    My guess is that Connection is busy with result means that you still have a statement handle open and did not ->finish() it before trying to issue another SQL statement.

    Update: Now, actually reading your title, the answer is "no, not with the DBD you're using, it seems".

      Right. MS SQL Server can handle only one active connection without sacrificing some chickens. This is a limitation of the communication protocol between the ODBC driver and the MS SQL Server, not a DBI or DBD::ODBC limitation, and it is documented in DBD::ODBC::FAQ. Other RDBMS like Oracle and PostgreSQL can handle multiple active connnections via DBI without any problems, at least with their native DBDs, but they should also support them with DBD::ODBC and a recent ODBC driver.

      Alexander

      --
      Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
        Below is the my code my ( $data_source, $user_id, $password ) = qw( PHOENIX DBADMIN DBADMIN ); my $conn_string = "driver={SQL Server};Server=$data_source;Database=$database;UID=$user_id;PWD=$password"; my $dbh = DBI->connect( "DBI:ODBC:$conn_string" ) or die $DBI::errstr; my $sql1 = "SELECT CONTENT_HASH FROM DS_STAGING_CONTENTHASH where STAGING_STATUS_GID!=210"; my $sth = $dbh->prepare( $sql1 ); $sth->execute; In this i executed only one query (above code works fine). But when i try to execute another query by my $sql2 = "SELECT CASESHARE_PATH FROM DS_CASESHARE"; my $sth2 = $dbh->prepare( $sql2 ); $sth2->execute; This gives error as i mentioned above
      I have done something similar but I used multithreading. It didn't seem to cause a problem that way.
Re: Can i execute two queries connecting to same DB at a time.
by Jenda (Abbot) on May 07, 2009 at 12:08 UTC

    Sure you can execute two queries at a time in a database. It would be a fairly useless database if you could not. You don't necessarily have to be able to execute two queries using one database CONNECTION, though. But what the heck? Just open another one.

    Jenda
    Enoch was right!
    Enjoy the last years of Rome.

      Yes, this is a workaround. But it comes at a cost: Transactions are per connection, not per program. You can't rollback two SQL commands that work on two connections. You can rollback each one separately, but not both at once. And yes, this is an important difference.

      If you use more than one connection, use only one distinct conection for database modifications (insert, update, delete), and the other connections only for reading (select). This way, transactions still work.

      Another workaround is to buffer the results from the first SQL statement, finish() it, and then process the next SQL statement.

      An even better solution is to get a proper RDBMS. There are several available, some even free as in beer and speech.

      Alexander

      --
      Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
        How standards have changed for a "proper RDBMS". Microsoft gets that design limitation from Sybase, which back in the day did a lot to convince everyone of the importance of having databases with ACID guarantees.

        And now today merely having decent performance and ACID guarantees is not enough to be called a proper RDBMS.

        Erm. Sorry to stomp on your sand castle, but ... it's not MSSQL that doesn't allow multiple open resultsets. It's DBD::ODBC. MSSQL does support that, since version 6.0.

        "Proper RDBMS". Yeah. Sure. If you want to slander, try to make sure you're not kidding.

        Jenda
        Enoch was right!
        Enjoy the last years of Rome.

Re: Can i execute two queries connecting to same DB at a time.
by mje (Curate) on May 11, 2009 at 13:05 UTC

    Issuing 2 selects on the same connection without reading all the result-set is deemed to be having "multiple active connections".

    DBD::ODBC does support multiple active statements.

    Microsoft SQL Server did not used to support multiple active statements. There were tricks you could play that allowed you to use multiple active statements but they came at a cost. Read the DBD::ODBC pod which explains some of these tricks and why they could cause you problems.

    If you have a newer SQL Server you can add "MARS_Connection=Yes" to your connection string and you will get multiple active statements. Here again, read the DBD::ODBC pod and FAQs.

Re: How to read the value of each tag from XML file.
by ww (Bishop) on Mar 02, 2010 at 12:34 UTC
    OP replaced original content and title ca 20100302. So the replies make sense, here's the original (2009) content (with markup added):

    I have connected to SQL DB and i want to execute two queried at a time. For single query i execute it works fine but when i execute another query it gives error

    "DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Conn +ection is busy with result Use of uninitialized value in concatenation (.) or string at Y:\read_m +dt.pl line 26, <> line 1."

    Please let me know if i am doing something wrong.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (8)
As of 2014-12-25 15:10 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (160 votes), past polls