Mysql reserved words in a legacy db

[Rene Dohmen]

Hi All,

I use web2py to create a admin interface for an older project with an
existing database in MySQL.
So I extracted the DAL info with an script.

--extracted from current mysql db --
user_db.define_table('users',
Field('user_id', 'id'),
Field('login_name', 'string'),
Field('title', 'string'),
Field('first_name', 'string'),
Field('last_name', 'string'),
Field('birthdate', 'datetime'),
Field('group', user_db.group_names,
requires=IS_IN_DB(user_db,'group_names.group_id','%(group_name)s')),
format='%(first_name)s %(last_name)s')
--
When I try to insert or update stuff web2py serves me a ticket.
Viewing the record is no problem. It even shows the dropdown with
groupnames from the linked_table.

In the documentation on
http://www.web2py.com/examples/static/epydoc/web2py.gluon.reserved_sql_keywords-pysrc.html
it says that "GROUP" is a keyword.
Is there a way to force the DAL to quote the fieldNames in its insert
and update query's? With our previous framework we didn't experience
any problems with a field "group" in a mysql table.
It would be cool if there is a way to loosen the restrictions on
reserved keywords on a per app basis.

Kind Regards

Rene Dohmen

[Cliff Kachinske]

It occurs to me you might try this

Field("'group'"....),

[Rene Dohmen]

I tried:
Field('`group`', ...),
AND
Field("'group'",...),

Resulting in a ticket:
SyntaxError: only [0-9a-zA-Z_] allowed in table and field names,
received `group`

[Massimo Di Pierro]

You cannot handle it that way. In order to allow reserved keywords as table and field names we need changes in the dal source.

massimo

[Rene Dohmen]

I did some reading in the list and several users had a problem with
it. Quoting fieldNames in postgres sql is different for MySQL.

Maybe a easy solution would be to add an extra optional parameter for
the DAL connection statement?
e.g. for MySQL:
user_db = DAL('mysql://user:pass@localhost/legacy_db', migrate=False,
quote_field_names='`')

Rene

> --