Martin Evans wrote:
This depends on the ODBC Driver - it was never a limitation of DBD::ODBC.
With MS SQL, at least the versions <2000, it seems to be a low-level
protocol limitation. The Sybase protocol simply cannot handle more than
one active statement.
By default SQL Server did not used to support multiple active
statements if any of them were select statements. You could get around
this by changing to a dynamic cursor (I believe there is a setting in
DBD::ODBC to enable this and perhaps even a test case for it in the t
subdir of the distribution in 20SqlServer.t).
I remember having had the same problem years ago, when "my" application
suddenly had to support MS SQL in addition to Oracle. I remember that
there was a problem with dynamic cursors, but I have forgotten if it
"only" required changing a few thousand lines of code in my application
or if there was a really ugly problem regarding transactions or the
number of dynamic cursors. Bill, please read the MS SQL documentation
about dynamic cursors extra carefully before deciding to go this way.
The workaround in my application was to use one general purpose ("main")
DB connection, and -- on demand and only if the database really needed
it -- a few auxillary connections that were/are restricted by contract
(i.e. documentation) to SELECT statements that MUST NOT affect
transactions on the main connection. A thin layer over DBI had
essentially two functions, one returning the main DB connection, and one
returning a named auxillary connection, creating a new connection for
each name. For databases like Oracle that support multiple connections,
even via DBD::ODBC, both functions simply returned the main DB connection.
In MS SQL Server 2005, there is a new thing called MARS (Multiple
Active Result Sets) which allows multiple active select statements but
it has some nasty implications it you are also doing transactions.
I did not expect anything else from "Access on Steroids". ;-) (Ok,
that's only 50% MS bashing, the low level protocol has to be changed
quite dramatically to allow multiple active statements. Keeping it
backwards compatible must be a real pain.)
For other drivers it depends. I believe Oracle ODBC driver does
support multiple active statements as myodbc does. Not sure about the
rest.
I think I've done lots of tests with Oracle 8, 9 vs. MS SQL 7, 8, 2000
to find out where the "bug" was that always popped up when trying to
have more than one active connection. I'm very sure I tested to connect
to Oracle via DBD::ODBC, and did not find the "bug". So DBD::ODBC and a
"recent" Oracle ODBC driver (everything newer than the archaeological
artefact Microsoft delivered up to at least Win2k) should support more
than one active connection. DBD::Oracle definately supports multiple
active statements. With PostgreSQL, the situation is nearly identically.
I did not explicitly test that for multiple active statements, but I use
it in code that needs them and it worked fine, both with PostgreSQL's
ODBC driver and the native DBD::Pg.
Alexander
If anyone wants to report success with a particular driver and
multiple active statements I will collect them and add a FAQ.
Martin
--
Alexander Foken
mailto:[EMAIL PROTECTED] http://www.foken.de/alexander/
