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.
Regards,
Jeff
> -----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
>
>
