cursor.skip(n) only skips 1 not n

[GoogleCodeExporter]

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.

Original issue reported on code.google.com by denes1...@yahoo.ca on 6 Mar 2009 at 10:02

[GoogleCodeExporter]

I'm not seeing the same thing.  Can you run the SQL Server unit tests from the 
tests
directory?  There is a test named test_skip that tests this, and it seems to be 
working.

Original comment by mkleehammer on 6 Mar 2009 at 10:54

• Changed state: Investigating

[GoogleCodeExporter]

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.

Original comment by denes1...@yahoo.ca on 7 Mar 2009 at 4:14

[GoogleCodeExporter]

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"

Original comment by mkleehammer on 9 Mar 2009 at 6:57

[GoogleCodeExporter]

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)

Original comment by denes1...@yahoo.ca on 9 Mar 2009 at 10:11

[GoogleCodeExporter]

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.

Original comment by mkleehammer on 9 Mar 2009 at 11:34

• Changed state: Accepted

[GoogleCodeExporter]

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.

Original comment by mkleehammer on 10 Mar 2009 at 12:00

[GoogleCodeExporter]

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.)

Original comment by mkleehammer on 10 Mar 2009 at 6:27

• Changed state: Fixed

[GoogleCodeExporter]

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.

Original comment by denes1...@yahoo.ca on 10 Mar 2009 at 9:26

[GoogleCodeExporter]

Original comment by mkleehammer on 21 Nov 2010 at 4:44

• Changed state: Complete