[DB-SIG] update where current of

[M.-A. Lemburg]

"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?

There was some discussion about this when we thought up the
2.0 of the spec. It turned out that named cursors are probably
not portable and thus the requirement to be able to name the cursor
or query its name was dropped.

FYI, mxODBC has APIs for naming cursors and querying the name
even if it was not named:

cursor.setcursorname(name) 
         Sets the name to be associated with the with the cursor object. There is a length limit for
         names in SQL at 18 characters. An InternalError will be raised if the name is too long or
         otherwise not useable. 

cursor.getcursorname() 
         Returns the current cursor name associated with the cursor object. This may either be the
         name given to the cursor at creation time or a name generated by the ODBC driver for it to
         use. 

and

connection.cursor([name]) 
         Constructs a new Cursor Object with the given name using the connection. If no name is
         given, the ODBC driver will determine a unique name on its own. You can query this name
         with cursor.getcursorname().


-- 
Marc-Andre Lemburg
______________________________________________________________________
Y2000:                                                    28 days left
Business:                                      http://www.lemburg.com/
Python Pages:                           http://www.lemburg.com/python/