OCIRollback problem

[juicy]

I want to do rollback from insert data into master-details table when there
are duplicate ID key in by user. The algorithm is like this

1. insert data into master_table
2. loop
3. select count(*) from detail where ID = <ID key in by user>
4. if count(*) > 0{ $dup = true;}//end if
5. insert data into detail_table
6. End loop
7. If $dup = true{OCIRollbac k($conn);}
else{OCICommit( $conn);}

if there are two duplicate row inserted into detail_table, it should
rollback all transaction but I found that it won't work and still insert
on record to it.

Please advise on this problem, thanks.

[Mladen Gogala]

Re: OCIRollback problem

On Fri, 03 Mar 2006 23:14:26 -0500, juicy wrote:
[color=blue]
> I want to do rollback from insert data into master-details table when there
> are duplicate ID key in by user. The algorithm is like this
>
> 1. insert data into master_table
> 2. loop
> 3. select count(*) from detail where ID = <ID key in by user>
> 4. if count(*) > 0{ $dup = true;}//end if
> 5. insert data into detail_table
> 6. End loop
> 7. If $dup = true{OCIRollbac k($conn);}
> else{OCICommit( $conn);}
>
> if there are two duplicate row inserted into detail_table, it should
> rollback all transaction but I found that it won't work and still insert
> on record to it.
>
> Please advise on this problem, thanks.[/color]


You should have posted the original code snipped that fails,
not just the pseudocode. Nevertheless, my guess is that
your problem is the fact that oci_execute performs a commit if
the statement execution is successful. Here is what the good
book says:

"oci_execut e() executes a previously parsed statement (see oci_parse()).
The optional mode allows you to specify the execution mode (default is
OCI_COMMIT_ON_S UCCESS). If you don't want statements to be committed
automatically, you should specify OCI_DEFAULT as your mode."

In addition to that, I feel inclined to ask why are you de-duping data
like that and why don't you create unique constraint and let the database
winnow out duplicates? Oracle supports things like unique constraints, and
primary keys which will eliminate the duplicates without loops.

--

http://www.mgogala.com