[python-sybase] An Enhancement Proposal against the "another command structure has results pending" problem

[Peter Hansack]

--------------------
import Sybase

db = Sybase.connect('SYBASE','sa','',database='data',auto_commit=1)

try:
   db.execute('drop table test')
except Sybase.Error:
   pass

db.execute('''create table test (
                 key1    varchar(10)   not null,
                 value1  int           null
                                )
           ''')
--------------------


This seemingly trivial piece of code does not work as expected. It creates

     Traceback (most recent call last):
       File "a.py", line 10, in ?
	 db.execute('''create table test (
       File "/usr/lib/python2.3/site-packages/Sybase.py", line 906, in
execute
	 fetcher.start(self.arraysize)
       File "/usr/lib/python2.3/site-packages/Sybase.py", line 260, in start
	 status = self._cmd.ct_send()
       File "/usr/lib/python2.3/site-packages/Sybase.py", line 157, in
_clientmsg_cb
	 raise DatabaseError(_fmt_client(msg))
     Sybase.DatabaseError: Layer: 1, Origin: 1
     ct_send(): user api layer: external error:
     This routine cannot be called because another command structure has
results
     pending. Layer: 1, Origin: 1
     ct_cmd_drop(): user api layer: external error: This routine can be
called
     only if the command structure is idle.
     Fatal Python error: unexpected exception during garbage collection
     Aborted

if the table 'test' does not exist already.

A look through the mail archive shows, that several others have been baffled
by this
unexpected "results pending" problem and the workaround - closing and
re-opening the
connection in the exception handler - is obviously a crutch.

A somewhat deeper look into the code of Sybase.py shows, that the exception
handler
catches an (irrelevant) informational message from the callback function -
thus
breaking the code of _FetchNow.start(), which would else call
_cmd.ct_results().



We can change our code to

--------------------
import Sybase

def my_clientmsg_cb(ctx, conn, msg):
    print "+++ %s" % Sybase._fmt_client(msg)

def my_servermsg_cb(ctx, conn, msg):
    if msg.msgnumber not in (5701, 5703):
       print "+++ %s" % Sybase._fmt_server(msg)

Sybase._ctx.ct_callback(Sybase.CS_SET, Sybase.CS_CLIENTMSG_CB,
my_clientmsg_cb)
Sybase._ctx.ct_callback(Sybase.CS_SET, Sybase.CS_SERVERMSG_CB,
my_servermsg_cb)

db = Sybase.connect('SYBASE','sa','',database='data',auto_commit=1)

try:
   db.execute('drop table test')
except Sybase.Error:
   pass

db.execute('''create table test (
                 key1    varchar(10)   not null,
                 value1  int           null
                                )
           ''')
--------------------


Now the code works. I the table 'test' does not exist, we get the message

   +++ Msg 3701, Level 11, State 1, Line 1
   Cannot drop the table 'test', because it doesn't exist in the system
catalogs.

and then the table is created.

But violating the privacy of _ctx is not good style. The next refactoring
of Sybase.py may break the code.

Therefore my proposal:

The callback functions should be settable by an official method.

At first I considered additional parameters in connect(). But the callback
functions
should be changable without a reconnect, so something in the style of
set_property()
would be preferable.

Of course default handlers should be installed if the callback functions are
not set
explicitly. I would prefer to an exception an informational message as
default;
to react sensibly to an unexpected exception is rather difficult.


Any opinions?

- Peter