[DB-SIG] update where current of
Fri, 03 Dec 1999 17:04:07 -0600
Many thanks!! I just got it to work interactively. I don't know that I
would have thought of the 2 cursor approach. Also, I've never seen 'id()'
before (I'm a python newbie) but I figured that kind of approach would be the
track I'd have to take.
I wrote a simple gui 'isql'-like program in perl/tk and since my conversion
I've been trying to rewrite it in python/tkinter/pmw. Actually the
"update...where current of ..." has nothing to do with the said program; I'm
just trying to also port some 4GL programs I've written. I hope to see if I
can gui-fy some stuff w/o resorting to 4J's/Dynamic 4GL.
"Stephen J. Turner" wrote:
> 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 <email@example.com>
> 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:
> backlog, = row
> # toggle the case of the backlog column and update in place
> backlog = string.swapcase(backlog)
> stmt.execute(update, (backlog,))