Error with mssql database : Field is a reserved SQL word

[Khalil KHAMLICHI]

Hi, 

I am trying to create an interface to sort of manage the database of a windows software (a very common need actually), this database is under Microsoft SQL Server 2008 Express Edition, and one of its mean tables has a field called 'Order' which is a reserved word in SQL language, and using this database is just impossible from web2py because of this field name.

whenever I try to use this table I ge this error :

127.0.0.1.2012-04-18.11-58-36.d24a6ea4-8275-4626-8d72-f464db6b9e24

<class 'pyodbc.ProgrammingError'> ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'Order'. (156) (SQLExecDirectW)")

I think this question shoud go to pyodbc mailing list ... but I thought maybe someone over here has any suggestions.

Thanks in advance.

[OTTIH ARINZE]

Sometimes you need words to make identification of your columns easier and unfortunately, some of these words may be reserved words. However, you could put such words in square brackets and use them as column names

so you could put such as [Order] instead of just 'Order'

[Khalil KHAMLICHI]

Well since the DAL handles all the exchange with database . . . How can I add squares and where?

[Khalil KHAMLICHI]

looks like its a DAL issue, 

The DAL need to include the [ ] by default around column names, I also tested using " " around the columns and it works too.

Hopefully Massimo you will look at this, in the meantime I will try to hack my way in the source files.

here is a code I tested outside w2p : 

con = pyodbc.connect('DRIVER={SQL Server};SERVER=chi-c7269ceba93\\SQLEXPRESS;DATABASE=SambaData2;UID=sa;PWD=sambapos.1234')

cur = con.cursor()

>>> x = cur.execute('select * from MenuItems')

>>> x = cur.execute('select Id, Order from ScreenMenuCategories')

Traceback (most recent call last):

  File "<pyshell#15>", line 1, in <module>

    x = cur.execute('select Id, Order from ScreenMenuCategories')

ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'Order'. (156) (SQLExecDirectW)")

>>> x = cur.execute('select "Id", "Order" from ScreenMenuCategories')

>>> for i in x:

print i

(1, 0)

(2, 0)

(3, 0)

(4, 0)

(5, 0)

(6, 0)

(7, 0)

>>> x = cur.execute('select Id, [Order] from ScreenMenuCategories')

>>> for i in x:

print i

(1, 0)

(2, 0)

(3, 0)

(4, 0)

(5, 0)

(6, 0)

(7, 0)

[Massimo Di Pierro]

At this point we do not support table names and field names that are keywords in SQL. In fact the DAL has a parameter 

DAL(..., check_reserved=True)

that will check for you and will prevent you from using the field name "order". The check is normally disabled for speed.

[Khalil KHAMLICHI]

I ended up removing this field from my models, since they can accept defaults at db level, so everyone of my inserts works now.
The problem comes from the fact that the 3d party db was designed wrongly, I might not face this problem again for years but wouldnt it be wonderful if theDAL was able to work on those columns without problem?
Just by simply wrapping column names in brackets before creating queries ? Or the backquote for mysql for example?

[Massimo Di Pierro]

It is in the todo list. ;-)