I agree. Tim.
On Mon, Aug 19, 2002 at 06:34:32PM +0100, [EMAIL PROTECTED] wrote: > > On 19-Aug-2002 Jeff Urlwin wrote: > > Martin, > > > > I think you and I are basically saying the same thing (semantic differences, > > I think): > > 1) the user should be responisble to check for more results > > > > 2) DBD::ODBC shouldn't "silently" eat up result sets. > > > > 3) even no-column result sets should be returned to the user, to at > least > > allow the user to obtain $sth->rows (if the database supports providing the > > correct value). DBD::ODBC has to do the right thing in these cases (i.e. > > when fetch is called, don't error, just check for more results and return > > undef, if SQLNumResultCols == 0 and call finish() if no more results are > > ready). That will allow the end-user to gracefully check themselves and get > > values from the multiple result sets. > > Yes, total agreement. Anything else is going to be guess work on the part of > DBD::ODBC and I don't think it is possible to make DBD::ODBC guess correctly > every time. It would be interesting to see how other DBDs with this issue cope > with it - assuming any have to. > > Martin > > >> -----Original Message----- > >> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > >> Behalf Of [EMAIL PROTECTED] > >> Sent: Monday, August 19, 2002 10:43 AM > >> To: Jeff Urlwin > >> Cc: Joe Tebelskis > >> Subject: RE: CPAN Upload: J/JU/JURL/DBD-ODBC-0.45_14.tar.gz > >> > >> > >> I will think about this more but my immediate thought is that the > >> problem is > >> the lack of SQLMoreResults in DBI/DBD::ODBC. If the programmer could call > >> SQLMoreResults he would have total control of getting the results > >> from a stored > >> procedure. more_results would just need to work like execute as > >> far as finding > >> out the number of columns, describing columns and getting > >> SQLRowCount but an > >> undef back from it would indicate there are not more results. > >> > >> The problem with the current implementation is that there is nothing to > >> distinguish the multiple result-sets, it just so happens the > >> current procs you > >> are working with are an update/insert followed by a select - they > >> could just > >> as easily be two selects. e.g. > >> > >> drop procedure mje1 > >> create procedure mje1 as begin select * from bench_char where f1 > >> < 2; select f1 > >> from bench_char where f1 < 2; end > >> > >> my $stmt = $dbh->prepare("{call mje1}"); > >> $stmt->execute(); > >> my @row; > >> my $c = 0; > >> while(@row = $stmt->fetchrow_array()) { > >> print "row $c: ", join(",", @row), "\n"; > >> } > >> $dbh->disconnect(); > >> > >> does not seem to work as I would have expected. SQLMoreResults is called, > >> SQLNumResultCols is called and returns 1, the columns are > >> described and rebound > >> but not fetched. Also, disconnect returns: > >> > >> DBI::db=HASH(0x8209038)->disconnect invalidates 1 active statement handle > >> (either destroy statement handles or call finish on them before > >> disconnecting) > >> at ./x.pl line 14. > >> > >> The only way I can see to do the right thing EVERY time is for > >> the user to have > >> more_results(). Your example [2] creates more_results and so I > >> think it would > >> work. The only problem I can see with this is what if they don't use > >> more_results - they will eventually get the "busy with another result-set" > >> error. However, since I don't think this worked properly before > >> if perhaps does > >> not matter. > >> > >> On 19-Aug-2002 Jeff Urlwin wrote: > >> > Martin, > >> > > >> > I can't understand (without the dbi trace) why, when > >> SQLNumResultCols == 0, > >> > that we're not calling SQLMoreResults, but that may become a moot point. > >> > I'm actually re-thinking everything a bit here. There are two > >> issues that > >> > need resolution, the first of which, I think I've solved with my new > >> > attribute. > >> > > >> > 1) There was definitely a problem with multiple result sets and > >> re-executing > >> > a query with no further prepare. Consider this: > >> > insert into somedifferent table values > >> > select int1, int2 from sometable; > >> > select char1, char2, char3, long4 from someothertable; > >> > > >> > Run that twice with no prepare() and you'll have a problem, especially > >> > without the "new" attribute odbc_force_rebind, which will > >> re-bind the result > >> > set after each execute. I don't want to rebind the result set > >> every time > >> > because the performance hit over multiple inserts/updates when > >> "mutating" > >> > result sets are not involved would cost something. When I mean > >> "mutating" > >> > result sets, I mean a stored proc that has multiple result sets *or* a > >> > stored proc that could return different result sets based upon > >> the input. > >> > >> I think this is a really difficult area for DBD::ODBC to deal > >> with unless the > >> perl script writer helps out. The overhead of rebinds when they are not > >> necessary can be quite large, especially with something like OOB > >> as each bind > >> requires a network trip. The person writing the script should > >> know they are > >> doing something like this and take action but for everyone else > >> the default > >> action should work as before - i.e. assuming single result-sets. > >> > >> > 2) I'm re-thinking the need to call SQLMoreResults in > >> dbd_st_execute. Why? > >> > We end up hiding result set(s) when SQLNumResultCols == 0. It > >> may not be > >> > important that we skip it, but it seems to be a part of the > >> current problem. > >> > What if someone wanted to know the number of rows affected in the > >> > insert/update? I think the cruxt of the problem is that when the > >> > SQLNumResultCols == 0, we got out of sync with the result > >> set(s). I *think* > >> > that I'd like to see the following work, no matter what: > >> > > >> > $sth = $dbh->prepare(); > >> > $sth->{odbc_force_rebind} = 1; > >> > while (@params = get_params) { > >> > $sth->execute(@params); > >> > do { > >> > my $rowcount = $sth->rows; # if it's > >> insert/update, then we can > >> get the > >> > rows affected? > >> > while (@data = $sth->fetchrow_array) { > >> > get result sets here > >> > if (no_data in @data) { > >> > print $rowcount; > >> > } else { > >> > } > >> > } > >> > } while ($sth->{odbc_more_results}); > >> > } > >> > > >> > Right now, I think that since execute is *sometimes* calling > >> SQLMoreResults > >> > and sometimes not, it's causing unexpected behavior. > >> > > >> > Your thoughts on this are most welcome! > >> > >> Martin > >> -- > >> Martin J. Evans > >> Easysoft Ltd, UK > >> Development > >> > >> >
