SQL Server requires that you fetch all rows from a session before reusing it. For scripters, this responsibility is on your shoulders. If you do not keep fetching rows until no more are left, then you will have very erratic behavior until you reopen that connection.
To make things simpler, you can use the prefetching results
parameter to the do query event. This will cause all the rows
returned from a query to be fetched into memory before the result set is made
available to your script. Warning: this could use up a lot of memory and
cause MacSQL to crash. You should only use this parameter
when you have a general idea how much data will be returned and know there is enough
memory allocated to MacSQL to handle it.
The following sample shows such a query:
set rowsTouched to do query "select * from order_item where order_id = 1234" with prefetching results