PyODBC global "set nocount on"
Issue #641
resolved
The PyODBCDialect issues "set nocount on" on all connections. This is a workaround for some problems that caused "invalid cursor state" errors. This workaround is not ideal and an alternative should ideally be found. This may entail making fixes to PyODBC.
Comments (2)
-
reporter
-
reporter
- changed status to resolved
Well, the changes to support scope_identity did most of the work towards fixing this. The last issue was inserts executed with executemany. I don't think it's important to fetch IDs in that case (although I've posted on the list to check) so have disabled scope_identity in that case. With that change, I have removed the global "set nocount off" in change 3820.
- Log in to comment
BTW, the main situation this helps with is an insert, where the table has a trigger that causes another insert. In this case, with nocount OFF, the database returns two result sets, roughly: "1 row inserted", "1 row inserted". pyODBC can only read the first result set, and when you try to issue another statement, you can an error because you haven't consumed all the results. I think the root of the problem is pyODBC forcing you to consume all the results. We should see about getting that fixed.