My favorites | Sign in
Project Home Downloads Wiki
READ-ONLY: This project has been archived. For more information see this post.
Search
for
  Advanced search   Search tips   Subscriptions
Issue 39: cursor.skip(n) only skips 1 not n
1 person starred this issue and may be notified of changes. Back to list
Status:  Complete
Owner:  ----
Closed:  Mar 2009


 
Reported by denes1...@yahoo.ca, Mar 6, 2009
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
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.

Status: Investigating
Mar 6, 2009
#2 denes1...@yahoo.ca
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
Project Member #3 mkleehammer
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
#4 denes1...@yahoo.ca
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
Project Member #5 mkleehammer
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
Project Member #6 mkleehammer
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
Project Member #7 mkleehammer
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
#8 denes1...@yahoo.ca
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
Project Member #9 mkleehammer
(No comment was entered for this change.)
Status: Complete

Powered by Google Project Hosting