[DB-SIG] update where current of

[root]

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.

Thanks!
Chuck Jackson
Waco, TX

"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 <sjturner@ix.netcom.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:
>         break
>     backlog, = row
>
>     # toggle the case of the backlog column and update in place
>     backlog = string.swapcase(backlog)
>     stmt.execute(update, (backlog,))