MS-SQL DAL Problem

[Jacob König]

Hi all,
since updating from 2.14.6 to 2.15.2 I have a problem with accessing a remote MS-SQL Database. It does not recognize certain fields and always gives me an "incorrect syntax" error.

This is the test with the "old" gluon:
>>> from gluon2146 import DAL, Field
>>> py = DAL("mssql://DRIVER={FreeTDS};SERVER=SERVERNAME\\INSTANCE;DATABASE=DATABASE;UID=USER;PWD=PASSWORD")
>>> py.define_table('quelle',
...                 Field('quelleID', type='id', writable=False, readable=False, rname='Id',label = 'Quelle ID'),
...                 Field('QName', readable=False, writable=False,rname='Name'),
...                 Field('quelle_datum', type = 'date',comment='Datum der Quelle', label = 'Quellendatum'),
...                 Field('bemerkung','text', comment='Bemerkung zur Quelle', label = 'Bemerkung'),
...                 rname = 'tQuelle',
...                 fake_migrate=True
...                )
<Table quelle (quelleID, QName, quelle_datum, bemerkung)>
>>>
... rows = py().select(py.quelle.ALL)
>>> for row in rows[0:5]:
...     print row.quelleID
...
4514
4515
4516
4517
4518
>>> for row in rows[0:5]:
...     print row.quelle_datum
...
2016-09-16
2016-09-16
2016-11-14
2016-09-16
2016-10-18
>>>

and this is with the new one:
>>> from gluon import DAL, Field
>>> py = DAL("mssql://DRIVER={FreeTDS};SERVER=SERVERNAME\\INSTANCE;DATABASE=DATABASE;UID=USER;PWD=PASSWORD")
>>> py.define_table('quelle',
...                 Field('quelleID', type='id', writable=False, readable=False, rname='Id',label = 'Quelle ID'),
...                 Field('QName', readable=False, writable=False,rname='Name'),
...                 Field('quelle_datum', type = 'date',comment='Datum der Quelle', label = 'Quellendatum'),
...                 Field('bemerkung','text', comment='Bemerkung zur Quelle', label = 'Bemerkung'),
...                 rname = 'tQuelle',
...                 fake_migrate=True
...                )
<Table quelle (quelleID, QName, quelle_datum, bemerkung)>
>>> rows = py().select(py.quelle.ALL)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/jacob/gluon/packages/dal/pydal/objects.py", line 2211, in select
    return adapter.select(self.query, fields, attributes)
  File "/home/jacob/gluon/packages/dal/pydal/adapters/base.py", line 760, in select
    return self._select_aux(sql, fields, attributes, colnames)
  File "/home/jacob/gluon/packages/dal/pydal/adapters/base.py", line 716, in _select_aux
    rows = self._select_aux_execute(sql)
  File "/home/jacob/gluon/packages/dal/pydal/adapters/base.py", line 710, in _select_aux_execute
    self.execute(sql)
  File "/home/jacob/gluon/packages/dal/pydal/adapters/__init__.py", line 67, in wrap
    return f(*args, **kwargs)
  File "/home/jacob/gluon/packages/dal/pydal/adapters/base.py", line 410, in execute
    rv = self.cursor.execute(command, *args[1:], **kwargs)
pyodbc.ProgrammingError: ('42000', "[42000] [FreeTDS][SQL Server]Incorrect syntax near 'quelle_datum'. (102) (SQLExecDirectW)")
>>>

The table already exists and I am just trying to access the Data.
If am am trying to only access the ID Field, it works.

>>> rows = py().select(py.quelle.quelleID)
>>> for row in rows:
...     print row.quelleID
...
4514
4515
4516

Does anyone know a workaround?

Thanks in advance :)

[Massimo Di Pierro]

This seems like a serious issue. Are you available to help us debug via chat?

[Manuel Vio]

Hi, recently I had an SQL issue upgrading web2py too. A DAL constructor default parameter (entity_quoting) changed his value and my application crashed.

Maybe your case differs, but you can try reverting entity_quoting to False and see if this works.

You can get more info here: https://github.com/web2py/web2py/issues/1697

[Jaco]

Hi,
after changing the entity_quoting to False in the connection string everything is working as expected.

Thank you :)