cursor.execute containing multiple statements returning data does not clear desription cache between statements

[kmedcalf]

Using following code (test.py):

import apsw

def myexectrace(cursor, sql, bindings):
    print '*', sql
    print '*', cr.getdescription()
    return True

cn = apsw.Connection(':memory:')
cn.setexectrace(myexectrace)

cr = cn.cursor()
cr.execute('create table x(x integer);')
cr.execute('create table y(y text, z integer);')
cr.execute('insert into x values (1),(2),(3)')
cr.execute("insert into y values ('A',1),('B',2),('C',3)")

for row in cr.execute('select * from x; select * from y;'):
    print row
for row in cr.execute('select * from x; select * from y;'):
    print cr.getdescription()
for row in cr.execute('select * from y; select * from x;'):
    print row
for row in cr.execute('select * from y; select * from x;'):
    print cr.getdescription()
--//--
returns the following:
>test.py
* create table x(x integer);
* ()
* create table y(y text, z integer);
* ()
* insert into x values (1),(2),(3)
* ()
* insert into y values ('A',1),('B',2),('C',3)
* ()
* select * from x;
* ((u'x', u'integer'),)
(1,)
(2,)
(3,)
* select * from y;
* ((u'x', u'integer'),)
(u'A', 1)
(u'B', 2)
(u'C', 3)
* select * from x;
* ((u'x', u'integer'),)
((u'x', u'integer'),)
((u'x', u'integer'),)
((u'x', u'integer'),)
* select * from y;
* ((u'x', u'integer'),)
((u'x', u'integer'),)
((u'x', u'integer'),)
((u'x', u'integer'),)
* select * from y;
* ((u'y', u'text'), (u'z', u'integer'))
(u'A', 1)
(u'B', 2)
(u'C', 3)
* select * from x;
* ((u'y', u'text'), (u'z', u'integer'))
(1,)
(2,)
(3,)
* select * from y;
* ((u'y', u'text'), (u'z', u'integer'))
((u'y', u'text'), (u'z', u'integer'))
((u'y', u'text'), (u'z', u'integer'))
((u'y', u'text'), (u'z', u'integer'))
* select * from x;
* ((u'y', u'text'), (u'z', u'integer'))
((u'y', u'text'), (u'z', u'integer'))
((u'y', u'text'), (u'z', u'integer'))
((u'y', u'text'), (u'z', u'integer'))

Adding a printf("RESET\n"); to the start of the resetcursor indicates that the cursor is not reset between statements hence the description_cache is not cleared:

>test
***RESET***
* create table x(x integer);
* ()
***RESET***
***RESET***
* create table y(y text, z integer);
* ()
***RESET***
***RESET***
* insert into x values (1),(2),(3)
* ()
***RESET***
***RESET***
* insert into y values ('A',1),('B',2),('C',3)
* ()
***RESET***
***RESET***
* select * from x;
* ((u'x', u'integer'),)
(1,)
(2,)
(3,)
* select * from y;
* ((u'x', u'integer'),)
(u'A', 1)
(u'B', 2)
(u'C', 3)
***RESET***
***RESET***
* select * from x;
* ((u'x', u'integer'),)
((u'x', u'integer'),)
((u'x', u'integer'),)
((u'x', u'integer'),)
* select * from y;
* ((u'x', u'integer'),)
((u'x', u'integer'),)
((u'x', u'integer'),)
((u'x', u'integer'),)
***RESET***
***RESET***
* select * from y;
* ((u'y', u'text'), (u'z', u'integer'))
(u'A', 1)
(u'B', 2)
(u'C', 3)
* select * from x;
* ((u'y', u'text'), (u'z', u'integer'))
(1,)
(2,)
(3,)
***RESET***
***RESET***
* select * from y;
* ((u'y', u'text'), (u'z', u'integer'))
((u'y', u'text'), (u'z', u'integer'))
((u'y', u'text'), (u'z', u'integer'))
((u'y', u'text'), (u'z', u'integer'))
* select * from x;
* ((u'y', u'text'), (u'z', u'integer'))
((u'y', u'text'), (u'z', u'integer'))
((u'y', u'text'), (u'z', u'integer'))
((u'y', u'text'), (u'z', u'integer'))
***RESET***
***RESET***

One solution is to add Py_CLEAR for the description_cache in the cursor_step routine around line 900 after the bindings are reset for the next statement and before the exectrace hook is called. On the other hand, perhaps a call to resetcursor should be being made somewhere in this case. In any event, inserting the extra calls to clear the description_cache fixes and produces the following (correct) output:

>test
***RESET***
* create table x(x integer);
* ()
***RESET***
***RESET***
* create table y(y text, z integer);
* ()
***RESET***
***RESET***
* insert into x values (1),(2),(3)
* ()
***RESET***
***RESET***
* insert into y values ('A',1),('B',2),('C',3)
* ()
***RESET***
***RESET***
* select * from x;
* ((u'x', u'integer'),)
(1,)
(2,)
(3,)
* select * from y;
* ((u'y', u'text'), (u'z', u'integer'))
(u'A', 1)
(u'B', 2)
(u'C', 3)
***RESET***
***RESET***
* select * from x;
* ((u'x', u'integer'),)
((u'x', u'integer'),)
((u'x', u'integer'),)
((u'x', u'integer'),)
* select * from y;
* ((u'y', u'text'), (u'z', u'integer'))
((u'y', u'text'), (u'z', u'integer'))
((u'y', u'text'), (u'z', u'integer'))
((u'y', u'text'), (u'z', u'integer'))
***RESET***
***RESET***
* select * from y;
* ((u'y', u'text'), (u'z', u'integer'))
(u'A', 1)
(u'B', 2)
(u'C', 3)
* select * from x;
* ((u'x', u'integer'),)
(1,)
(2,)
(3,)
***RESET***
***RESET***
* select * from y;
* ((u'y', u'text'), (u'z', u'integer'))
((u'y', u'text'), (u'z', u'integer'))
((u'y', u'text'), (u'z', u'integer'))
((u'y', u'text'), (u'z', u'integer'))
* select * from x;
* ((u'x', u'integer'),)
((u'x', u'integer'),)
((u'x', u'integer'),)
((u'x', u'integer'),)
***RESET***
***RESET***

[kmedcalf]

Sorry for the yucky formatting. I guess you have to do something weird to get this thing to be plain text.

[rogerbinns]

I fixed the formatting. You can put 3 backticks at the beginning and end of the code sections.

[kmedcalf]

Thanks Roger. Note that in myexectrace the cr.description should be cursor.description. Result is the same either way.