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

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: Microsoft ODBC 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.

Replies are listed 'Best First'.
Re: Can i execute two queries connecting to same DB at a time. by Corion (Patriarch) 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".	[reply] [d/l] [select]
Re^2: Can i execute two queries connecting to same DB at a time. by afoken (Chancellor) on May 07, 2009 at 06:50 UTC
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". ;-)	[reply]
Re^3: Can i execute two queries connecting to same DB at a time. by Anonymous Monk on May 07, 2009 at 06:55 UTC
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	[reply]
Re^4: Can i execute two queries connecting to same DB at a time. by Corion (Patriarch) on May 07, 2009 at 07:02 UTC
Re^4: Can i execute two queries connecting to same DB at a time. by afoken (Chancellor) on May 07, 2009 at 07:00 UTC
Re^2: Can i execute two queries connecting to same DB at a time. by bingohighway (Acolyte) on May 07, 2009 at 09:57 UTC
I have done something similar but I used multithreading. It didn't seem to cause a problem that way.	[reply]
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.	[reply]
Re^2: Can i execute two queries connecting to same DB at a time. by afoken (Chancellor) on May 07, 2009 at 14:51 UTC
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". ;-)	[reply] [d/l]
Re^3: Can i execute two queries connecting to same DB at a time. by tilly (Archbishop) on May 07, 2009 at 20:34 UTC
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.	[reply]
Re^3: Can i execute two queries connecting to same DB at a time. by Jenda (Abbot) on May 08, 2009 at 00:37 UTC
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.	[reply]
Re^4: Can i execute two queries connecting to same DB at a time. by afoken (Chancellor) on May 09, 2009 at 01:33 UTC
Re^5: Can i execute two queries connecting to same DB at a time. by Jenda (Abbot) on May 10, 2009 at 12:05 UTC
Some notes below your chosen depth have not been shown here
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.	[reply]
Re: How to read the value of each tag from XML file. by ww (Archbishop) 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."` [download] Please let me know if i am doing something wrong.	[reply] [d/l]

Back to Seekers of Perl Wisdom