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 <http://doc.ddart.net/mssql/sql70/set-set_23.htm>
).
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<http://msdn.microsoft.com/de-de/library/ms130812%28SQL.90%29.aspx>
)
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
-~----------~----~----~----~------~----~------~--~---
