python-sybase-misc

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

[Skip M. <sk...@po...>]

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.

Is there a better (less sledgehammer-like) way to recover from errors?

Thanks,

-- 
Skip Montanaro
Got spam? http://www.spambayes.org/
sk...@po...

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

[Josh C. <na...@gm...>]

I'd like to know this too, I've run into the same problems.

-Josh


On Fri, 3 Sep 2004 13:45:07 -0500, Skip Montanaro <sk...@po...> 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.
> 
> Is there a better (less sledgehammer-like) way to recover from errors?
> 
> Thanks,
> 
> --
> Skip Montanaro
> Got spam? http://www.spambayes.org/
> sk...@po...
> _______________________________________________
> Python-sybase mailing list
> Pyt...@ww...
> https://www.object-craft.com.au/cgi-bin/mailman/listinfo/python-sybase
>

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

[Alex S. <ale...@gm...>]

On Fri, 3 Sep 2004 14:04:50 -0500, Josh Close <na...@gm...> wrote:
> I'd like to know this too, I've run into the same problems.

Me too. I've been doing the same thing as Skip, not really satisfying.

cheers, Alex
-- 
Alex Suzuki | as...@cy... | http://n.ethz.ch/student/asuzuki/
"Computer Science is no more about computers than astronomy
 is about telescopes." - E.W.Dijkstra

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

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

[Skip M. <sk...@po...>]

Sorry to take so long to get back to this problem.  Squeaky wheels and all,
you know...

    >> 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")

    ...

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

    ...

    Dave> There are two possible ways to attempt a fix.

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

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

This didn't work.

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

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

This did...

Thanks,

Skip

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

[Skip M. <sk...@po...>]

Perhaps I spoke a bit too soon...

    Dave> There are two possible ways to attempt a fix.

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

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

    Skip> This didn't work.

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

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

    Skip> This did...

Solution #2 worked on Solaris/Intel, but not on Linux (neither did solution
#1).  Cursors created after an error still give a ct_send() error with this
message when their execute() method is called:

    ct_send(): user api layer: external error: This routine cannot be called
    because another command structure has results pending.

Skip

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

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

Skip Montanaro wrote:
> Perhaps I spoke a bit too soon...
> 
>     Dave> There are two possible ways to attempt a fix.
> 
>     Dave> 1) Construct the Fetcher in the FETCHING state.  This will mean
>     Dave>    that any exception raised in the ct_command() or the ct_send()
>     Dave>    function will result in ct_cancel(CS_CANCEL_ALL) when the
>     Dave>    Fetcher is deleted.
> 
>     Dave>      In _FetchLazy.__init__() change:
>     Dave>          self._state = _LAZY_IDLE
>     Dave>      to
>     Dave>          self._state = _LAZY_FETCHING
> 
>     Skip> This didn't work.
> 
>     Dave> 2) Set the Fetcher state to FETCHING immediately before calling
>     Dave>    ct_send() in _FetchLazy.start().
> 
>     Dave>      In _FetchLazy.start() change:
>     Dave>          status = self._cmd.ct_send()
>     Dave>      to
>     Dave>          self._set_state(_LAZY_FETCHING)
>     Dave>          status = self._cmd.ct_send()
> 
>     Skip> This did...
> 
> Solution #2 worked on Solaris/Intel, but not on Linux (neither did solution
> #1).  Cursors created after an error still give a ct_send() error with this
> message when their execute() method is called:
> 
>     ct_send(): user api layer: external error: This routine cannot be called
>     because another command structure has results pending.

Hmmm...  Looks like I have to do some more research into the CT library 
state machine.

- Dave

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