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

[stuff at mailzilla.net]

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.

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