[web2py] Re: iterselect() and doing other DB actions - Connection is busy with results for another command
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. On Wednesday, January 10, 2018 at 3:26:16 PM UTC-6, Anthony wrote: > > 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 > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: iterselect() and doing other DB actions - Connection is busy with results for another command
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 -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: iterselect() and doing other DB actions - Connection is busy with results for another command
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. :\
On Wednesday, January 10, 2018 at 11:16:48 AM UTC-6, Anthony wrote:
>
> 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
>
> On Wednesday, January 10, 2018 at 10:48:19 AM UTC-5, Brian M wrote:
>>
>> 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.
>>
>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
[web2py] Re: iterselect() and doing other DB actions - Connection is busy with results for another command
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/test?MARS_Connection=yes', ...)
Note, it may need to be all caps -- MARS_CONNECTION.
Anthony
On Wednesday, January 10, 2018 at 10:48:19 AM UTC-5, Brian M wrote:
>
> 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.
>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
