Dean Arnold wrote:
No. N/A. Maybe. Any ideas?Questions/issues:1. Will this support updatable cursors? If so, how? If not, can it be extended to support them?
Since Informix does not support updatable scrollable cursors, I have no immediate need of the concept, but I know they are intended to exist by the [JO]DBC standards. I also know that (if the functionality is provided at all) the Informix implementations jump through hoops to provide client-side updatable scrollable cursors. However, I've not investigated exactly how bad those hoops are.
2. Something about the i/f spec doesn't seem quite right to me.SQLFetchScroll is the ODBC function - it takes a statement handle, a fetch orientation, and an offset. The proposed Perl interface is pretty close to that! The ODBC interface includes a 'bookmark' option and does not include 'current', though that is not a big loss since it is directly equivalent to Relative 0.
(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.
I'm not clear how you update a row via the cursor without fetching it first -- but then I'm not clear how the updates occur to the database
which can affect the scroll cursor contents. Is it only the DELETE and UPDARTE operations executed with 'WHERE CURRENT OF' or does it include unrelated (searched) updates - INSERT, DELETE and UPDATE operations?
For example, consider a scroll cursor on "SELECT a.*, b.* FROM OneTable A, TwoTable B WHERE A.Column = B.Column". Now suppose there are three rows in OneTable with Column = 123 and there is one row in TwoTable with Column = 123. Now, suppose the scroll cursor has one of these three rows visible, and the user executes $dbh->do('DELETE FROM TwoTable WHERE Column = 123'). If the select statement were re-executed (and nothing else had changed), then there'd be three rows fewer in the result set. How is a client-side implementation supposed to work out that three rows from result set need to be removed (and which ones they are). Note that the DELETE was completely independent of anything to do with the scrollable $sth, beyond sharing the same $dbh. Of course, if you can't have multiple statements at work on a single $dbh - permitted by the DBI - then the problem doesn't arise in quite that form, but if $dbh1 has the scrollable cursor and $dbh2 has the delete operation, what then. And, of course, isolation levels come into play too. If the scrollable cursor is running at SERIALIZABLE isolation, then independent connections cannot interfere with each other - and $dbh2 can't mess with the data that $dbh1 is reading. OTOH, when you have multiple statements runnable on a single $dbh, then you can modify the database with one statement without running into isolation constraints. Now suppose the scroll cursor has not yet reached the position where the three rows would have appeared? What about if it has gone beyond that position? Are you allowed to have an ORDER BY clause in your scrollable cursor? Are you allowed to have joins in your scrollable cursor? If not, what about aggregates, etc?
It sounds a bit as if the Java code expects you to think as if you are playing with indexed-sequential files, which is badly non-relational. There's an element of 'cursors are non-relational, doubly so scroll cursors', too - independent of JDBC or ODBC.
3. There are a whole host of side issues involved with updatable andThe default implementation would have to set the ODBC SQLGetInfo attributes to an appropriately low level of ability. That is, the default implementation would indicate that it only deals with non-updatable cursors which are not aware of changes that occur, etc. ODBC permits this...
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.)
In Informix parlance, if you have a scroll cursor with hold, then the cursor retains its position across transaction boundaries; otherwise, the cursor is closed. Of course, the DBI AutoCommit semantics gets severely in the way - you have to use WITH HOLD cursors most of the time if AutoCommit is on to get remotely sane behaviour out of the system.
I'm not sure I understand the preamble to the last question.4. Where do attributes end and SQL begin? Rather, who wins when it's 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?
The answer to the last question is "No". The intention is that you'd have to call $sth->setattr(...) before doing $sth->execute to make the cursor scrollable. [And, incidentally, that is going to require large changes in DBD::Informix; at the moment, the cursor is declared in the $sth->prepare method (because that way I don't have to worry about not re-declaring the cursor when the statement is re-executed), but this won't be an option in the future -- I'll have to put the declare in the $sth->execute code because it needs to know the statement attributes before it can safely declare the cursor of the correct type.] Incidentally, Tim, are you sure that forcing us to use the ODBC SQL-prefixed names is Perl-ish? It feels horrid!
Hell yes! Why do you think I copied dbi-dev? And it is only an outline implementation, not "the implementation". This is only barely the start of something. It gives you some working code to look at, and starts to describe the issues to be resolved. And updatable scrollable cursors need to be (re)visited.I think this i/f needs more scrutiny/debate before rollout, due to all the add'l issues that may arise later.
> Even if it's 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.
I was asleep when the bulk API was being designed. I've no real idea whether there's anything much for DBD::Informix to do there; at the moment, it doesn't do it, so the default DBI implementation is used. There's a limit to how much the underlying ESQL/C provides, too, so it might well not be worth worrying about.
There needs to be discussion about what is involved in this. You raise good questions. I don't have answers for all of them.
I think a read-only scroll cursor implementation is useful. I don't have any experience with updatable scroll cursors; I know that they are highly non-trivial to implement within a database server, let alone on the client side faking it. My gut feel is that the default scroll cursor implementation should only provide non-updatable cursors; if the DBMS provides better support, it can be used by the driver, and it can claim the better support through the $dbh->get_info method.
One interesting bit is 'how can DBI intercept the relevant $dbh->get_info calls when the default implementation is in use and provide an accurate description of the capabilities'.
Me too, pro tempore. There's very little here that is not available for revision, and nothing that is not debatable. Let the debate continue...My 2 cents, I now cede the soapbox...
------- Original Message ----- From: "Jonathan Leffler" <[EMAIL PROTECTED]>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 ([EMAIL PROTECTED], [EMAIL PROTECTED]) #include <disclaimer.h>
Guardian of DBD::Informix 1.04.PC1 -- http://dbi.perl.org/
