I'd rather a flag, or better yet in 0.6 a freetds specific dialect/url,
i.e. mssql+freetds://<url>.
ddorothy wrote:
>
> I have looked into this and considered what you have said. I think I
> have come up with a potential solution. It seems to be that the most
> common driver for mssql on non-windows platforms is going to be
> freeTDS. Since there appears to be no way of knowing what ODBC is
> using under the hood we should be able to safely assume that they are
> using freeTDS if not on windows. Further, there should be nominal
> overhead in setting "nocount on." So perhaps the following diff will
> offer what is needed to address this specific issue.
>
> --- sqlalchemy/databases/mssql.py 2009-06-01 13:00:36.000000000 -0400
> +++ sqlalchemy/databases/mssql.py 2009-06-08 15:31:22.000000000 -0400
> @@ -239,7 +239,7 @@
> does **not** work around
>
> """
> -import datetime, decimal, inspect, operator, re, sys, urllib
> +import datetime, decimal, inspect, operator, re, sys, urllib, os
>
> from sqlalchemy import sql, schema, exc, util
> from sqlalchemy import Table, MetaData, Column, ForeignKey, String,
> Integer
> @@ -982,6 +982,8 @@
> super(MSSQLExecutionContext_pyodbc, self).pre_exec()
> if self.compiled.isinsert and self.HASIDENT and not
> self.IINSERT \
> and len(self.parameters) == 1 and
> self.dialect.use_scope_identity:
> + if os.name != 'nt':
> + self.cursor.execute("SET NOCOUNT ON")
> self.statement += "; select scope_identity()"
>
> def post_exec(self):
> @@ -996,6 +998,8 @@
> except pyodbc.Error, e:
> self.cursor.nextset()
> self._last_inserted_ids = [int(row[0])]
> + if os.name != 'nt':
> + self.cursor.execute("SET NOCOUNT OFF")
> else:
> super(MSSQLExecutionContext_pyodbc, self).post_exec()
>
>
>
> On May 9, 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:z4egba...
>>
>> 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
-~----------~----~----~----~------~----~------~--~---
