legacy database with reserved word field name

[Martin Barnard]

Continuing on my Oracle adventures, I have found a table which I need to access, which has an integer field called 'drop'.  For obvious reasons, it is disallowed. Is there any way to force this field name through the DAL (my account is read-only, so I know that I probably can't actually drop the table).

I think that the reserved_sql_keywords is a good thing, but it would be nice if we could occasionally 'force' a table or field name for legacy database access.

Can anybody think of ways of bypassing reserved words checking for legacy dbs tablenames?


Martin Barnard

[Derek]

try putting it in [brackets] or "quotes".

[Martin Barnard]

Tried. Didn't work.

--
 
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/gHOkeBz3_yA/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Massimo Di Pierro]

You can simply set DAL(..., check_reserved=None)

the problem is, will Oracle accept the SQL generated by DAL? You need to try. I do not know.

[Martin Barnard]

I had already done that. The error generated is in the Field class and is matching against REGEX_PYTHON_KEYWORDS, here:

  File "/home/www-data/web2py/gluon/dal.py", line 9139, in __init__
    raise SyntaxError('Field: invalid field name: %s' % fieldname)
SyntaxError: Field: invalid field name: drop

code at line 9139 in dal.py is:

  if not isinstance(fieldname, str) or hasattr(Table,fieldname) or \

        fieldname[0] =='_' or REGEX_PYTHON_KEYWORDS.match(fieldname):

            raise SyntaxError('Field: invalid field name: %s' % fieldname)

Would there be a simple way of forcing it to not check, at the field level maybe? It would be beneficial for the field definition to have a check_reserved flag or even a force_name=False by default flag for us poor souls wrestling with legacy applications. I could bypass this, but then I'd need to keep doing it every time I update web2py.

Martin

--

[Martin Barnard]

After tinkering with dal.py & separating out the check, the issue is with hasattr(Table,fieldname). I'm assuming that self.drop exists as part of the object creation. A possible solution would be to have a legacy flag on a field so that internally it could map to the right field, but externally it would have a safe name. Something like this would be good:

Field('drop' ,'integer', alias_name=True, alias='d_value')

Then it could be accessed via db.table_name.d_value

Martin

(and yes, I've found at least 5 tables with a "drop" field so far, sigh.)

[Massimo Di Pierro]

Can you try?

if hasattr(db.table,'drop'): delattr(db.Table,'drop')

then create the table? This is not a good fix but will help me understand.