Hi,
I'm trying SA with MS SQL Express 2005 (Linux, pymssql, freeTDS).
there is an issue with NOT, AND, OR operators of mssql. the SQLAlchemy
translates Boolean type to MSSQL BIT data type, but... NOT on
something_of_BIT_Datatype produces error. It is stated in the msdn
that NOT, AND, OR work only on mssql-internally generated boolean
type. Do you have some idea how to preserve entire boolean semantics
in mssql?
regards,
stefan
see the example error below:
Traceback (most recent call last):
File "mssql.py", line 89, in <module>
checkWith( db_mssql)
File "mssql.py", line 72, in checkWith
print session.query( Manager).select( ~ table_Manager.c.tobeornot)
File "/home/foche/src/hor/sqlalchemy/orm/query.py", line 350, in
select
return self.select_whereclause(whereclause=arg, **kwargs)
File "/home/foche/src/hor/sqlalchemy/orm/query.py", line 359, in
select_whereclause
return self._select_statement(statement, params=params)
File "/home/foche/src/hor/sqlalchemy/orm/query.py", line 1072, in
_select_statement
return self.execute(statement, params=params, **kwargs)
File "/home/foche/src/hor/sqlalchemy/orm/query.py", line 971, in
execute
result = self.session.execute(self.mapper, clauseelement,
params=p)
File "/home/foche/src/hor/sqlalchemy/orm/session.py", line 194, in
execute
return self.connection(mapper,
close_with_result=True).execute(clause, params, **kwargs)
File "/home/foche/src/hor/sqlalchemy/engine/base.py", line 517, in
execute
return Connection.executors[c](self, object, *multiparams,
**params)
File "/home/foche/src/hor/sqlalchemy/engine/base.py", line 557, in
execute_clauseelement
return self.execute_compiled(elem.compile(dialect=self.dialect,
parameters=param), *multiparams
File "/home/foche/src/hor/sqlalchemy/engine/base.py", line 568, in
execute_compiled
self._execute_raw(context)
File "/home/foche/src/hor/sqlalchemy/engine/base.py", line 581, in
_execute_raw
self._execute(context)
File "/home/foche/src/hor/sqlalchemy/engine/base.py", line 599, in
_execute
raise exceptions.SQLError(context.statement, context.parameters,
e)
sqlalchemy.exceptions.SQLError: (DatabaseError) internal error: SQL
Server message 4145, severity 1
An expression of non-boolean type specified in a context where a
condition is expected, near 'ORDER
DB-Lib error message 20018, severity 5:
General SQL Server error: Check messages from the SQL Server.
'SELECT [Manager].tobeornot AS [Manager_tobeornot], [Manager].name AS
[Manager_name],
[Manager].duties AS [Manager_duties], [Manager].id AS [Manager_id]
\nFROM [Manager] \n
WHERE NOT [Manager].tobeornot ORDER BY [Manager].id' {}
-----------------------------------
the example script is:
--------------------------------
from sqlalchemy import *
import pymssql
db_mssql = create_engine( 'mssql://sa:[EMAIL PROTECTED]/proba?
text_as_varchar=1', module= pymssql)
#'FIX USERNAME/PASSWORD in the above lines!!!
def checkWith( db):
meta = BoundMetaData( db)
meta.engine.echo = 1
table_Manager = Table( 'Manager', meta,
Column( 'duties', type= String, ),
Column( 'name', type= String, ),
Column( 'tobeornot', type= Boolean, ),
Column( 'id', Integer, primary_key= True, ),
#Column( 'obj_id', Integer, Sequence('obj_id_seq'), ),
#for mssql only one identity column per table is allowed
)
class Manager( object):
def set( me, **kargs):
for k,v in kargs.iteritems(): setattr( me, k, v)
return me
def __str__(me): return str(me.__class__.__name__)
+':'+str(me.name)
__repr__ = __str__
meta.create_all()
mapper_Manager = mapper( Manager, table_Manager)
c = Manager().set( name= 'pencho', duties= 'many', tobeornot=
True)
session = create_session()
session.save(c)
session.flush()
print c
print session.query( Manager).select()
d = Manager().set( name= 'torencho', duties= 'bany', tobeornot=
True)
e = Manager().set( name= 'mnogoVojdMalkoIndianec', duties= 'lany',
tobeornot= False)
session = create_session()
session.save(d)
session.save(e)
session.flush()
print session.query( Manager).select( ~ table_Manager.c.tobeornot)
--~--~---------~--~----~------------~-------~--~----~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
