[web2py] MSSQL Field Name: file is not allowed

[Omi Chiba]

I just want to share my experience which takes hours to figure out. I
was reading web2py book - 03 Overview - An Image Blog, it worked fine
with SQLite and tried to use mssql server on my local PC which is also
the new experience to me...

Replaced the DAL for sqlite with mssql
db = DAL("sqlite://storage.sqlite")
=> db = DAL("mssql://ID:PASS@SERVERNAME\SQLEXPRESS/images")

db.define_table('image',
   Field('title'),
   Field('file', 'upload'))

Then It complains  "Incorrect syntax near the keyword 'file'.

... the problem was that field name "file" is reserved word for mssql
server and cannot use it even I run the statement directly on the
server..

CREATE TABLE image(
    id INT IDENTITY PRIMARY KEY,
    title VARCHAR(512) NULL,
    file VARCHAR(512) NULL
);

But it actually allow to create the field called "file" using new
table wizard.
It's very strange.