Re: Is it ever legal (should be supported) to issue a non select in selectall_arrayref?

On 17/06/11 13:19, Jonathan Leffler wrote:
> On Fri, Jun 17, 2011 at 04:11, David Nicol<[email protected]>  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