> 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)

> 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 "/home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
> SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/unitofwork.py", line 713, in
> save_objects
>    task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
>  File "/home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
> SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/mapper.py", line 1347, in
> _save_obj
>    c = connection.execute(statement.values(value_params), params)
>  File "/home/XXX/virtual_envs/py25-pylons/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 "/home/XXX/virtual_envs/py25-pylons/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 "/home/XXX/virtual_envs/py25-pylons/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 "/home/XXX/virtual_envs/py25-pylons/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 "/home/XXX/virtual_envs/py25-pylons/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) ('24000', '[24000] [FreeTDS][SQL
> Server]Invalid cursor state (0) (SQLExecDirectW)') 'INSERT INTO
> activities (institution, application_id) VALUES (?, ?); select
> scope_identity()' ['UMass', 1]

> > 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

> 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/taw00008/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.

> 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/taw00008/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.

> If not then somebody more familiar with sa would need to look into why
> these tests are failing.

> Thanks,
> Lucas

> 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.

>> 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

> ===================================================================
> --- 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.

> > ===================================================================
> > --- 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.

> Michael

> 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.

> > > 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

> 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)
> 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

> 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;' []

> 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.

> > 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

>    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: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

> > 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

> 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 <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: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

> I'd rather a flag, or better yet in 0.6 a freetds specific dialect/url,
> i.e. mssql+freetds://<url>.

> > 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 Trierhttp://blog.michaeltrier.com/http://thisweekindjango.com/

> 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()