Affected Records for UPDATE, INSERT, DELETE for Oracle stored procs

I'm trying to figure out a way to determine the number of
records that were affected by calling Oracle stored procs
that UPDATE, INSERT or DELETE.

The problem is that I am working within an existing
framework that dynamically creates UPDATE, INSERT and
DELETE commands for Oracle data adapters based on all the
tables in the database. However, I can't call
OracleCommand.ExecuteNonQuery (which returns the number of
records affected) to execute the stored procs because of
the way the code is structured.

Is it possible to get the number of affected records for
UPDATEs, INSERTs and DELETEs without calling
ExecuteNonQuery? If so, what should I call? If not, we're
going to have a fair bit of rewriting to do.

Thanks in advance,

Mike Lastort
lastort@speakeasy.net

Dino
Tue Jul 29 15:11:11 CDT 2003

Maybe you could append
Select %ROWCOUNT

to the generated queries?


"Mike Lastort" <lastort@speakeasy.net> wrote in message
news:011f01c35607$9a68dff0$a601280a@phx.gbl...
> I'm trying to figure out a way to determine the number of
> records that were affected by calling Oracle stored procs
> that UPDATE, INSERT or DELETE.
>
> The problem is that I am working within an existing
> framework that dynamically creates UPDATE, INSERT and
> DELETE commands for Oracle data adapters based on all the
> tables in the database. However, I can't call
> OracleCommand.ExecuteNonQuery (which returns the number of
> records affected) to execute the stored procs because of
> the way the code is structured.
>
> Is it possible to get the number of affected records for
> UPDATEs, INSERTs and DELETEs without calling
> ExecuteNonQuery? If so, what should I call? If not, we're
> going to have a fair bit of rewriting to do.
>
> Thanks in advance,
>
> Mike Lastort
> lastort@speakeasy.net

Mike
Wed Jul 30 09:59:44 CDT 2003

Thanks. I think that's leading us in the right direction.
We're going to try to use %ROWCOUNT in conjunction with an
update cursor which is created based on a "SELECT FOR
UPDATE."

Hopefully that will do what we want.

We're going to use the rowcount as the output parameter
from the stored procs, returning 1 if one record is
updated, deleted or inserted, and 0 for anything else, and
hope that the DataAdapter.Update() method will give us
what we want.



>-----Original Message-----
>Maybe you could append
> Select %ROWCOUNT
>
>to the generated queries?
>
>
>"Mike Lastort" <lastort@speakeasy.net> wrote in message
>news:011f01c35607$9a68dff0$a601280a@phx.gbl...
>> I'm trying to figure out a way to determine the number
of
>> records that were affected by calling Oracle stored
procs
>> that UPDATE, INSERT or DELETE.
>>
>> The problem is that I am working within an existing
>> framework that dynamically creates UPDATE, INSERT and
>> DELETE commands for Oracle data adapters based on all
the
>> tables in the database. However, I can't call
>> OracleCommand.ExecuteNonQuery (which returns the number
of
>> records affected) to execute the stored procs because of
>> the way the code is structured.
>>
>> Is it possible to get the number of affected records for
>> UPDATEs, INSERTs and DELETEs without calling
>> ExecuteNonQuery? If so, what should I call? If not,
we're
>> going to have a fair bit of rewriting to do.
>>
>> Thanks in advance,
>>
>> Mike Lastort
>> lastort@speakeasy.net
>
>
>.
>

Bill
Mon Aug 04 14:46:15 CDT 2003

Thanks Folks. Mike and I are working together. The
problem is not really how to find out if a row is changed
in Oracle. More specifically, it is:

How to let the ADO.Net DataAdapter.Update() method know
that the wrong number of rows changed?

We are using the DataAdapter's Update() method to write
changes to the database. The documentation on
DataAdapter's InsertCommand (and UpdateCommand and
DeleteCommand) claims that one can use a stored procedure
call or a SQL statement to run during Update(). We would
like to do so. We have plugged in the stored proc and
it's working fine.

However, in the case where there is a concurrent change
(i.e., someone else modified the row since you read it),
we want to signal the DataAdapter that it should raise a
DbConcurrencyException. How can we?

In the case of embedded SQL, presumably the DataAdapter
uses the RowsAffected property to decide (expecting one
row to change). Is something similar possible with stored
procs?

Can someone with access to the ADO.Net code look this up
for us? Is it documented somewhere I haven't looked?

Thanks Muchly,
Bill



>-----Original Message-----
>Thanks. I think that's leading us in the right direction.
>We're going to try to use %ROWCOUNT in conjunction with
an
>update cursor which is created based on a "SELECT FOR
>UPDATE."
>
>Hopefully that will do what we want.
>
>We're going to use the rowcount as the output parameter
>from the stored procs, returning 1 if one record is
>updated, deleted or inserted, and 0 for anything else,
and
>hope that the DataAdapter.Update() method will give us
>what we want.
>
>
>
>>-----Original Message-----
>>Maybe you could append
>> Select %ROWCOUNT
>>
>>to the generated queries?
>>
>>
>>"Mike Lastort" <lastort@speakeasy.net> wrote in message
>>news:011f01c35607$9a68dff0$a601280a@phx.gbl...
>>> I'm trying to figure out a way to determine the number
>of
>>> records that were affected by calling Oracle stored
>procs
>>> that UPDATE, INSERT or DELETE.
>>>
>>> The problem is that I am working within an existing
>>> framework that dynamically creates UPDATE, INSERT and
>>> DELETE commands for Oracle data adapters based on all
>the
>>> tables in the database. However, I can't call
>>> OracleCommand.ExecuteNonQuery (which returns the
number
>of
>>> records affected) to execute the stored procs because
of
>>> the way the code is structured.
>>>
>>> Is it possible to get the number of affected records
for
>>> UPDATEs, INSERTs and DELETEs without calling
>>> ExecuteNonQuery? If so, what should I call? If not,
>we're
>>> going to have a fair bit of rewriting to do.
>>>
>>> Thanks in advance,
>>>
>>> Mike Lastort
>>> lastort@speakeasy.net
>>
>>
>>.
>>
>.
>