On 17/06/11 13:19, Jonathan Leffler wrote:
On Fri, Jun 17, 2011 at 04:11, David Nicol<davidn...@cpan.org> wrote:
Thanks-- the proposed docpatch was a trial balloon to get this answer.
Here's copy for a revised proposed patch:
When you really don't know if the statement you have in a variable is going
to be a C<SELECT> or not, unrolling the process into
C<prepare>,C<execute>,
and some C<fetch> variant called within an C<eval> block will always work.
Doesn't $sth->{NUM_OF_FIELDS} tell you whether the statement is something
that returns values or not? It's zero for anything that isn't similar to a
SELECT, and positive for any statement like a SELECT (or, for at least some
DBMS, things like EXECUTE PROCEDURE where the procedure returns values)?
From DBI:
Number of fields (columns) in the data the prepared statement may return.
Statements that don't return rows of data, like DELETE and CREATE set
NUM_OF_FIELDS to 0 (though it may be undef in some drivers).
In DBD::ODBC that is certainly taken literally in that NUM_OF_FIELDS is
positive ONLY if there is a result-set from select although the result-set may
be empty. In the case of output bound parameters from procedures it is not
certain when these are available as they can only be retrieved by binding and
are not always available until the procedure completes i.e., SQLMoreResults
returns SQL_NO_MORE_DATA. I'm not sure how this works for other DBDs but in
ODBC you can retrieve column data with SQLGetData but you cannot retrieve
output parameters with SQLGetData so they are not really part of the
result-set. It avoids the further confusion of:
(pseudo code):
procedure test (a out integer) is
a = 2;
select acol from table1
insert into table2 values (1);
select acol.bcol from table2
In ODBC the output parameter must be specifically bound and in most drivers is
not available until the procedure has finished i.e., you have retrieved all the
rows from the last select or you closed the statement on the last select. So in
DBD::ODBC you'd have:
pseudo code again:
prepare(/{call test(?)}/)
bind_param_inout(1, \my $out)
execute;
do {
# first iteration NUM_OF_FIELDS will be 1 and $out will still be undef
# second iteration NUM_OF_FIELDS will be 0 and $out will still be undef
# third iteration NUM_OF_FIELDS will be 2 and $out will still be undef
while(fetch) # exhaust select
} while odbc_more_results;
$out will be 2 now
If NUM_OF_FIELDS indicated "whether the statement is something that returns values
or not" and those values are procedure output parameters then I'd expect you to be
able to use bind_col on them. If you cannot call bind_col for them then I guess they
don't count in NUM_OF_FIELDS.
The only thing I'm not sure about is whether $sth->{NUM_OF_FIELDS} is always
set by $dbh->prepare, or whether some DBMS can't set it until you do
$sth->execute.
Right now, I am unsure of that too.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
