Martin,
Unfortunately, I just ran into a problem. The problem comes into play when
trying to discerne at executed time whether no data (no result) columns
means we *should* make the statement inactive (i.e. finish it) or not.
$sth->prepare("insert into table values blah");
$sth->execute;
(should finish and/or mark inactive, but can't since we don't really know
what's coming, especially if this is a stored procedure) )
$sth->prepare("{? = call some_crazy_procedure()});
$sth->execute;
(should not call finish or mark inactive, since )
Now, if the procedure inserts data, the user will have to fetchrow_xxx to
clear out the result sets...I don't really like that scenario and would
rather silently "eat" the result sets with no columns, since the semantics
really change (calling a procedure that inserts and returns a value now
means forcing the fetchrow_xxx to clear out the "empty" result set).
I have to keep thinking about this. I think DBD::Sybase does it the way I
did, originally, as that was where the code was taken. One thought would be
to have the attribute odbc_more_results force the call to SQLMoreResults
instead of trying to get it after the fetch. That might solve the problem
and allow the end-user to call SQLMoreResults by themselves, but I'm not
sure how well it will do with everything else.
Regards,
Jeff
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
> Behalf Of [EMAIL PROTECTED]
> Sent: Monday, August 19, 2002 1:35 PM
> To: Jeff Urlwin
> Cc: [EMAIL PROTECTED]; Joe Tebelskis
> Subject: RE: Multiple Result sets in DBD::ODBC (and others?)
>
>
>
> 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
> >>
> >>
>
>
