Re^3: retrieve data from array empty

[Corion]

My experience with DBD::SQLite tells me otherwise. DBD::SQLite does not like having more than one statement handle active.

[afoken]

DBD::SQLite does not like having more than one statement handle active.

The same is true for Microsoft SQL Server. That's a limitation of the communication protocol that the SQL Server inherited from Sybase.

So I guess that Sybase has the same problem.

Recent MS SQL Servers can execute some commands parallel to an active statement, using something like a side channel ("MARS" = "Multiple Active Result Sets"). I've never worked with that (my last project with MS SQL Server ended around 2007, and that simply used several parallel connections), but it's documented in DBD::ODBC and DBD::ODBC::FAQ (search for SQL Server). It's not full support like in Oracle. From DBD::ODBC::FAQ:

By default MS SQL Server did not used to support multiple active statements if any of them were select statements. You could get around this (with caution) by changing to a dynamic cursor. There is a "hack" in DBD::ODBC which can be used to enable MAS but you have to fully understand the implications of doing so(see "odbc_SQL_ROWSET_SIZE" in DBD::ODBC and "odbc_cursortype" in DBD::ODBC).

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 if you are also doing transactions. To enable MARS from DBD::ODBC add "MARS_Connection=Yes" to the connection string as in:

$h->DBI->connect('dbi:ODBC:DSN=mydsn;MARS_Connection=Yes;');

NOTE: Even though you may be using MS SQL Server 2005 if you are using MS SQL Server drivers you will need to use the Native Client Driver or a later MS SQL Server ODBC driver (2008 or later) to use MARS.

For other drivers it depends. I believe various Oracle ODBC drivers do support multiple active statements as myodbc does.

Think carefully before using multiple active statements. It is probably not portable and there is nearly always a better way of doing it.

And from DBD::ODBC

odbc_SQL_ROWSET_SIZE

Setting odbc_SQL_ROWSET_SIZE results in a call to SQLSetConnectAttr to set the ODBC SQL_ROWSET_SIZE (9) attribute to whatever value you set odbc_SQL_ROWSET_SIZE to.

The ODBC default for SQL_ROWSET_SIZE is 1.

Usually MS SQL Server does not support multiple active statements (MAS) i.e., you cannot have 2 or more outstanding selects. You can set odbc_SQL_ROWSET_SIZE to 2 to persuade MS SQL Server to support multiple active statements.

Setting SQL_ROWSET_SIZE usually only affects calls to SQLExtendedFetch but does allow MAS and as DBD::ODBC does not use SQLExtendedFetch there should be no ill effects to DBD::ODBC.

Be careful with this attribute as once set to anything larger than 1 (the default) you must retrieve all result-sets before the statement handle goes out of scope or you can upset the TDS protocol and this can result in a hang. With DBI this is unlikely as DBI warns when a statement goes out of scope with outstanding results.

NOTE: if you get an error saying "[Microsoft][ODBC SQL Server Driver]Invalid attribute/option identifier (SQL-HY092)" when you set odbc_SQL_ROWSET_SIZE in the connect method you need to either a) upgrade to DBI 1.616 or above b) set odbc_SQL_ROWSET_SIZE after connect.

In versions of SQL Server 2005 and later see "Multiple Active Statements (MAS)" in the DBD::ODBC::FAQ instead of using this attribute.

Thanks to Andrew Brown for the original patch.

DBD developer note: Here lies a bag of worms. [...]

Alexander