sqlalchemy sql.true() does not work for IBM DB2

[GoogleCodeExporter]

When using sql.true() in query filter to query data in db2. It parse the 
sql.true() to 'true', which is a string. However, if changed sql.true() to 
'True', it works well.

E.g. the following test code works well.

class TT(Base):
    __tablename__ = 'tt'

    id = Column(Integer, primary_key = True)
    btest = Column('btest', Boolean, default = True)

db2 = 
sqlalchemy.create_engine('ibm_db_sa://neutron:neutron@localhost:50000/ntrnovs?ch
arset=utf8')
session = sessionmaker( autocommit = False, autoflush = False, bind = db2)()
print session.query(TT).filter(TT.btest==True).all()[0].btest

However, if I changed the 'True' to 'sql.true()', it raise the following errors.

[root@rhel62 ~]# python db2_select.py 
Traceback (most recent call last):
  File "db2_select.py", line 18, in <module>
    print session.query(TT).filter(TT.btest==sql.true()).all()[0].btest
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py", line 2115, in all
    return list(self)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py", line 2227, in __iter__
    return self._execute_and_instances(context)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py", line 2242, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 1449, in execute
    params)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 1584, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 1698, in _execute_context
    context)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 1691, in _execute_context
    context)
  File "/usr/lib/python2.6/site-packages/ibm_db_sa/ibm_db.py", line 104, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/lib64/python2.6/site-packages/ibm_db_dbi.py", line 1334, in execute
    self._set_cursor_helper()
  File "/usr/lib64/python2.6/site-packages/ibm_db_dbi.py", line 1217, in _set_cursor_helper
    raise self.messages[len(self.messages) - 1]
sqlalchemy.exc.ProgrammingError: (ProgrammingError) 
ibm_db_dbi::ProgrammingError: SQLNumResultCols failed: [IBM][CLI 
Driver][DB2/LINUXX8664] SQL0401N  The data types of the operands for the 
operation "=" are not compatible or comparable.  SQLSTATE=42818 SQLCODE=-401 
'SELECT tt.id AS tt_id, tt.btest AS tt_btest \nFROM tt \nWHERE tt.btest = true' 
()

[root@rhel62 ~]# rpm -qa | grep sqlalchemy python-sqlalchemy-0.7.9-1

I have already report the same issue to sqlalchemy, but it seems that it is not an issue of sqlalchemy. https://bitbucket.org/zzzeek/sqlalchemy/issue/3120

Original issue reported on code.google.com by `huwei....@gmail.com` on 10 Jul 2014 at 1:20

[GoogleCodeExporter]

This is the issue with older version of SQLAlchemy(0.7.x or 0.8x), release note 
of 
SQLAlchemy(http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html) 
also stats that sql.true() will not work for backend which doesn't supports 
native Boolean constants with older version of SQLALchemy(0.7.x or 0.8.x)

Original comment by rahul.pr...@in.ibm.com on 10 Jul 2014 at 1:00

• Changed state: Accepted

[GoogleCodeExporter]

Issue 157 has been merged into this issue.

Original comment by rahul.pr...@in.ibm.com on 15 Jul 2014 at 12:16

[GoogleCodeExporter]

I have committed the changes requires and suggested by matt in google code git 
branch. It is working fine in my environment please give a try and let me know 
how it is working for you. 

Original comment by rahul.pr...@in.ibm.com on 15 Jul 2014 at 12:18

[GoogleCodeExporter]

Rahul, your fix has a typo, it should be visit_false:

def visit_fase(self, expr, **kw):

Also, you don't need the supports_native_boolean = False since that's the 
default in SQLCompiler.

Original comment by mattrie...@gmail.com on 16 Jul 2014 at 12:54

[GoogleCodeExporter]

Thanks matt, I have modified the typo error.

yes, Its not mandatory to "supports_native_boolean = False" but its good to 
have it.

Original comment by rahul.pr...@in.ibm.com on 16 Jul 2014 at 4:12

[GoogleCodeExporter]

Fix is released with ibm_db_sa-0.3.2

Original comment by rahul.pr...@in.ibm.com on 5 Nov 2014 at 6:34

• Changed state: Fixed