[python-sybase] Problem with calling stored procedures

[Dave Cole]

> > import Sybase
> > Sybase._ctx.debug = 1
> > db = Sybase.connect('SYBASE', 'sa', '', 'pubs2')
> > 
> > def test(table):
> >     print "Test table:", table
> >     c = db.cursor()
> >     c.execute('select * from '+table)
> >     c.fetchall()
> >     c.close()
> >     cmd = "sp_helpindex %s" % table
> >     print db.execute(cmd)
> > 
> > test('authors')
> 
> Ahh...  It does fail - it returns a status code from the stored
> procedure of -6.  I have no idea what that means...
> 
> What I did discover though is that the problem is transaction related.
> If I knew a lot more about isolation levels and how they interact with
> stored procedures I might be able to give a more meaningful answer.

Try this in ISQL:

begin tran
go
select * from authors
go
sp_helpindex authors
go

Now the real mystery is why the module is not detecting and reporting
the messages associated with the failed stored procedure...

Trying something else...

>>> import Sybase
>>> db = Sybase.connect('SYBASE', 'sa', '', 'pubs2')
>>> def test(table):
...     print "Test table:", table
...     c = db.cursor()
...     c.execute('select * from '+table)
...     c.fetchall()
...     c.close()
...     cmd = "sp_helpindex %s" % table
...     print db.execute(cmd)
... 
>>> test('authors')
Test table: authors
[[(-6,)]]
>>> print Sybase._ct_errors(db._conn, 'oops')
oops
Msg 2762, Level 16, State 3:
Line 91:
The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database.

Msg 2762, Level 16, State 3:
Line 102:
The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database.

Msg 208, Level 16, State 6:
Line 305:
#spindtab not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).

So...  Now the real bug has been found - the Sybase.py module is not
detecting the failure of the stored procedure at the server side.  The
client side is working without errors and receiving the error status
from the server and it seems that my code interprets that as meaning
everything is fine.

Ooops.

- Dave

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