Commit trouble

[Shawn]

Hi. I'm calling a procedure that inserts a new record in my oracle DB. I'm
using an OleDbTransaction object so that if anything goes wrong the
transaction is not commited and the new record is not inserted. The problem
is that the transaction is carried out regardless of the commit method is
called or not. This code for instance even has a RollBack, but it still
inserts the new record. Can somebody please tell me what I'm doing wrong?

Dim oleDbConnection As OleDbConnection

Dim strOleDbConnectionString As String = _
"Provider=OraOLEDB.Oracle;" & _
"Data Source=xxx;" & _
"User Id=xxx;" & _
"Password=xxxx"

oleDbConnection = New OleDbConnection(strOleDbConnectionString)
oleDbConnection.Open()

Dim oleDbCommand As OleDbCommand
oleDbCommand = New OleDbCommand()
oleDbCommand.Connection = oleDbConnection
OleDbTransaction = oleDbConnection.BeginTransaction
oleDbCommand.CommandType = CommandType.StoredProcedure
oleDbCommand.CommandText = strOleDbCommandText
oleDbCommand.Transaction = OleDbTransaction

oleDbCommand.Parameters.Clear()

'Creating parameters.
oleDbCommand.Parameters.Add(New OleDbParameter("ConceptName",
OleDbType.VarChar)).Direction = ParameterDirection.Input
oleDbCommand.Parameters.Add(New OleDbParameter("FirstTrans",
OleDbType.Integer)).Direction = ParameterDirection.Input
oleDbCommand.Parameters.Add(New OleDbParameter("LastTrans",
OleDbType.Integer, 15, ParameterDirection.InputOutput, False, CType(15,
Byte), CType(32, Byte), "LastTrans", DataRowVersion.Current, Nothing))

'Assigning value to the parameters.
oleDbCommand.Parameters("ConceptName").Value = "test"
oleDbCommand.Parameters("FirstTrans").Value = 1009209
oleDbCommand.Parameters("LastTrans").Value = 1009578

oleDbCommand.ExecuteNonQuery()

oleDbCommand.Transaction.Rollback()


Thanks,
Shawn

 

[David Browne]

Hi. I'm calling a procedure that inserts a new record in my oracle DB. I'm
using an OleDbTransaction object so that if anything goes wrong the
transaction is not commited and the new record is not inserted. The problem
is that the transaction is carried out regardless of the commit method is
called or not. This code for instance even has a RollBack, but it still
inserts the new record. Can somebody please tell me what I'm doing wrong?

Does the command call a stored procedure?

If so is there a commit inside the procedure, or is there any block declared
as an autonomous transaction?

David

 

[Shawn]

Hi David, and thanks for trying to help me here.
The OleDbCommand object calls a procedure, yes. The procedure-name is in
strOleDbcommandText (oleDbCommand.CommandText = strOleDbCommandText). As
far as I know there is no commit inside the procedure. I didn't write the
procedure, but I have looked through it and I didn't see any commit
statement. Unfortunately I can't double check it for another 10 hours... I
will though, as soon as I get back to work
Your last question I didn't understand. What do you mean by "any block
declared as an autonomous transaction"? Could you give me an example?

Thanks!!
Shawn.

 

[David Browne]

Hi David, and thanks for trying to help me here.
The OleDbCommand object calls a procedure, yes. The procedure-name is in
strOleDbcommandText (oleDbCommand.CommandText = strOleDbCommandText). As
far as I know there is no commit inside the procedure. I didn't write the
procedure, but I have looked through it and I didn't see any commit
statement. Unfortunately I can't double check it for another 10 hours... I
will though, as soon as I get back to work
Your last question I didn't understand. What do you mean by "any block
declared as an autonomous transaction"? Could you give me an example?

Oracle has autonomous transactions, which means that you can declare that a
block of PL\SQL should run in its own isolated transaction which is commited
independently of the current transaction.

This is highly usefull is certian situations, but could be the cause of the
behavior you have seen.

For example this procedure:

create or replace procedure insert_log_row(in_log_entry varchar2(255)
PRAGMA AUTONOMOUS_TRANSACTION
as
begin
insert into log_table(id,message) values
(log_table_seq.nextval,in_log_entry);
end;



when run from this block

begin
insert_log_row('Rolling back transaction');
rollback;
end;

the row would still be inserted into the log_table.


David

 

[Shawn]

David,
There was a commit inside the procedure. I removed it and now it works
great.
Thanks!
Shawn



message

Hi David, and thanks for trying to help me here.
The OleDbCommand object calls a procedure, yes. The procedure-name is in
strOleDbcommandText (oleDbCommand.CommandText = strOleDbCommandText). As
far as I know there is no commit inside the procedure. I didn't write the
procedure, but I have looked through it and I didn't see any commit
statement. Unfortunately I can't double check it for another 10 hours... I
will though, as soon as I get back to work
Your last question I didn't understand. What do you mean by "any block
declared as an autonomous transaction"? Could you give me an example?

Oracle has autonomous transactions, which means that you can declare that a
block of PL\SQL should run in its own isolated transaction which is commited
independently of the current transaction.

This is highly usefull is certian situations, but could be the cause of the
behavior you have seen.

For example this procedure:

create or replace procedure insert_log_row(in_log_entry varchar2(255)
PRAGMA AUTONOMOUS_TRANSACTION
as
begin
insert into log_table(id,message) values
(log_table_seq.nextval,in_log_entry);
end;



when run from this block

begin
insert_log_row('Rolling back transaction');
rollback;
end;

the row would still be inserted into the log_table.


David