Detect, undo, and forget setinputsizes() on cursor · Issue #411 · oracle/python-oracledb (original) (raw)

If setinputsizes is not called, then one can reuse the cursor for different SQL statements without problems like so

cur.execute("select :a, :b from dual", a="foo", b="bar")
cur.execute("select :c from dual", c=999)

If it is called, however, then an exception may be raised. Consider

cur.execute("select :a, :b from dual", a="foo", b="bar")
cur.setinputsizes(**cur.bindvars)
cur.execute("select :c from dual", c=999)

>> DPY-4008: no bind placeholder named ":a" was found in the SQL text

There doesn't appear to be any way to undo this, for example

cur.execute("select :a, :b from dual", a="foo", b="bar")
cur.setinputsizes(**cur.bindvars)

# attempt to reset
cur.setinputsizes()

cur.execute("select :c from dual", c=999)

>> DPY-4008: no bind placeholder named ":a" was found in the SQL text

If we could detect if setinputsizes were called, and if the empty call setinputsizes() would have it forget, then we could safely reuse cursors in procedural programming, such as

def foo(cur):
    if cur.inputsizes_called:
        oldvars = cur.bindvars.copy()
        cur.setinputsizes() # reset
        cur.execute(...)
        if isinstance(oldvars, list): 
            cur.setinputsizes(*oldvars) # restore
        elif isinstance(oldvars, dict): 
            cur.setinputsizes(**oldvars)
    else:
        cur.execute(...)

Alternatively, the cursor could detect if a different sql statement (string id) were used and then forget on its own.