Thanks to everyone for the help. I've been trying to do too many things at once and completely missed the execute_for_fetch in perl in DBD::Oracle - sorry for the noise and thanks xho for pointing that out.
The attached patch (diff with subversion this morning rev 6867):
1. adds support for array context aware execute_for_fetch returning
(executed_tuples, rows_affected)
This has two advantages:
a) you don't have to calculate the rows affected by traversing the
ArrayTupleStatus and suming the rows affected.
b) for DBD::Oracle you can't traverse the ArrayTupleStatus suming the rows
affected and get the right value because they are all -1 so only doing
2 (below) would have actually made it impossible to get the rows affected.
2. changes execute_array in scalar context to return execute_tuples as per DBI
docs instead of rows affected.
3. fixes some minor typos and spelling mistakes
4. In the Changes file and Oracle.pm I assumed the next release was 1.19.
I think it is worth a note in the pod that execute_array can not set the
individual results in the ArrayTupleStatus and so sets them all to -1 but I
could not see an obvious place to put it. BTW, I did check the OCI docs and I
too (like xho) could not find a way of obtaining the status per row. I also
checked our Oracle ODBC driver which uses OCI and arrays of parameters but ODBC
only requires the total affected in SQLRowCount.
I've tested this change with the stock 1.52 DBI and a one modified to support
array context execute_for_fetch.
BTW xho, google groups is as you've discovered only one way i.e. they see posts
to dbi-dev/dbi-users mailing list but we don't see posts to google. I
discovered this some time ago and really don't like it. For a while I was
answering posts on google with a sig pointing this out but it seemed to be a
losing battle.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
On 06-Sep-2006 Martin J. Evans wrote:
> For anyone in dbi-user list you may have been following my discussion on
> execute_array in DBI. Usually what I do in my code is:
>
> $affected = $sth->execute(for an insert statement);
> die "did not insert expected number of rows"
> if ($affected != $the_expected_number_of_inserts);
>
> which works because execute returns the rows affected.
>
> execute_array returns the tuples executed and the only way to get the rows
> affected is to pass an ArrayTupleStatus then cycle through the array adding
> up
> the rows affected for each row - this is pretty inconvenient.
>
> Tim suggested a change to allow execute_array to understand being called in
> array context so you could do:
>
> ($executed, $affected) = execute_array();
>
> This works great and I have a patch to DBI which will do this which I'll
> commit
> this evening (subject to access to svn).
>
> However, I'm using DBD::Oracle so this does not solve my problem because:
>
> 1. DBD::Oracle has its own execute_for_fetch which does not understand array
> context.
>
> 2. Oracle OCI cannot return the individual rows affected per tuple so when
> execute_for_fetch was implemented the ArrayTupleStatus was filled with -1's
> for
> unknown rows affected.
>
> This rendered execute_array with DBD::Oracle useless to me. However, looking
> at
> DBD::Oracle and OCI I've discovered OCI can return the total rows affected
> (which I want) and in fact due to a bug in DBD::Oracle execute_for_fetch
> currently returns the total rows affected instead of the number of rows
> affected.
>
> What I need assistance with is to change DBD::Oracle's execute_for_fetch to
> understand array context so it can return the tuples executed and the rows
> affected (which it already has). At the same time we could fix the bug I
> reported in DBD::Oracle so in scalar context it returns the tuples executed
> instead of rows affected.
>
> If someone can help or point me in the right direction I'd be most grateful
> since I'm not that familiar with XS.
>
> Thanks.
>
> Martin
> --
> Martin J. Evans
> Easysoft Ltd, UK
> http://www.easysoft.com
dbd_oracle_patch
Description: dbd_oracle_patch
