I am running web2py.2.0.9 and I get an error on doing INSERTS with MySQL if
I define a table with an Alias.
Apparently the DAL will attempt to do this SQL:
INSERT INTO tbl_favourite_header AS
favourite_header(CreationDate,Description,ChangeDate,MemberID) VALUES
('2013-03-01 12:12:10', 'my favourite', '2013-03-01 12:12:10', 3)
My table definition looks like this:
db.define_table(
'tbl_favourite_header',
Field('Description', 'string'),
Field('CreationDate', 'datetime'),
Field('ChangeDate', 'datetime'),
Field('MemberID', 'integer'),
Field('GroupID', 'integer'),
).with_alias('favourite_header')
I get this execption when I try to do an insert.
Traceback (most recent call last):
File "/var/www/web2py/web2py.2.0.9/gluon/restricted.py", line 209, in
restricted
exec ccode in environment
File
"/var/www/web2py/web2py.2.0.9/applications/abc/controllers/favourites.py",
line 176, in <module>
File "/var/www/web2py/web2py.2.0.9/gluon/globals.py", line 186, in
<lambda>
self._caller = lambda f: f()
File
"/var/www/web2py/web2py.2.0.9/applications/abc/controllers/favourites.py",
line 80, in create
MemberID=session.member.rid
File "/var/www/web2py/web2py.2.0.9/gluon/dal.py", line 7798, in insert
ret = self._db._adapter.insert(self,self._listify(fields))
File "/var/www/web2py/web2py.2.0.9/gluon/dal.py", line 1142, in insert
raise e
ProgrammingError: (1064, "You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax
to use near 'AS
favourite_header(CreationDate,Description,ChangeDate,MemberID) VALUES
('2013-' at line 1")
I have encountered this error before in previous version of web2py and
generally patched it by doing something like this:
diff gluon/dal.00.py gluon/dal.py
1133c1133,1134
< return 'INSERT INTO %s(%s) VALUES (%s);' % (table, keys, values)
---
> tableName = ('%s' % table).split(' ')[0]
> return 'INSERT INTO %s(%s) VALUES (%s);' % (tableName, keys,
values)
I appreciate that this is a fairly naive approach as it assumes that the
table name will never have a space in it. I wonder if someone could come up
with a more robust fix to this problem.
Chris
--
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
