0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?

[Tom Wood]

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

[Lukasz Szybalski]

On Mon, Apr 27, 2009 at 10:01 AM, Tom Wood <Thomas...@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

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

[Tom Wood]

Lucas,

Thanks for taking a peek at this!

My connection string looks like: 'mssql://user:pass@server/db?
driver=FreeTDS_82&Servername=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:

> 2. What tds version have you set in /etc/freetds/tds.dsn.templatehttp://lucasmanual.com/mywiki/unixODBC

[Tom Wood]

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.

[Lukasz Szybalski]

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

[Michael Trier]

On Apr 29, 2009, at 10:08 AM, Tom Wood <Thomas...@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/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:

Interesting fix. I'll apply and test against windows and pyodbc.

[Tom Wood]

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

[Lukasz Szybalski]

On Thu, Apr 30, 2009 at 8:36 AM, Tom Wood <Thomas...@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
> >
>

[mtrier]

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

[mtrier]

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

[Ed Singleton]

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

[Tom Wood]

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

[Ed Singleton]

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 user_name() as user_name;' []

[mtrier]

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

[mtrier]

> 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

[Rick Morrison]

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

[Michael Bayer]

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 !

[ddorothy]

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

> problem.  http://markmail.org/message/z4egbaof35j67dgt#query:+page:1+mid:z4egba...

[Michael Bayer]

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

[Michael Trier]

On Mon, Jun 8, 2009 at 5:29 PM, Michael Bayer <mik...@zzzcomputing.com> wrote:

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/

[phrr...@googlemail.com]

I vigorously agree.

pjjH


On Jun 8, 8:57 pm, Michael Trier <mtr...@gmail.com> wrote:

[Lukasz Szybalski]

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