[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On Mon, Jun 8, 2009 at 2:46 PM, ddorothydidoro...@gmail.com 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.0 -0400 +++ sqlalchemy/databases/mssql.py 2009-06-08 15:31:22.0 -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() Is it possible to set this nocount off somewhere on the dsn setup configuration file? Or this can only be set at run time? Thanks, Lucas --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
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.0 -0400 +++ sqlalchemy/databases/mssql.py 2009-06-08 15:31:22.0 -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 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
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
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.0 -0400 +++ sqlalchemy/databases/mssql.py 2009-06-08 15:31:22.0 -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 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
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On Mon, Jun 8, 2009 at 5:29 PM, Michael Bayer mike...@zzzcomputing.comwrote: I'd rather a flag, or better yet in 0.6 a freetds specific dialect/url, i.e. mssql+freetds://url. I personally like the freetds dialect idea because there's a lot more issues specific to freetds than just this one. -- Michael Trier http://blog.michaeltrier.com/ http://thisweekindjango.com/ --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
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
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On May 6, 7:56 am, Ed Singleton singleto...@gmail.com wrote:
If it helps, I have finally got my system working, now using FreeTDS
0.82, SQLAlchemy 0.5.3, pymssql, Python 2.5, (all on Mac Leopard) and
SQL Server 2005 (on an WinXP vm).
With this setup, your test passes without any problems.
I also tried it out using pyodbc 2.1.5 and the test failed with this
traceback:
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) ('42000', '[42000] [FreeTDS][SQL
Server]Must declare the scalar variable @u#. (137)
(SQLExecDirectW)') u'SELECT user_name() as user_name;' []
This is a result of passing unicode statements directly to FreeTDS
which doesn't work.
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.
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
If it helps, I have finally got my system working, now using FreeTDS
0.82, SQLAlchemy 0.5.3, pymssql, Python 2.5, (all on Mac Leopard) and
SQL Server 2005 (on an WinXP vm).
With this setup, your test passes without any problems.
I also tried it out using pyodbc 2.1.5 and the test failed with this
traceback:
==
ERROR: sql_test.Tester.test_orm_relation
--
Traceback (most recent call last):
File /Users/singletoned/.envs/pyodbc25/lib/python2.5/site-packages/
nose-0.10.4-py2.5.egg/nose/case.py, line 363, in setUp
try_run(self.inst, ('setup', 'setUp'))
File /Users/singletoned/.envs/pyodbc25/lib/python2.5/site-packages/
nose-0.10.4-py2.5.egg/nose/util.py, line 453, in try_run
return func()
File /Users/singletoned/temp/sql_test.py, line 35, in setup
metadata.create_all()
File /Users/singletoned/.envs/pyodbc25/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/schema.py, line 1765, in
create_all
bind.create(self, checkfirst=checkfirst, tables=tables)
File /Users/singletoned/.envs/pyodbc25/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/engine/base.py, line 1129, in
create
self._run_visitor(self.dialect.schemagenerator, entity,
connection=connection, **kwargs)
File /Users/singletoned/.envs/pyodbc25/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/engine/base.py, line 1158, in
_run_visitor
visitorcallable(self.dialect, conn, **kwargs).traverse(element)
File /Users/singletoned/.envs/pyodbc25/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/sql/visitors.py, line 89, in
traverse
return traverse(obj, self.__traverse_options__, self._visitor_dict)
File /Users/singletoned/.envs/pyodbc25/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/sql/visitors.py, line 200, in
traverse
return traverse_using(iterate(obj, opts), obj, visitors)
File /Users/singletoned/.envs/pyodbc25/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/sql/visitors.py, line 194, in
traverse_using
meth(target)
File /Users/singletoned/.envs/pyodbc25/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/sql/compiler.py, line 807, in
visit_metadata
collection = [t for t in sql_util.sort_tables(tables) if
self._can_create(t)]
File /Users/singletoned/.envs/pyodbc25/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/sql/compiler.py, line 800, in
_can_create
return not self.checkfirst or not
self.dialect.has_table(self.connection, table.name, schema=table.schema)
File /Users/singletoned/.envs/pyodbc25/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/databases/mssql.py, line 1105,
in has_table
current_schema = schema or self.get_default_schema_name(connection)
File string, line 1, in lambda
File /Users/singletoned/.envs/pyodbc25/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/engine/base.py, line 1894, in
decorated
connection.info[key] = val = fn(self, connection)
File /Users/singletoned/.envs/pyodbc25/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/databases/mssql.py, line 1070,
in get_default_schema_name
user_name = connection.scalar(sql.text(query))
File /Users/singletoned/.envs/pyodbc25/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/engine/base.py, line 814, in
scalar
return self.execute(object, *multiparams, **params).scalar()
File /Users/singletoned/.envs/pyodbc25/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/engine/base.py, line 824, in
execute
return Connection.executors[c](self, object, multiparams, params)
File /Users/singletoned/.envs/pyodbc25/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/engine/base.py, line 874, in
_execute_clauseelement
return self.__execute_context(context)
File /Users/singletoned/.envs/pyodbc25/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/engine/base.py, line 896, in
__execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File /Users/singletoned/.envs/pyodbc25/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/engine/base.py, line 950, in
_cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor,
context)
File /Users/singletoned/.envs/pyodbc25/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/engine/base.py, line 931, in
_handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) ('42000', '[42000] [FreeTDS][SQL
Server]Must declare the scalar variable @u#. (137)
(SQLExecDirectW)') u'SELECT
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On 27 Apr, 16:01, Tom Wood thomas.a.w...@gmail.com wrote:
Hi all,
Am having a problem withSQLAlchemy0.5.3 and MSSQL. Running on a
Debian stack, using FreeTDS 0.82, pyodbc 2.1.4, Python 2.5 and
(separately) SQL Server 2000 and SQL Server 2005.
I have a fairly comparable setup, with SQLALchemy 0.5.3, Python 2.6,
FreeTDS 0.82, pyodbc 2.1.5, all running on Mac 10.5. Connecting to
SQL Server 2005 running on a virtual machine.
I've been using the connection string: mssql://user:password@/?
dsn=schematest?driver=FreeTDS
I can do simple connections using both pyodbc and sqlalchemy. I can
execute a query, though I don't have any data yet so they only return
empty results.
Trying to create tables tends to cause the following error though:
ProgrammingError: (ProgrammingError) Attempt to use a closed cursor.
None None
Running your test causes the same error, shown below. I'm a bit stuck
on what to try next, but I'll keep fiddling and let you know of
anything that works for me.
Ed
==
ERROR: sql_test.Tester.test_orm_relation
--
Traceback (most recent call last):
File /Users/singletoned/.envs/odbc/lib/python2.6/site-packages/
nose-0.10.4-py2.6.egg/nose/case.py, line 363, in setUp
try_run(self.inst, ('setup', 'setUp'))
File /Users/singletoned/.envs/odbc/lib/python2.6/site-packages/
nose-0.10.4-py2.6.egg/nose/util.py, line 453, in try_run
return func()
File /Users/singletoned/temp/sql_test.py, line 34, in setup
metadata.create_all()
File /Users/singletoned/.envs/odbc/lib/python2.6/site-packages/
SQLAlchemy-0.5.3-py2.6.egg/sqlalchemy/schema.py, line 1765, in
create_all
bind.create(self, checkfirst=checkfirst, tables=tables)
File /Users/singletoned/.envs/odbc/lib/python2.6/site-packages/
SQLAlchemy-0.5.3-py2.6.egg/sqlalchemy/engine/base.py, line 1129, in
create
self._run_visitor(self.dialect.schemagenerator, entity,
connection=connection, **kwargs)
File /Users/singletoned/.envs/odbc/lib/python2.6/site-packages/
SQLAlchemy-0.5.3-py2.6.egg/sqlalchemy/engine/base.py, line 1158, in
_run_visitor
visitorcallable(self.dialect, conn, **kwargs).traverse(element)
File /Users/singletoned/.envs/odbc/lib/python2.6/site-packages/
SQLAlchemy-0.5.3-py2.6.egg/sqlalchemy/sql/visitors.py, line 89, in
traverse
return traverse(obj, self.__traverse_options__,
self._visitor_dict)
File /Users/singletoned/.envs/odbc/lib/python2.6/site-packages/
SQLAlchemy-0.5.3-py2.6.egg/sqlalchemy/sql/visitors.py, line 200, in
traverse
return traverse_using(iterate(obj, opts), obj, visitors)
File /Users/singletoned/.envs/odbc/lib/python2.6/site-packages/
SQLAlchemy-0.5.3-py2.6.egg/sqlalchemy/sql/visitors.py, line 194, in
traverse_using
meth(target)
File /Users/singletoned/.envs/odbc/lib/python2.6/site-packages/
SQLAlchemy-0.5.3-py2.6.egg/sqlalchemy/sql/compiler.py, line 807, in
visit_metadata
collection = [t for t in sql_util.sort_tables(tables) if
self._can_create(t)]
File /Users/singletoned/.envs/odbc/lib/python2.6/site-packages/
SQLAlchemy-0.5.3-py2.6.egg/sqlalchemy/sql/compiler.py, line 800, in
_can_create
return not self.checkfirst or not self.dialect.has_table
(self.connection, table.name, schema=table.schema)
File /Users/singletoned/.envs/odbc/lib/python2.6/site-packages/
SQLAlchemy-0.5.3-py2.6.egg/sqlalchemy/databases/mssql.py, line 1105,
in has_table
current_schema = schema or self.get_default_schema_name
(connection)
File string, line 1, in lambda
File /Users/singletoned/.envs/odbc/lib/python2.6/site-packages/
SQLAlchemy-0.5.3-py2.6.egg/sqlalchemy/engine/base.py, line 1894, in
decorated
connection.info[key] = val = fn(self, connection)
File /Users/singletoned/.envs/odbc/lib/python2.6/site-packages/
SQLAlchemy-0.5.3-py2.6.egg/sqlalchemy/databases/mssql.py, line 1070,
in get_default_schema_name
user_name = connection.scalar(sql.text(query))
File /Users/singletoned/.envs/odbc/lib/python2.6/site-packages/
SQLAlchemy-0.5.3-py2.6.egg/sqlalchemy/engine/base.py, line 814, in
scalar
return self.execute(object, *multiparams, **params).scalar()
File /Users/singletoned/.envs/odbc/lib/python2.6/site-packages/
SQLAlchemy-0.5.3-py2.6.egg/sqlalchemy/engine/base.py, line 1676, in
scalar
self.connection._handle_dbapi_exception(e, None, None,
self.cursor, self.context)
File /Users/singletoned/.envs/odbc/lib/python2.6/site-packages/
SQLAlchemy-0.5.3-py2.6.egg/sqlalchemy/engine/base.py, line 931, in
_handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) Attempt to use a closed cursor.
None None
--~--~-~--~~~---~--~~
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
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
Mike ... thanks for picking this up! Just so you know, I haven't done any other testing of my change to see how it interacts with, say, db triggers that fire on the insert. I can add an another small bit of info: I also see the dialect unit test failures (test_fetchid_trigger and test_slice_mssql) against SQLAlchemy 0.5.3, otherwise same configuration as above. Just curious: were you able to reproduce the invalid cursor state exception using FreeTDS? I'm frankly nervous that there is something funny about our stack, although another developer here has been able to reproduce the problem on a separate system (Debian again.) -Tom On Apr 30, 11:06 pm, mtrier mtr...@gmail.com wrote: On Apr 30, 11:04 pm, mtrier mtr...@gmail.com wrote: Some additional info, and a possible fix: === --- lib/sqlalchemy/databases/mssql.py (revision 5930) +++ lib/sqlalchemy/databases/mssql.py (working copy) @@ -991,7 +991,7 @@ # We may have to skip over a number of result sets with no data (due to triggers, etc.) while True: try: - row = self.cursor.fetchone() + row = self.cursor.fetchall()[0] break except pyodbc.Error, e: self.cursor.nextset() I.e., calling fetchall() instead of fetchone() seems to clean up the cursor state. Also, FWIW, the original test passes just fine on Windows and pyodbc. So it's definitely a FreeTDS issue. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
Hi Lucas, I don't think #1350 applies here, but just in case, I pass-ed out the mssql dialect do_begin per the suggestion in the discussion thread referenced by that ticket: no impact on the invalid cursor state exception. -Tom Can you read over this ticket and see if maybe you are affected.http://www.sqlalchemy.org/trac/ticket/1350 If not then somebody more familiar with sa would need to look into why these tests are failing. Thanks, Lucas --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On Thu, Apr 30, 2009 at 8:36 AM, Tom Wood thomas.a.w...@gmail.com wrote: Hi Lucas, I don't think #1350 applies here, but just in case, I pass-ed out the mssql dialect do_begin per the suggestion in the discussion thread referenced by that ticket: no impact on the invalid cursor state exception. I guess at this point you would need to: 1. try doing the insert using plain pyodbc. See if pyodbc inserts the record with no problems. If pyodbc works then create a bug in sqlalchemy. If pyodbc does not work we need to find out why. Thanks, Lucas -Tom Can you read over this ticket and see if maybe you are affected.http://www.sqlalchemy.org/trac/ticket/1350 If not then somebody more familiar with sa would need to look into why these tests are failing. Thanks, Lucas -- How to create python package? http://lucasmanual.com/mywiki/PythonPaste DataHub - create a package that gets, parses, loads, visualizes data http://lucasmanual.com/mywiki/DataHub --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On Apr 29, 10:08 am, Tom Wood thomas.a.w...@gmail.com wrote: Some additional info, and a possible fix: === --- lib/sqlalchemy/databases/mssql.py (revision 5930) +++ lib/sqlalchemy/databases/mssql.py (working copy) @@ -991,7 +991,7 @@ # We may have to skip over a number of result sets with no data (due to triggers, etc.) while True: try: - row = self.cursor.fetchone() + row = self.cursor.fetchall()[0] break except pyodbc.Error, e: self.cursor.nextset() I.e., calling fetchall() instead of fetchone() seems to clean up the cursor state. This change does not affect any of the tests on Windows. So that's good. I'd like to confirm a couple of other things before we commit. Thanks a lot. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On Apr 30, 11:04 pm, mtrier mtr...@gmail.com wrote: Some additional info, and a possible fix: === --- lib/sqlalchemy/databases/mssql.py (revision 5930) +++ lib/sqlalchemy/databases/mssql.py (working copy) @@ -991,7 +991,7 @@ # We may have to skip over a number of result sets with no data (due to triggers, etc.) while True: try: - row = self.cursor.fetchone() + row = self.cursor.fetchall()[0] break except pyodbc.Error, e: self.cursor.nextset() I.e., calling fetchall() instead of fetchone() seems to clean up the cursor state. Also, FWIW, the original test passes just fine on Windows and pyodbc. So it's definitely a FreeTDS issue. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On Wed, Apr 29, 2009 at 9:08 AM, Tom Wood thomas.a.w...@gmail.com wrote:
Some additional info, and a possible fix:
I can reproduce this problem running the SQLAlchemy dialect unit
tests. Using a trunk (r5930) checkout, FreeTDS 0.82 with tds protocol
version 8.0, pyodbc 2.1.4, Python 2.5 and SQL Server 2005, I see three
test failures in dialect.mssql:
test_binary fails with:
DataError: (DataError) ('22018', '[22018] [FreeTDS][SQL Server]
Implicit conversion from data type varchar to varbinary is not
allowed. Use the CONVERT function to run this query. (257)
(SQLPrepare)') 'INSERT INTO binary_table (primary_id, data,
data_image, data_slice, misc, pickled, mypickle) VALUES
(?, ?, ?, ?, ?, ?, ?)' [1, read-only buffer for 0x842e680, size -1,
offset 0 at 0xb75c8f80, read-only buffer for 0x842e680, size -1,
offset 0 at 0xb75c8f60, read-only buffer for 0xb75e9c20, size -1,
offset 0 at 0xb75d00a0, 'binary_data_one.dat', read-only buffer for
0xb75c67a0, size -1, offset 0 at 0xb75d0180, read-only buffer for
0xb75d9d68, size -1, offset 0 at 0xb75d0100]
I'm going to ignore this for now, since it seems to be unrelated to my
problem.
However, test_fetchid_trigger and test_slice_mssql both fail with the
Invalid cursor state exception:
File /home/taw8/src/sqlalchemy-trunk/lib/sqlalchemy/engine/
base.py, line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) ('24000', '[24000] [FreeTDS][SQL
Server]Invalid cursor state (0) (SQLExecDirectW)') 'INSERT INTO foo
(bar, range) VALUES (?, ?); select scope_identity()' [1, 1]
Here's a possible fix. The following patch to mssql.py corrects my
problems, as well as the test_fetchid_trigger and test_slice_mssql
failures:
Index: lib/sqlalchemy/databases/mssql.py
===
--- lib/sqlalchemy/databases/mssql.py (revision 5930)
+++ lib/sqlalchemy/databases/mssql.py (working copy)
@@ -991,7 +991,7 @@
# We may have to skip over a number of result sets with
no data (due to triggers, etc.)
while True:
try:
- row = self.cursor.fetchone()
+ row = self.cursor.fetchall()[0]
break
except pyodbc.Error, e:
self.cursor.nextset()
I.e., calling fetchall() instead of fetchone() seems to clean up the
cursor state.
Two caveats: (1) there are many other (non dialect) test failures with
and without my patch, although the patch does reduce the number. So
maybe there is something amok with my configuration. (2) I'm only
tried this on Debian--I have no idea what would happen on Windows.
Can you read over this ticket and see if maybe you are affected.
http://www.sqlalchemy.org/trac/ticket/1350
If not then somebody more familiar with sa would need to look into why
these tests are failing.
Thanks,
Lucas
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On Apr 29, 2009, at 10:08 AM, Tom Wood thomas.a.w...@gmail.com wrote:
Some additional info, and a possible fix:
I can reproduce this problem running the SQLAlchemy dialect unit
tests. Using a trunk (r5930) checkout, FreeTDS 0.82 with tds protocol
version 8.0, pyodbc 2.1.4, Python 2.5 and SQL Server 2005, I see three
test failures in dialect.mssql:
test_binary fails with:
DataError: (DataError) ('22018', '[22018] [FreeTDS][SQL Server]
Implicit conversion from data type varchar to varbinary is not
allowed. Use the CONVERT function to run this query. (257)
(SQLPrepare)') 'INSERT INTO binary_table (primary_id, data,
data_image, data_slice, misc, pickled, mypickle) VALUES
(?, ?, ?, ?, ?, ?, ?)' [1, read-only buffer for 0x842e680, size -1,
offset 0 at 0xb75c8f80, read-only buffer for 0x842e680, size -1,
offset 0 at 0xb75c8f60, read-only buffer for 0xb75e9c20, size -1,
offset 0 at 0xb75d00a0, 'binary_data_one.dat', read-only buffer for
0xb75c67a0, size -1, offset 0 at 0xb75d0180, read-only buffer for
0xb75d9d68, size -1, offset 0 at 0xb75d0100]
I'm going to ignore this for now, since it seems to be unrelated to my
problem.
This failure has started about a month ago and I haven't had time to
investigate.
However, test_fetchid_trigger and test_slice_mssql both fail with the
Invalid cursor state exception:
File /home/taw8/src/sqlalchemy-trunk/lib/sqlalchemy/engine/
base.py, line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) ('24000', '[24000] [FreeTDS][SQL
Server]Invalid cursor state (0) (SQLExecDirectW)') 'INSERT INTO foo
(bar, range) VALUES (?, ?); select scope_identity()' [1, 1]
Here's a possible fix. The following patch to mssql.py corrects my
problems, as well as the test_fetchid_trigger and test_slice_mssql
failures:
Interesting fix. I'll apply and test against windows and pyodbc.
Index: lib/sqlalchemy/databases/mssql.py
===
--- lib/sqlalchemy/databases/mssql.py (revision 5930)
+++ lib/sqlalchemy/databases/mssql.py (working copy)
@@ -991,7 +991,7 @@
# We may have to skip over a number of result sets with
no data (due to triggers, etc.)
while True:
try:
-row = self.cursor.fetchone()
+row = self.cursor.fetchall()[0]
break
except pyodbc.Error, e:
self.cursor.nextset()
I.e., calling fetchall() instead of fetchone() seems to clean up the
cursor state.
Two caveats: (1) there are many other (non dialect) test failures with
and without my patch, although the patch does reduce the number. So
maybe there is something amok with my configuration. (2) I'm only
tried this on Debian--I have no idea what would happen on Windows.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On Mon, Apr 27, 2009 at 10:01 AM, Tom Wood thomas.a.w...@gmail.com wrote:
Hi all,
Am having a problem with SQLAlchemy 0.5.3 and MSSQL. Running on a
Debian stack, using FreeTDS 0.82, pyodbc 2.1.4, Python 2.5 and
(separately) SQL Server 2000 and SQL Server 2005.
The (nose) test below fails with the exception:
ProgrammingError: (ProgrammingError) ('24000', '[24000] [FreeTDS][SQL
Server]Invalid cursor state (0) (SQLExecDirectW)') 'INSERT INTO
activities (institution, application_id) VALUES (?, ?); select
scope_identity()' ['UMass', 1]
Complete stack can be found below.
I'd love to hear from anyone running under a comparable configuration--
whether you see the same results or not! :-)
FYI: The test succeeds using SQLAlchemy 0.5.0rc3, but fails with every
subsequent release. It also passes running against a sqllite db.
Thanks very much.
Tom Wood
University of Connecticut
# begin test code
import sqlalchemy as sa
from sqlalchemy import orm
conn = 'mssql://insert your string here'
engine = sa.create_engine(conn)
I'm not sure if I can help but if we could start with the basics and
find out what version of tds are you using and how are you connecting?
1. Are you using dsn-less or dsn connection string? @dsn ?
2. What tds version have you set in /etc/freetds/tds.dsn.template
http://lucasmanual.com/mywiki/unixODBC
Have you tried setting it to tds version 8.0?
Thanks,
Lucas
metadata = sa.MetaData(bind=engine)
applications_table = sa.Table('applications', metadata,
sa.Column('id', sa.Integer,
primary_key=True),
sa.Column('last_name', sa.types.String
(20)))
activities_table = sa.Table('activities', metadata,
sa.Column('id', sa.Integer,
primary_key=True),
sa.Column('institution', sa.types.String
(20)),
sa.Column('application_id', sa.Integer,
sa.ForeignKey('applications.id')))
Session = orm.sessionmaker()
class Application(object):
def __init__(self, last_name):
self.last_name = last_name
class Activity(object):
def __init__(self, institution):
self.institution = institution
orm.mapper(Application, applications_table, properties={'activities':
orm.relation(Activity, backref='application')})
orm.mapper(Activity, activities_table)
class Tester(object):
def setup(self):
metadata.create_all()
self.session = Session()
def teardown(self):
self.session.close()
metadata.drop_all()
def test_orm_relation(self):
app = Application(last_name='Wood')
act = Activity(institution='UConn')
act2 = Activity(institution='UMass')
app.activities.append(act)
app.activities.append(act2)
self.session.add(app)
self.session.commit()
assert act.id is not None
assert app.id is not None
assert act2.id is not None
assert act.application_id == app.id
assert act2.application_id == app.id
# begin stack crawl
ERROR: simple_test.Tester.test_orm_relation
--
Traceback (most recent call last):
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
nose-0.10.4-py2.5.egg/nose/case.py, line 182, in runTest
self.test(*self.arg)
File /home/XXX/unicode_tests/simple_test.py, line 45, in
test_orm_relation
self.session.commit()
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 673, in
commit
self.transaction.commit()
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 378, in
commit
self._prepare_impl()
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 362, in
_prepare_impl
self.session.flush()
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 1351, in
flush
self._flush(objects)
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 1422, in
_flush
flush_context.execute()
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/unitofwork.py, line 244, in
execute
UOWExecutor().execute(self, tasks)
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/unitofwork.py, line 707, in
execute
self.execute_save_steps(trans, task)
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/unitofwork.py, line 722, in
execute_save_steps
self.save_objects(trans, task)
File
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
Lucas,
Thanks for taking a peek at this!
My connection string looks like: 'mssql://user:p...@server/db?
driver=FreeTDS_82Servername=MyServer'
where FreeTDS_82 is defined in my odbcinst.ini, and MyServer is
defined in my freetds.conf.
I've tried 'tds version = 8.0' and 'tds version = 7.0' in my
freetds.conf, but still see the same behavior. I'm fairly certain
both config files are being read correctly.
Using FreeTDS 0.82, I see the exception as reported. Using FreeTDS
0.63, I see a slightly different exception:
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
DBAPIError: (Error) ('HY000', 'The driver did not supply an
error!') 'INSERT INTO activities (institution, application_id) VALUES
(?, ?); select scope_identity()' ['UMass', 1]
and the FreeTDS log includes:
17:28:26.864001 tds_submit_query(): state is PENDING
17:28:26.864015 tds_client_msg: #20019: Attempt to initiate a new
SQL Server operation with results pending.. Connection state is now
1.
which suggests to me the same problem, just being caught in a
different place.
I should also mention that simpler tests (e.g., using the ORM to save
an object with no relations) do succeed.
-Tom
On Apr 27, 12:18 pm, Lukasz Szybalski szybal...@gmail.com wrote:
On Mon, Apr 27, 2009 at 10:01 AM, Tom Wood thomas.a.w...@gmail.com wrote:
Hi all,
Am having a problem with SQLAlchemy 0.5.3 and MSSQL. Running on a
Debian stack, using FreeTDS 0.82, pyodbc 2.1.4, Python 2.5 and
(separately) SQL Server 2000 and SQL Server 2005.
The (nose) test below fails with the exception:
ProgrammingError: (ProgrammingError) ('24000', '[24000] [FreeTDS][SQL
Server]Invalid cursor state (0) (SQLExecDirectW)') 'INSERT INTO
activities (institution, application_id) VALUES (?, ?); select
scope_identity()' ['UMass', 1]
Complete stack can be found below.
I'd love to hear from anyone running under a comparable configuration--
whether you see the same results or not! :-)
FYI: The test succeeds using SQLAlchemy 0.5.0rc3, but fails with every
subsequent release. It also passes running against a sqllite db.
Thanks very much.
Tom Wood
University of Connecticut
# begin test code
import sqlalchemy as sa
from sqlalchemy import orm
conn = 'mssql://insert your string here'
engine = sa.create_engine(conn)
I'm not sure if I can help but if we could start with the basics and
find out what version of tds are you using and how are you connecting?
1. Are you using dsn-less or dsn connection string? �...@dsn ?
2. What tds version have you set in
/etc/freetds/tds.dsn.templatehttp://lucasmanual.com/mywiki/unixODBC
Have you tried setting it to tds version 8.0?
Thanks,
Lucas
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
