On Sat, May 08, 2004 at 12:06:34PM -0700, Jonathan Leffler wrote: > Tim Bunce wrote: > > >On Tue, May 04, 2004 at 10:03:05AM +0100, Tim Bunce wrote: > > > >>Here's a further update of what I'm thinking of with respect > >>to $sth->more_results (many thanks for the valuable feedback). > >> > >>This is quite long because I've tried to consider all the issues. > > > >Anyone else got any comments? > > Dear Tim, > > Yes. Sorry, I've been rather busy, and I've only just managed to make > time to look at it.
No problem. Thanks for looking. > DBD::Informix already supports batches of statements - under the terms > imposed by the Informix servers. If you prepare a statement (string) > that happens to contain several SQL statements and the server will > accept it, so will DBD::Informix. If the server won't support it, you > get back whatever error message is appropriate, according to the server. > > [There's a counter-proposal at the bottom, after an explanation.] > > <WARNING MODE="wet blanket"> > > This proposal strikes me as unnecessary meddling in the affairs of the > DBMS. It is also stuff that I won't be implementing in DBD::Informix. > That is pretty much 100% non-negotiable. > > Why? > > Because it is messing with what is provided natively. > > Informix permits you to use batches of statements. You separate them > with semi-colons - outside of comments and strings, of course. You > can have an arbitrary number of placeholders for input values. > > So far, so good. > > You can't use multiple select statements, nor can you mix select and > non-select statements. So a batch can only contain non-select statements. Okay. Can the client get the row count (or error) from each individual statement? > There is no way in Informix to separate the > execution of the statements as mandated by the more_results structure, > not using the server provided facilities. Either the batch is > executed or it is not. If you want separate statements to execute one > at a time, you don't use batches - you use separate statements > executed one at a time. The "more_results structure" does _not_ mandate separate statements to execute one at a time. Here's a quote: : Whether the entire batch of statements is executed before any results : are available, or whether calling more_results() actually triggers : the execution of the next sub-statement in the batch, is undefined. > Pre-parsing the SQL to determine whether a batch is a batch, and if > so, whether it needs to be processed by DBD::Informix or whether it > can simply be submitted to the server is nonsense. I agree, and the spec don't require it. > Oh, there's another catch -- in Informix, CREATE PROCEDURE statements > (and variants upon them) contain embedded semi-colons. This is a > complete PITA for any client-side code scrutinizing SQL and trying to > determine statement boundaries. I guess you've missed the previous emails where I confirmed that client-side code scrutinizing of SQL is not expected or required. > Believe me, I've done a lot of it, > and it is *hard* to get it right - very hard! So hard that even my > premier effort at doing it has a bug in it because I found out about > C-style comments in Informix SQL only recently (they were/are not > documented). If you can be bothered to be interested, I'll bore you > to death with all the Informix-specific details (issues such as when a > comment start character is not the start of a comment) - I won't blame > you if you cannot be bothered with all those details. I recall some from previous discussions in previous years :) > </WARNING> > > There's nothing to stop DBI implementing it on top of the current > facilities - as long as you get all the SQL parsing correct for > Informix databases (as well as any others you are working with). > > <COUNTER-PROPOSAL> > > If you wish to define a driver-specific function (also accessible via > database handles) that will accept a string and return an array of > separate SQL statements, then I will be happy to provide such a > function as part of DBD::Informix. DBI can probably provide a decent > common surrogate or default implementation for the majority of > database servers. > > @sql = $dbh->split_sql($string); > > This would help DBI build an accurate working implementation of the > functionality outlined in the discussion. I would be willing to > provide that. DBD::Informix already includes the underlying SQL > tokenizer that deals with this nonsense (so that it can determine the > number of placeholders accurately), so extending it to do the > splitting like that is relatively straight-forward. This is basically > a driver-specific implementation of the line in your proposal: > > my @statements = split /;/, $statement; # (obviously dumb example) > > </COUNTER-PROPOSAL> I guess you missed the emails where I refered to the preparse() method. My *longer term* plan is that preparse() will enable the DBI to offer working more_results functionality for drivers that don't implement it themselves. I don't expect drivers for databases which don't have a general batch concept to implement more_results et al. It's certainly not mandatory in any way. All I'm trying to do is to define a standard API for those drivers that can and do so that code can be more easily ported between them. I hope that clears up your concerns. Tim.
