I love that we're putting this level of thought into the issue. I also hate that the state of MSSQL requires us to put this level of thought into the issue. that said, carry on !
On May 9, 2009, at 4:21 PM, Rick Morrison wrote: > There are some differences on what happens then the MS Windows ODBC > driver connects to an MSSQL database, and what happens when FreeTDS > connects, and I believe that the three most common problems reported > for pyodbc on FreeTDS: > > 1) string encoding issues (attempting to execute unicode > statements) > 2) transaction coordination issues (the MSSQL "autocommit" mode > problem) > 3) the multiple result set issue we see here > > are all likely artifacts of these differences. Here I'll focus only > on the third problem, the topic of this thread, but I think there > are similar solutions to the other two problems. > > Issuing the following query to MSSQL: > > insert into tablea values('foo'); select scope_identity() > > Can return either one or two distinct result sets. In default mode, > MSSQL returns TWO result sets: the first is the number of rows > performed in the INSERT (this happens with UPDATE and DELETE as > well), and the second result set is the result of the second SELECT, > and contains the just-inserted identity value (if any). > > It's possible to suppress the first result set by issuing a "SET > NOCOUNT OFF" statement, which sets the MSSQL connection to not > return the first result set (more info here). > > Now it appears that the MS Windows ODBC driver might magically omit > the first result set and only return the results of the second > result set (which is all the current SQLA tests cover, I believe), > but that's all that's happening is that the MS Windows ODBC driver > issues the "SET NOCOUNT OFF" statement upon the connection > instantiation, and that FreeTDS does not. > > That means that if no other action is taken, that issuing that > insert pair above is going to return ONE result set for a MS Windows > ODBC connection, and TWO result sets for a FreeTDS connection. So if > SQLA assumes that the first result set is going to contain the > identity values, it will work on Windows and bork on Unix, and that > pretty much matches the reported behavior so far. > > So if that's the case, there's two possible fixes: > > a) Issue the SET NOCOUNT OFF at connection-establishment time, > so both flavors of connection will behave the same. > > or, > > b) Process the list of returned result sets and determine which > set contains the identity value (more into on that here on MSDN) > > I think that multi-result set handling was recently added to pyodbc, > so (b) just recently became possible, but all things being equal, > the (a) option may be easier, especially because there's other magic > words that the MS Windows ODBC driver utters when establishing a > connection as well, issuing spells to turn off autocommit mode for > example, and other things that can subtlety (and not so subtlety) > affect the outcomes of queries issued over that connection. > > So assuming going with plan (a), a full fix for the FreeTDS + pyodbc > problem is going to involve diagnosing what the list of those > incantations are, and coming up with what amounts to an > "initialization script" of SQL statements that should be sent over a > newly established pyodbc connection to make sure it's in a known > state before returning it for user operations. > > As far as the content of that init script goes, it STM that it > should be a matter of running a query trace tool on the server side > to see what SQL is sent when a Windows ODBC connection is made, and > basically duplicating that stream of statements for FreeTDS > connections. > > But the other piece of the puzzle is then "which init script gets > sent upon connection establishment?" You'll need to know if it's a > Windows connection or a FreeTDS connection. Is there a > straightforward way to determine if the connection being made is > over a Windows driver, a commercial UNIX driver like EasySoft, or > over FreeTDS, or should SQLA just punt, and leave that sort of thing > up to the user to figure out? > > I'm travelling this weekend, but I can maybe spare some time next > week to look at this, or Mike/Micheal: if you think there's enough > here to work with, feel free to run with it. > > Rick > > On Sat, May 9, 2009 at 12:41 AM, mtrier <mtr...@gmail.com> wrote: > > > 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 -~----------~----~----~----~------~----~------~--~---