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 >> >>
