> Dean Arnold wrote:
> > Questions/issues:
> >
> > 1. Will this support updatable cursors? If so, how?
> > If not, can it be extended to support them?
>
> No. N/A. Maybe. Any ideas?
>
> 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.
I guess if ODBC is the LCD for most platforms, then followng
whatever model it provides should be sufficient...I must admit
I'm not a big fan of ODBC, and I hope Mssr. Bunce will *never*
let DBI get as bloated with committee think as ODBC!
>
> > 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.
>
> 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.
>
> 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?
Since a cursor is "positioned", strictly speaking, a fetch is not needed,
ie, just
move the cursor to some row and apply your update/delete...and in JDBC, they
add the "insert row" notion to allow inserts thru the cursor (a bit clumsy
in my opinion,
but consistent with the notion of positioning).
And any SQL which doesn't explicitly reference a cursor doesn't use the
cursor;
visibility to a cursor of the changes applied by other apps updating the
datasource
is platform and/or app specific, based on all the isolation issues you
mention below.
For that matter, updates made thru a cursor may not even be visible to that
cursor,
depending on how the dbms/driver/app behaves.
>
> 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.
Agreed...IMHO, cursors, while convenient, are a bad habit that can often
be avoided by properly crafted declarative SQL and a good optimizer
in the DBMS.
>
> > 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.)
>
> The 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...
>
> 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.
>
> > 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?
>
> I'm not sure I understand the preamble to the last question.
I was wondering if native SQL already provides most of the
cursor functionality, and DBI's current definition is sufficient
to provide most of the scaffolding...I guess the answer is
"No", since arbitrary positioning in a result set isn't available
in any SQL dialect I'm aware of. My concern is API
bloat: if SQL can do it within the current DBI, then defining
a new set of functions/attributes is probably not desirable
(tho there seems to be a significant audience for the counter
argument - "SQL bad, Perl good" -,
hence DBIx::Recordset and its kin). My POV derives from DBD::Teradata's
support for simple updatable cursors...but it doesn't support arbitrary
positioning, so I guess it doesn't really apply.
>
> 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!
>
> > I think this i/f needs more scrutiny/debate before rollout, due
> > to all the add'l issues that may arise later.
>
> 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.
>
> > 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'.
>
I'm a little confused by the semantics of get_info in this case;
if I ask get_info() if scrolled cursors are supported, and my DBMS doesn't
support them, does DBI say "unsupported", even tho its going to try to
provide an emulation, and so (as far as the app is concerned) it is
supported ?
In fact, is that even possible ? ie, if the target datasource can't do
arbitrary
positioning, can DBI actually safely create an emulation (e.g, the resultset
is very
large, and there's no explicit ordering of the resultset, assuming you can't
pull
the entire resultset into the client, how do you reposition to the first row
once
you've consumed 3 bufferful's ?) Or is there some error to be raised in the
case
when scrollbable cursors can't be safely emulated ?
from your POD:
"Previously fetched rows are cached (possibly in the server, possibly in
the client). When a row is to be fetched, if it is already in the
cache, it is returned from the cache. If it is not already in the
cache, then the row is fetched from the database (caching result rows as
necessary). If the row turns out not to exist, then 'undef' is
returned. Fetches before the first row return undef; fetches after the
last row return undef. The current position is then one space out of
range, and an appropriate relative fetch (or an absolute fetch) is
needed to collect values again."
Can DBI resurrect that first row after I've processed
10,000,000 rows, assuming my datasource doesn't have native
support for scrollable cursors ? Can you elaborate on how you
envision handling that case ? Do you expect some unique row ID,
or unique set of key fields, to be available to be able to do that ?
Maybe I'm reading too much into this API, and there won't be any emulation
provided, and execute_scrolled() will return undef if DBI doesn't get
a positive reading from get_info(SUPPORTS_SCROLLED) or somesuch ?
>
> > My 2 cents, I now cede the soapbox...
>
> 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...
>
>
All the issues you raise echo my concerns, hence my
initial comments and request for deeper examination/debate.
The bulk API work didn't raise much traffic from dbi-dev except
between Mssr. Bunce and myself; while that
makes development expedient, I certainly would've liked a few more comments
to make sure our assumptions were amenable to all those driver writers out
there.
So I think a little more traffic on the cursor issue will be helpful
Would it be possible for you or Mssr. Bunce to put together a summary of
assumptions about how this i/f is going to behave, esp wrt any emulation
DBI will provide for drivers that don't fully support scrollable cursors ?
("set $sth->err/errstr and return undef" is a perfectly acceptable answer to
me).
Regards,
Dean Arnold
Presicient Corp.
www.presicient.com
