[Catalyst] Database-backed sessions: Session::Store::DBI/DBIC and MSSQL

[Stuart Watt]

I don't know if anyone else has hit these problems, and I am working on =

them, but there appear to be a set of significant issues around the DBI =

and DBIC session stores using Microsoft SQL Server as the back-end. =

These are mostly related to the fact that MSSQL only allows one active =

statement at a time. Therefore, you get errors like:

[error] Caught exception in engine =

"DBIx::Class::Storage::DBI::__ANON__(): DBI Exception: DBD::ODBC::st =

execute failed: [Microsoft][ODBC SQL Server Driver]Connection is busy =

with results for another hstmt (SQL-HY000) [for Statement "UPDATE =

sessions SET expires =3D ? WHERE id =3D ?" with ParamValues: 1=3D1245271741=
, =

2=3D'session:6b263f72b22e53043beca20c3746dee2baa3bf35'] at =

D:/perl510-20090611/site/lib/Catalyst/Plugin/Session/Store/DBI.pm line 52"

Since I was using Session::Store::DBI rather than Session::Store::DBIC I =

thought that might be the problem, but I now suspect the session =

handling needs its own connections. (On a side note, I got all sorts of =

strange error messages trying to set up Session::Store::DBIC, and I am =

not worrying about them just yet.)

Since Session::Store::DBI can be set to use its own connection, I =

thought that might resolve it. Not completely, unfortunately, and now =

the error is due to the recommended use of a text column for the data. =

It seems the DBI+DBD::ODBC likes to require LongTruncLen set to a rather =

larger value than is the default (apparently the default can even be =

zero!), so you get truncation errors when reading any session data. The =

way the statements are set up (in a batch) makes it hard to set this for =

one statement independently, and supposedly it needs to be set before a =

statement is prepared.

I had thought the transition to MSSQL would be as easy for session =

handling as it had been for model data. Not so. And no, MSSQL was not my =

idea!

All the best
Stuart
-- =

Stuart Watt
ARM Product Developer
Information Balance
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/catalyst/attachments/20090617/372b7=
7e8/attachment.htm