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 <[email protected]> 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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
