iterselect() and doing other DB actions - Connection is busy with results for another command

[Brian M]

I'm attempting to process through record sets from DAL queries that can sometimes return hundreds of thousands of records. To try to keep memory usage under control I wanted to use iterselect() instead of a plain DAL select(). However, the problem I'm running into is that as I process each record I need to perform other actions against the same database but when I do so I get "Connection is busy with results for another command" because of course I haven't finished getting all of the records yet. Is there some simple way to deal with this? Seems like it'd be the norm to need to do other things with the database while using iterselect().

I'm using a Microsoft SQL Server database with pyodbc. The DAL connection is using mssql4n with {SQL Server Native Client 11.0} as an extra driver argument.

[Anthony]

Have you tried enabling the Multiple Active Result Sets option? I think you can do so by adding the following to the connection string:

DAL('mssql4://username:password@localhost/mydb?MARS_Connection=yes', ...)

Note, it may need to be all caps -- MARS_CONNECTION.

Anthony

[Brian M]

Thank you Anthony, that works!

Only issue is that while using iterselect() you apparently can't do your own db.commit() or else you'll get "Function sequence error (0) (SQLFetch)" I suspect that it may be closing the result set that your'e trying to iterate over. :\

[Anthony]

On Wednesday, January 10, 2018 at 2:48:34 PM UTC-5, Brian M wrote:

Thank you Anthony, that works!

Only issue is that while using iterselect() you apparently can't do your own db.commit() or else you'll get "Function sequence error (0) (SQLFetch)" I suspect that it may be closing the result set that your'e trying to iterate over. :\

The only other option would probably be to create a completely separate DAL() instance for the other operations, as a separate instance will also establish a separate connection to the database. If both sets of operations use some of the same tables, then you'd also have to define those models twice (you could do so by writing a function that takes a DAL instance and defines the relevant tables on it).

Anthony

[Brian M]

Yes, I thought of a different DAL instance but for my situation it isn't really necessary since I can move when I do the commit() or just store that data elsewhere (the commit was really just to store a "I made it through this datetime" checkpoint record). I was more mentioning it for the next person that might find this.