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;' []
On 27 Apr 2009, at 16:01, Tom Wood 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)
> 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]
>
> >
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
