[egenix-users] Strange behavior using mxODBC & SQL Server

[M.-A. Lemburg]

stuff at mailzilla.net wrote:
> I am a BlackAdder user and new to mxODBC.  I recently downloaded the 
> mxODBC package from TheKompany to use on Windows XP.  I have created a 
> script that uses mxODBC to migrate some data from a file to a MS SQL 
> Server 2000 database using the mx.ODBC.Windows package (using the "SQL 
> Server" driver as part of my dsn).
> 
> Using a cursor, I have been able to update and insert records without any 
> issues.  However, I am seeing some unusual behavior when performing 
> selects.
> 
> Consider a table that stored information about CD's, with several columns:
> genre_id (INT)
> artist (VARCHAR)
> album (VARCHAR)
> etc...
> 
> When performing a query such as:
> SELECT * FROM CD  
> WHERE genre_id = 5
> AND artist = 'artist1'
> 
> I sometimes end up with 0 rows even though there is at least 1 with the 
> criteria (as verified against SQL Server using the Enterprise Manager 
> query tool).
> 
> Oddly enough, if I issue a second query changing the AND clauss to
> AND artist = 'artist2'
> 
> I will get results.  If I then re-issue the first query (knowing that the 
> underlying data has not been modified) I get nothing... Do it again, I get 
> results.
> 
> I haven't been able to isolate the behavior, but if I do the artist2 query 
> first, that one fails and then the artist1 query it succeeds.  My 
> expectations are for both queries to succeed, always, based on the current 
> data in the table.  If I get rid of the perfectly legal AND clause the 
> query always works but that isn't a solution to the problem but it may 
> provide some more insight.
> 
> I'm not sure what is going on here and I'd appreciate any pointers.

Sounds strange indeed. You might want to try using bound parameters
in your query:

artist = ?

then pass in the data via the .execute() parameters:

.execute(sql, ('artist1',))

Other things to consider:

* upgrade to the latest MS SQL Server ODBC driver
* check the MS SQL Server Knowledgebase
* turn off connection pooling for the connection in the
   ODBC manager
* use a new cursor for each query (rather than reusing
   a single one)

The fact that you get different results from the database for the
same query suggest that something in the caching logic of the ODBC
driver is not working right.

> After reading the online docs about mxODBC I changed my code to use 
> c.executedirect(sql) to perform the queries rather than c.execute(sql) but 
> that had no effect on the problem.
> 
> I've been using SQL for a number of years (mostly MySQL and Oracle) so my 
> exposure to SQL Server is minimal at best (though, the query I'm issuing 
> shouldn't be a problem).
> 
> 
> Any help would be appreciated,
> 
> Phil
> 
> 
> _______________________________________________________________________
> eGenix.com User Mailing List                     http://www.egenix.com/
> http://lists.egenix.com/mailman/listinfo/egenix-users

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source  (#1, Jul 22 2003)
 >>> Python/Zope Products & Consulting ...         http://www.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
2003-07-01: Released mxODBC.Zope.DA for FreeBSD             1.0.6 beta 1