Re: [python-sybase] Connection unusable after an exception has been raised

[Dave C. <dj...@ob...>]

Skip Montanaro wrote:
> If a cursor's execute() method results in an error (say, from a trigger
> failing for some reason) it appears there is no recourse to continue other
> than closing the connection and starting over.  Here's the simple test case:
> 
>     c = Sybase.Connection(...)
>     c1 = c.cursor()
>     c1.execute("select * from blah")
>     c2 = c.cursor()
>     c2.execute("select 1")
> 
> The first execute() call results in a DatabaseError exception because there
> is no column "blah".  The second execute() call also fails with a
> DatabaseError, this time in ct_send.  The message is:
> 
>     ct_send(): user api layer: external error: This routine cannot be called
>     because another command structure has results pending.
> 
> How do I toss those pending results?  I've tried wrapping the c1 calls in
> c.begin()/c.rollback() calls.  I've tried closing and/or deleting c1.  I've
> tried fetching the nonexistent results of the failed execute() call.
> Nothing seems to make Sybase happy again until a new connection is created.

I think I know what is causing this.  If someone could do an experiment 
for me I will make the change and cut a new release (with the output 
hook patch as well).

The cursor class uses a _FetchLazy object to perform lazy fetching of 
rows from the server.  The _FetchLazy object has an internal state which 
tracks the current state of the connection being used to fetch results. 
  It is constructed in the IDLE state.  Looking at the Cursor.execute() 
method:

     # Discard any previous results
     self._fetcher = None

     # Prepare to retrieve new results.
     fetcher = self._fetcher = _FetchLazy(self._owner)
     cmd = fetcher._cmd
     cmd.ct_command(CS_LANG_CMD, sql)
     for name, value in params.items():
         buf = DataBuf(value)
         buf.name = name
         status = cmd.ct_param(buf)
         if status != CS_SUCCEED:
             fetcher._raise_error(Error, 'ct_param')
     self.description = fetcher.start(self.arraysize)

If an exception is raised during either the ct_command() function or 
ct_send() the fetcher is still in the IDLE state.  This means that when 
the next execute() is performed and the previous fetcher is discarded, 
no cancel action is performed.

There are two possible ways to attempt a fix.

1) Construct the Fetcher in the FETCHING state.  This will mean that any 
exception raised in the ct_command() or the ct_send() function will 
result in ct_cancel(CS_CANCEL_ALL) when the Fetcher is deleted.

     In _FetchLazy.__init__() change:
         self._state = _LAZY_IDLE
     to
         self._state = _LAZY_FETCHING

2) Set the Fetcher state to FETCHING immediately before calling 
ct_send() in _FetchLazy.start().

     In _FetchLazy.start() change:
         status = self._cmd.ct_send()
     to
         self._set_state(_LAZY_FETCHING)
         status = self._cmd.ct_send()

For various reasons I do not currently have a working Sybase server at 
the moment so would appreciate it if someone else could make the change 
and test for me.

- Dave

-- 
http://www.object-craft.com.au