Questions/issues: 1. Will this support updatable cursors ? If so, how ? If not, can it be extended to support them ?
2. Something about the i/f spec doesn't seem quite right to me. (Probably because I've been doing way too much Java/JDBC lately ). As you may be aware, JDBC uses explicit function methods prev(), next(), first(), last(), etc. to move the cursor, and then individual getXXX()'s to retrieve the fields. Perhaps a bit too wordy for Perl (as Java ever seems to be), but I think it "feels" a bit better and maybe separates the functionality a bit more cleanly ? Ie, maybe I just want to move to the next row and apply an update to one of the fields without fetching everything...if an $sth is derived from execute_scroll() then fetchrow_XXX() doesn't move the cursor; only one of the movement i/f's can do that. 3. There are a whole host of side issues involved with updatable and scrolled cursors (e.g., if I apply an update to the current row, do I see it immediately ? Does the cursor survive a commit of updates ? If I "DELETE WHERE CURRENT ...", where is the cursor after the delete ? etc.) 4. Where do attributes end and SQL begin ? Rather, who wins when its SQL vs. DBI attribute vs.DBI method call ? Ie, if I prepare()/execute() an "SELECT * FROM MyTable FOR UPDATE", does the $sth implicitly behave as a scrollable if my platform supports scrollable ? I think this i/f needs more scrutiny/debate before rollout, due to all the add'l issues that may arise later. Even if its limited solely to read-only cursors initially, someone will eventually want updatables, so planning for them now may help avoid major API rework later. I think this area covers a lot more terrain than the bulk API (the number of usage scenarios for bulk operations is pretty small compared to all the permutations of how various cursor operations can behave and be used), so it likely could benefit from some healthy debate. My 2 cents, I now cede the soapbox... Later, Dean Arnold Presicient Corp. www.presicient.com ----- Original Message ----- From: "Jonathan Leffler" <[EMAIL PROTECTED]> To: "Tim Bunce" <[EMAIL PROTECTED]>; "Jonathan Leffler" <[EMAIL PROTECTED]> Cc: "DBI Developers Mailing List" <[EMAIL PROTECTED]> Sent: Friday, January 10, 2003 11:58 AM Subject: DBI - SCROLL CURSOR support - outline implementation > Dear Tim, > > Here is a working proof of concept for scroll cursors in pure Perl. At > least, it works with DBD::Informix, and I believe the SQL is platform > neutral enough to work on other DBMS too if you fix the DBI->connect > information. > > There are a ton of issues to resolve, but I think it demonstrates that > the basic Perl support for scroll cursors in DBI would be fairly simple > and short (and hence easily included when the drivers do not support > it). > > Issues: > * How should the user tell prepare/execute that this will be used as a > scroll cursor? > * I envisage (a variant on) the %ctl structure actually being an > attribute of the $sth, so that instead of being passed explicitly to > the fetchrow_scroll_arrayref method, it would arrive with the $sth. > * I need to track down how (whether) to modify the %ctl structure so > it contains the array directly rather than a reference to the array. > * I need to track down how (whether) to remove some other references > in the test code. > * We need to decide whether to use strings to determine the fetch > operation. They're simple, but... > * We would need to review parameter binding. > * We would need to review column binding. > * We would need to review alternatives to returning arrayref: > presumably fetchrow_scroll_array and fetchrow_scroll_hashref would > be desirable. > * We do not need to support selectall_scroll_* methods - you don't > want to fetch everything if you want to scroll. > * I assume we'd enforce rules that if a cursor is not started as a > scroll cursor, you can't decide to use it as a scroll cursor after > the (first) execute? Or is that after the prepare? I think it > probably needs to be the execute that gets told about the > scroll-ness since you would not know, in general, whether you have a > fetchable statement until after the prepare has completed. > * Are the implemented semantics of fetching before first and after > last row correct? > * How much does the prototype POD need to be fixed? > > Where do you want to go from here? > > -- > Jonathan Leffler #include <disclaimer.h> > STSM, Informix Database Engineering, IBM Data Management > Phone: +1 650-926-6921 Fax: +1 650-926-6971 Tie-line: 630-6921 > Email: [EMAIL PROTECTED] > Guardian of DBD::Informix v1.04.PC1 -- http://dbi.perl.org
