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.
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. 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.
So far, so bad.
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.
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. 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.
</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>
--
Jonathan Leffler ([EMAIL PROTECTED], [EMAIL PROTECTED]) #include <disclaimer.h>
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
