PHP gets around this with odbc_close() which I believe $stmt->finish() could be
used for. The main problem would then be requiring scripts to call finish which
I guess would be undesirable.
I'm not sure but I'd guess the number of poeple returning multiple result-sets
from procedures is few (partly based on the fact it was not working properly).
It may be seen as a bit of a hack, but how about an attribute to say "we are
potentially fetching multiple result-sets" and in this case the statement is
marked inactive when [a] SQLMoreResults says there are no more result-sets or
[b] finish() is called (but this implies calling SQLFreeStmt(SQL_CLOSE)). If
this new attribute is not set (the default) it works as before. Anyone not
setting this new attribute but returning multiple result-sets and not consuming
them (no automatic call to SQLMoreResults now) will get an error with MS SQL
Server something like "busy with result-set on an another statement).
Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development
On 19-Aug-2002 Jeff Urlwin wrote:
> 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
>> >>
>> >>
>>
>>
