[DB-SIG] update where current of

[Stephen J. Turner]

This is a multi-part message in MIME format.
--------------1F06F5647D70875CDF56B84C
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Chuck Jackson wrote:
> Is it possible to do the following code w/Python and informixdb?
> 
> update sometable
>     set somecolumn = "some value"
> where current of THIS_HERE_CURSOR
> 
> It's the THIS_HERE_CURSOR that I can't figure out how to address.

At first, I was going to say "no," since the DB-API spec doesn't appear
to address positioned UPDATEs and DELETEs -- only searched ones.  Then I
started digging, and the answer surprised me.  It turns out it _is_
possible, though technically it's a cheat, since it (a) is nonportable
to any other DB-API implementation, and (b) requires that you know how
informixdb generates cursor IDs.

Attached is a contrived example showing how to do it.  The magic is in
the construction of the positioned UPDATE statement:
  "update ... where current of CUR%lX" % id(cursor)

Perhaps a future revision to the DB-API spec should add a read-only
'name' attribute to cursor objects, so that this could be done more
portably as:
  "update ... where current of %s" % cursor.name

Thoughts, anyone?

--
Stephen J. Turner <sjturner@ix.netcom.com>
--------------1F06F5647D70875CDF56B84C
Content-Type: text/plain; charset=us-ascii;
 name="ifxposupd.py"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="ifxposupd.py"

import string

# connect to the database
import informixdb
db = informixdb.informixdb('stores7')

# allocate cursor and statement objects
cur = db.cursor()
stmt = db.cursor()

# create the positioned update string
update = 'update orders set backlog=? where current of CUR%lX' % id(cur)

# open an updateable cursor
cur.execute('select backlog from orders for update')
while 1:
    row = cur.fetchone()
    if not row:
        break
    backlog, = row

    # toggle the case of the backlog column and update in place
    backlog = string.swapcase(backlog)
    stmt.execute(update, (backlog,))


--------------1F06F5647D70875CDF56B84C--