Great stuff guys I cant wait to have a look at it in detail next week.
cheers
John Scoles
----- Original Message -----
From: "Martin J. Evans" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Thursday, September 07, 2006 8:28 AM
Subject: RE: execute_array enhancement to DBI and request for help for
someone who knows DBD::Oracle or XS
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
