Re^2: Multiple DB Queries using DBI

*blinks* I've used all the databases mentioned and have never come across a limit on the number of open $sth's. I don't think any modern database cares about that. Maybe something like FoxPro or DB2 ...

Being right, does not endow the right to be rude; politeness costs nothing.
Being unknowing, is not the same as being stupid.
Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

Comment on Re^2: Multiple DB Queries using DBI

Replies are listed 'Best First'.
Re^3: Multiple DB Queries using DBI by ikegami (Patriarch) on Nov 18, 2004 at 19:35 UTC
It's straight out of the DBI docs: Portable applications should not assume that a new statement can be prepared and/or executed while still fetching results from a previous statement. I do not have personal experience with using Perl to talk with MS SQL or Oracle databases, but I know for sure that only one query can be active at a time per MS SQL database connection in VB. My coworker has said the same for Oracle (using an unspecified language other than VB and Perl).	[reply]
Re^4: Multiple DB Queries using DBI by dragonchild (Archbishop) on Nov 18, 2004 at 19:40 UTC
I know that for Oracle and MS SQL (which is the same as Sybase, for all practical purposes), you can have multiple sths executing at once. Heck, without that for Oracle, a lot of Perl code would mysteriously break. :-) Being right, does not endow the right to be rude; politeness costs nothing. Being unknowing, is not the same as being stupid. Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence. Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.	[reply]
Re^5: Multiple DB Queries using DBI by runrig (Abbot) on Nov 18, 2004 at 21:21 UTC
DBD::ODBC did not used to allow multiple active statement handles (because ODBC did not allow it). Support for it was added in DBD::ODBC 1.08 if you use `odbc_cursortype => DBI::SQL_CURSOR_DYNAMIC` on the connect (and it only says it "may allow some servers" to support multiple concurrent statements).	[reply] [d/l]
Re^5: Multiple DB Queries using DBI by mpeppler (Vicar) on Nov 19, 2004 at 07:56 UTC
updated: changed "physical" to "network" connections. With DBD::Sybase multiple active $sth require multiple ~~physical~~ network connections. There is a way to have multiple simultaneous queries active on a single connection by using client side cursors, but this seriously limits the type of queries that can be sent so I didn't implement it. That said - you can have multiple $sth open* at the same time as long as only one is active at a time (meaning that it has been executed and that not all results have been fetched yet.)* Michael	[reply]
Re^3: Multiple DB Queries using DBI by simonm (Vicar) on Nov 18, 2004 at 21:29 UTC
A quick Google search found at least one case: `Can't have multiple statement handles on a single database handle when AutoCommit is OFF at /usr/local/lib/perl5/site_perl/5.8.0/sun4-solaris/DBD/Sybase.pm line 105` Apparently this is because "DBD::Sybase silently opens up another database handle for you when you try to use multiple statement handles concurrently from a single database handle." <a href="http://www.cwinters.com/programming/yapc_dbi.html">*</a>	[reply] [d/l]