[DB-SIG] update where current of
Stephen J. Turner
Fri, 03 Dec 1999 16:21:59 -0500
This is a multi-part message in MIME format.
Content-Type: text/plain; charset=us-ascii
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
"update ... where current of %s" % cursor.name
Stephen J. Turner <firstname.lastname@example.org>
Content-Type: text/plain; charset=us-ascii;
# connect to the database
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')
row = cur.fetchone()
if not row:
backlog, = row
# toggle the case of the backlog column and update in place
backlog = string.swapcase(backlog)