> If you change the test case to indicate that supports_unicode and > supports_unicode_statements = False, then it runs just fine with the > fix. Without the fix it fails as well, which indicates to me the > issue is in FreeTDS. > > I'd like to commit this but I want to have a discussion with Mike > Bayer first to be sure he's okay with it.
I had a discussion with Mike Bayer and he expressed that he was uncomfortable committing a hack that just hides the problem instead of figuring out and fixing the problem properly. As we got into the code we began to question some of the design choices surrounding that bit of code, specifically the use of the "; select scope_identity()" part. I spent quite a bit of time last night digging into the whole issue and here are my findings. First I removed the pyodbc specific code and just tried to use the base dialect code which doesn't do the "; select scope_identity()" hack but instead actually calls a separate execute in the post_exec to get the identity value. This resulted in returning None values every time. I thought it was an issue with pyodbc since they indicate so in their documentation, but it turns out a raw pyodbc script produces the correct results. I finally discovered that the reason we're getting None in this case is do to the prepared queries. Basically the prepared query is in a different scope than the post_exec so it can't get the identity value. Changing this to not use scope_identity but to use @@identity works properly. Now clearly that's not the desired solution since that will be affected by triggers, etc..., and likely the reason for the odd implementation we see of "; select scope_identity". This ensured that the identity was retrieved in the same scope, prepared statement, as the initial insert. I say all the above just more for reference documentation and not as a solution to the problem. Once I got passed the identity issue I was able to get back to the initial Invalid Cursor State problem. After lots of traces it's clear that this cursor problem is a result of something that FreeTDS is doing when retrieving the identity, but only in the case of a transaction. The problem is related to the fact that in those cases the cursor is returning more than one result. That particular cursor error occurs when you try to select an identity but have not fully selected al the results from the cursor. The perplexing part is that the return value of the second result is always None which indicates that there are no more results. Here's a breakdown of what I saw: 1. fetchall()[0] - this will cause the problem to go away as indicated above because it's fully selecting all results before the identity is retrieved. 2. fetchone; fetchone() - if I add two fetchone() statements it will also cause the problem to go away. This clearly indicates that there is a second result. 3. session.commit() - Adding a session.commit() following each insert also causes the problem to go away. So clearly it's being influenced by the open transaction. I proved this by writing raw pyodbc outside of a transaction which worked fine. So the end result of all this is that I know the situation under which it's happening, I'm pretty confident the problem is related to FreeTDS and transactions (it doesn't happen on pure pyodbc on Windows at all), but I don't know the actual statement causing it nor the proper solution. I've also been unable to produce a pure pyodbc script that reproduces this problem, but I haven't explored everything there. I did find this thread where Rick Morrison identified the same problem. http://markmail.org/message/z4egbaof35j67dgt#query:+page:1+mid:z4egbaof35j67dgt+state:results If Rick has more information about this I'd love to hear it. That thread then went off in another direction. So at this point we don't have a solution. If we decide to get rid of the "; select scope_identity()" business then that opens us up to identity problems where triggers are involved. The work around at this point is to commit following each insert. Michael --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
