db.select "limit" clause should map to "top #" on mssql databases

Bug #324049 reported by RussRyba on 2009-02-01
2
Affects Status Importance Assigned to Milestone
web.py
Low
Anand Chitipothu

Bug Description

import web
db = web.database(dbn='mssql',host='localhost',user='sa',password='sapass',db='mydata')
app = web.application(urls, globals())
render = web.template.render()
urls = (
  '/', 'get_data',
)
class get_data:
    def GET(self):
        records = db.select('some_table', limit=33)
        return render.show_data(records)

-----------------------------------------------
Running the above on a MSSQL database fails because MSSQL does not have a limit clause. It has a "top" clause which can be used for equivalence.

127.0.0.1:56305 - - [01/Feb/2009 14:05:41] "HTTP/1.1 GET /" - 500 Internal Server Error
ERR: SELECT * FROM some_table LIMIT 33
Traceback (most recent call last):
  File "C:\Python25\lib\site-packages\web.py-0.31-py2.5.egg\web\application.py", line 211, in process
    return self.handle()
  File "C:\Python25\lib\site-packages\web.py-0.31-py2.5.egg\web\application.py", line 201, in handle
    return self._delegate(fn, self.fvars, args)
  File "C:\Python25\lib\site-packages\web.py-0.31-py2.5.egg\web\application.py", line 385, in _delegate
    return handle_class(cls)
  File "C:\Python25\lib\site-packages\web.py-0.31-py2.5.egg\web\application.py", line 360, in handle_class
    return tocall(*args)
  File "C:\webpy\iditarod_server.py", line 83, in GET
    records = db.select('some_table', limit=33)
  File "c:\python25\lib\site-packages\web.py-0.31-py2.5.egg\web\db.py", line 617, in select
    return self.query(qout, processed=True)
  File "c:\python25\lib\site-packages\web.py-0.31-py2.5.egg\web\db.py", line 579, in query
    self._db_execute(db_cursor, sql_query)
  File "c:\python25\lib\site-packages\web.py-0.31-py2.5.egg\web\db.py", line 530, in _db_execute
    for x in sql_query.values()])
  File "c:\python25\lib\site-packages\pymssql.py", line 126, in execute
    self.executemany(operation, (params,))
  File "c:\python25\lib\site-packages\pymssql.py", line 152, in executemany
    raise DatabaseError, "internal error: %s" % self.__source.errmsg()
DatabaseError: internal error: SQL Server message 102, severity 15, state 1, line 1:
Incorrect syntax near '33'.
DB-Lib error message 10007, severity 5:
General SQL Server error: Check messages from the SQL Server.

----------------------------------
Running the basic command in Query Analyzer fails as well.
----------------------------------

The database select functions should check if the database engine is mssql, then map LIMIT to TOP and put it AFTER the select statement, but before the field list.

Example.

db.select('some_table',limit=4)

    use_top = False --> Select * from some_table limit 4
    use_top = True --> Select top 4 * from some_table

Changed in webpy:
assignee: nobody → anandology
importance: Undecided → Low
milestone: none → 0.32
status: New → Fix Committed
Changed in webpy:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers