[python-sybase] Nested queries with Sybase module

[Sébastien Sablé]

Hi,

first thanks for the Sybase python module, it has been very useful to me so far.

I have a problem however with concurrency in the DBAPI version of the
API. I am trying to call an UPDATE query nested in a SELECT with two
cursors.

Here is a test case to illustrate the problem:

from unittest import TestCase, main
import Sybase

class TestConcurrency(TestCase):

    def setUp(self):
        conn = Sybase.connect('MYSERVER', MYLOGIN', 'MYPASSWD', auto_commit=1)
        c1 = conn.cursor()
        try:
            c1.execute("DROP TABLE Foo")
        except:
            pass
        conn.close()

    def test01(self):
        conn = Sybase.connect('MYSERVER', 'MYLOGIN', 'MYPASSWD', auto_commit=1)
        c1 = conn.cursor()
        c1.execute("CREATE TABLE Foo (id INT, name VARCHAR(15),
PRIMARY KEY (id))")
        c1.execute("INSERT INTO Foo (id, name) VALUES ( 1, 'foo')")
        c1.execute("INSERT INTO Foo (id, name) VALUES ( 2, 'bar')")
        c1.execute("SELECT id, name FROM Foo")
        s = [c1.fetchone()]
        c2 = conn.cursor()
        # the test fails here because of pending results
        c2.execute("UPDATE Foo SET name = 'foobar' WHERE id = 2")
        s += [c1.fetchone()]
        self.assertEqual(s, [(1, 'foo'), (2, 'bar')])

        conn.close()

if __name__ == '__main__':
    main()

When running this test, I get the following exception:

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

The open client documentation tells that it is possible to do some
nested cursor commands in some cases:

http://manuals.sybase.com/onlinebooks/group-cn/cng1251e/clcprgde/@ebt-link;pt=8207;lang=fr?target=%25N%14_9567_START_RESTART_N%25

And indeed I have been able to do something like that using the
sybasect API. But I would really prefer to be able to use DBAPI.

I tried to look further for the problem and realized that the Sybase
module is never using ct_cursor to handle cursors but it actually
calls ct_command.

So is there some way to do what I am trying to do with the Sybase
DBAPI or should I use sybasect?

Thanks in advance
regards

--
Sébastien Sablé