python-sybase-misc

[python-sybase] Problem with calling stored procedures

[Harri P. <har...@tr...>]

Hello,

I think the old problem with calling some stored procedures still seems to be
there.

Namely, I still can't get any sensible results from calling sp_helpindex. 
The code is something like:


def getindex(table):
    print "getindex", table
    c = cnx.cursor()
    c.callproc("sp_helpindex", [table])
    l = c.fetchall()
    print l
    while c.nextset():
        l = c.fetchall()
        print l

cnx=Sybase.connect("<server>","<uname>","<pass>")
cnx.cursor().execute("use mydb")

getindex("mytable")


-Harri

Re: [python-sybase] Problem with calling stored procedures

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

> Hello, I think the old problem with calling some stored procedures
> still seems to be there.
> 
> Namely, I still can't get any sensible results from calling
> sp_helpindex.  The code is something like:
> 
> 
> def getindex(table):
>     print "getindex", table
>     c = cnx.cursor()
>     c.callproc("sp_helpindex", [table])
>     l = c.fetchall()
>     print l
>     while c.nextset():
>         l = c.fetchall()
>         print l
> 
> cnx=Sybase.connect("<server>","<uname>","<pass>")
> cnx.cursor().execute("use mydb")
> 
> getindex("mytable")

Hmm...  It Seems To Work For Me With 0.34 (TM)

>>> import Sybase
>>> db = Sybase.connect('SYBASE', 'sa', '')
>>> db.cursor().execute('use pubs2')
>>> c = db.cursor()
>>> c.callproc('sp_helpindex', ['titles'])
>>> for r in c.fetchall():
...     print r
... 
('titleidind          ', 'clustered, unique located on default                    ', ' title_id', 0, 0, 0)
('titleind            ', 'nonclustered located on default                         ', ' title', 0, 0, 0)
>>> c.nextset()
1
>>> for r in c.fetchall():
...     print r
... 
(0,)
>>> c.nextset()
>>> 

- Dave

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

Re[2]: [python-sybase] Problem with calling stored procedures

[Harri P. <har...@tr...>]

I replied to this from home, and the mailing list barked at that, so here
it goes again.  Sorry if you get it twice.  See below for a simple testcase
for the presumed bug.

-Harri


On Tuesday 02 April 2002 14:31, Dave Cole wrote:
> > Hello, I think the old problem with calling some stored procedures
> > still seems to be there.
> >
> > Namely, I still can't get any sensible results from calling
> > sp_helpindex.  The code is something like:
> >
> >
> > def getindex(table):
> >     print "getindex", table
> >     c = cnx.cursor()
> >     c.callproc("sp_helpindex", [table])
> >     l = c.fetchall()
> >     print l
> >     while c.nextset():
> >         l = c.fetchall()
> >         print l
> >
> > cnx=Sybase.connect("<server>","<uname>","<pass>")
> > cnx.cursor().execute("use mydb")
> >
> > getindex("mytable")
>
> Hmm...  It Seems To Work For Me With 0.34 (TM)
>
> >>> import Sybase
> >>> db = Sybase.connect('SYBASE', 'sa', '')
> >>> db.cursor().execute('use pubs2')
> >>> c = db.cursor()
> >>> c.callproc('sp_helpindex', ['titles'])
> >>> for r in c.fetchall():
>
> ...     print r
> ...
> ('titleidind          ', 'clustered, unique located on default             
>       ', ' title_id', 0, 0, 0) ('titleind            ', 'nonclustered
> located on default                         ', ' title', 0, 0, 0)
>
> >>> c.nextset()
>
> 1
>
> >>> for r in c.fetchall():
>
> ...     print r
> ...
> (0,)
>
> >>> c.nextset()
>
> - Dave

I finally fished from my disk the old testcase I sent your way last November, 
which still should exhibit the problem:

I wrote:

After some tests, I got a fairly minimal test case that should also fail for
you.  The preceding 'select' somehow throws the 'sp_helpindex' call off
balance.

-Harri

------8<------8<------8<------8<------8<------8<------8<------8<--------
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')

Re: [python-sybase] Problem with calling stored procedures

[Harri P. <ha...@ne...>]

On Tuesday 02 April 2002 14:31, Dave Cole wrote:
> > Hello, I think the old problem with calling some stored procedures
> > still seems to be there.
> >
> > Namely, I still can't get any sensible results from calling
> > sp_helpindex.  The code is something like:
> >
> >
> > def getindex(table):
> >     print "getindex", table
> >     c = cnx.cursor()
> >     c.callproc("sp_helpindex", [table])
> >     l = c.fetchall()
> >     print l
> >     while c.nextset():
> >         l = c.fetchall()
> >         print l
> >
> > cnx=Sybase.connect("<server>","<uname>","<pass>")
> > cnx.cursor().execute("use mydb")
> >
> > getindex("mytable")
>
> Hmm...  It Seems To Work For Me With 0.34 (TM)
>
> >>> import Sybase
> >>> db = Sybase.connect('SYBASE', 'sa', '')
> >>> db.cursor().execute('use pubs2')
> >>> c = db.cursor()
> >>> c.callproc('sp_helpindex', ['titles'])
> >>> for r in c.fetchall():
>
> ...     print r
> ...
> ('titleidind          ', 'clustered, unique located on default             
>       ', ' title_id', 0, 0, 0) ('titleind            ', 'nonclustered
> located on default                         ', ' title', 0, 0, 0)
>
> >>> c.nextset()
>
> 1
>
> >>> for r in c.fetchall():
>
> ...     print r
> ...
> (0,)
>
> >>> c.nextset()
>
> - Dave

I finally fished from my disk the old testcase I sent your way last November, 
which still should exhibit the problem:

I wrote:

After some tests, I got a fairly minimal test case that should also fail for
you.  The preceding 'select' somehow throws the 'sp_helpindex' call off
balance.

-Harri

------8<------8<------8<------8<------8<------8<------8<------8<--------
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')

Re: [python-sybase] Problem with calling stored procedures

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

> 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 to shed some light on the problem:

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()
    db.commit()
    cmd = "sp_helpindex %s" % table
    print db.execute(cmd)

test('authors')

- Dave

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

Re: [python-sybase] Problem with calling stored procedures

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

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

Re[2]: [python-sybase] Problem with calling stored procedures

[Harri P. <har...@tr...>]

On 04 Apr 2002 01:03:10 +1000 Dave Cole <dj...@ob...> wrote:

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

Maybe a dumb question, but why would you be opening a transaction in the
Sybase module if not explicitly asked?

I'd expect what I'm doing in python be the same as above, without the
"begin tran" line.


-Harri

Re: Re[2]: [python-sybase] Problem with calling stored procedures

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

>>>>> "Harri" == Harri Pasanen <har...@tr...> writes:

>> begin tran
>> go
>> select * from authors
>> go
>> sp_helpindex authors
>> go

Harri> Maybe a dumb question, but why would you be opening a
Harri> transaction in the Sybase module if not explicitly asked?

The begin tran is implicit as the connection is in chained transaction
mode by default.  To turn off this behaviour you need to pass the
auto_commit flag to the Sybase.connect() function.

Harri> I'd expect what I'm doing in python be the same as above,
Harri> without the "begin tran" line.

The other way to get it to happen is this:

set chained on
go
select * from authors
go
sp_helpindex authors
go

The Sybase documentation says:

        If you set chained transaction mode, Adaptive Server
        implicitly invokes a begin transaction before the following
        statements: delete, insert, open, fetch, select, and
        update. You must still explicitly close the transaction with a
        commit.

        To cancel all or part of a transaction, use the rollback
        command.  The rollback command must appear within a
        transaction; you cannot roll back a transaction after it is
        committed.

- Dave

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