| Issue 39: | cursor.skip(n) only skips 1 not n | |
| 1 person starred this issue and may be notified of changes. | Back to list |
Using pyodbc-2.1.3.win32-py2.5.exe on win2003 sp2 and xp sp2
cursor.execute('select * from table')
cursor.skip(10)
cursor.fetchall()
here skip(10) is equivalent to skip(1)
I would expect to obtain the same result as doing
cursor.skip(1) ten times
Tested against MSSQL 2005 and db2/400 v5r3.
Mar 6, 2009
Project Member
#1
mkleehammer
Status:
Investigating
Mar 6, 2009
Sorry but I could not find them, where are the tests?. I am testing manually on Python 2.5.4 IDLE and I get consistent results.
Mar 9, 2009
The tests are in the source code distributions. Download the source zip file and
look for a 'tests' directory. In there is are different files for different
databases. In your case, find sqlservertests.py
To run, pass it the connection string in quotes, like:
python sqlservertests.py "driver={SQL
Server};server=localhost;database=test;uid=user;pwd=password"
Mar 9, 2009
C:\Python25>python sqlservertests.py "Driver={SQL Server};server=192.168.0.63;da
tabase=testdb;uid=testdb;pwd=dbtest"
Did not find the pyodbc library in the build directory. Will use an installed v
ersion.
python: 2.5.4 (r254:67916, Dec 23 2008, 15:10:54) [MSC v.1310 32 bit (Intel)]
pyodbc: 2.1.3 C:\Python25\lib\site-packages\pyodbc.pyd
odbc: 03.52.0000
driver: SQLSRV32.DLL 03.85.1117
supports ODBC version 03.52
os: Windows
XP 5.1.2600 SP2 Uniprocessor Free
======================================================================
ERROR: test_datetime2 (__main__.SqlServerTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
File "sqlservertests.py", line 491, in test_datetime2
self.cursor.execute("create table t1(dt datetime2)")
ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Ser
ver]Column, parameter, or variable #1: Cannot find data type datetime2. (2715) (
SQLExecDirectW)')
----------------------------------------------------------------------
Ran 132 tests in 12.218s
FAILED (errors=1)
Mar 9, 2009
Dang it... datetime2 is a SQL2008-only feature so you need to use Driver={SQL Server
Native Client 10.0}. The script actually checks the version of SQL and ignores the
2008-only tests if you don't have 2008. However, I didn't check for the case that
you have 2008 but aren't using the latest driver. (In fact, I'm not sure I can do
that reliably.)
More importantly, test_skip doesn't actually skip more than one record, so the test
is not helping anyway. I changed it to skip 2 and it fails, just like you said.
The issue is the flag: I need to replace SQL_FETCH_NEXT with SQL_FETCH_RELATIVE.
Unit tests aren't enough if they don't cover everything.
Status:
Accepted
Mar 9, 2009
FYI: The "Dang it" was regarding my failure to have the script catch the case, not the fact that you didn't use the 2008 driver name.
Mar 10, 2009
Fixed in 426051ee130898181390b50ef465aa04d0285c8e which will be in 2.1.5 when it is released. It turns out SQLFetchScroll doesn't work like I expected when using a forward-only cursor! Because scrollable cursors can be slower and use more resources, I've simply put in a loop. This may not be the fastest way to skip records, but the overall speed will probably be best. (If you are concerned about speed, you probably want to use SQL that skips records instead of skipping them manually anyway.)
Status:
Fixed
Mar 10, 2009
Great! Thank you. I found the problem while running some tests to simulate LIMIT and OFFSET in MSSQL and DB2/400 but ended up using a list slice and not skip.
Nov 21, 2010
(No comment was entered for this change.)
Status:
Complete
|