Dear All,

I have IDS 9.3 on sun solaris 8. My database is created with buffered logging.
I execute the below steps in dbaccess

begin work;

execute procedure 1;
execute procedure 2;

commit work;

If there is an error in procedure 2 it should ever rollback the procedure 1 statement.

Another example -

begin work;
insert into emp (emp_id, name) values (1, "jack);
insert into emp (emp_id, name) values (2, "tom);
insert into employee (emp_id, name) values (3, "tom);
commit work;

I have wrongly written entered the table name in the third sql statement.
The above query inserts 2 records in emp table and gives an error for table not found in the third statement. I would want the statement to behave in such a way that if there is an error in the third statement, it should not insert the data in the frist two statements. I have also set the env variable DBACCNOIGN to 1, but still no luck. It still inserts data through the first 2 statements and gives an error. Any workarounds. Appreciate ur feedback.

Regards,

lloyd

this is what i use in my procedures

on exception in (-206)

-- 206 : specified table not in the database. Will get same error if a non-existing table name is mentioned in select statement.
--- MAKE SURE THAT TABLE NAMES ARE CORRECT IN SELECT/UPDATE/DELETE STATEMENTS.

--- do nothing
trace "Message: " || l_sql_err || " " || l_isam_err || l_err_txt;

let l_sql_err = 0;

let l_isam_err = 0;
let l_err_txt = "";

end exception with resume;


put this in the beginig of you procedure after the defines

Cheers

Artem

Hi artemka,
Thanks for your feedback, i want my procedures to behave in such a way
if the second procedure fails the first one should also rollback. Whereas in my current transactions its not happening. Even if the second procedure fails the first procedures commits the data into the database. Any help.

Regards,

lloyd

The way we get around this problem, is call the procedures from the java app
and do the begin, commit, and rollback from the java app.


but looking at the example that you provided if you put the logic in that i posted in the previous reply you should be able to trap the error and do a commit or a rollback depending on the sqlcode from the statement.

Hi Artemka,

Thanks for your feedback, i want it do it from backend only, through dbaccess. I have written procedures which would copy data from one database to another. There are 10 procedures written for each table.
The first procedures copies data in to the master table, and the rest to the respective child tables. I want my procedures to work in such a way that if there is a failure in any of the child tables, the master table should also rollback the data. The same is not happening. I have used transactions but still it fails. Any workarounds?

Regards,

lloyd

Given your examples, you are not going to get what you want unless you check for errors and force the rollback. For example, an error in the second stored procedure is not going to force a transaction rollback unless you trap the error within the stored procedure and specifially invoke the rollback.

Similar in the second example. An improperly named table in the third select does not generate an error significant enough to force the rollback. Enclose the logic with a stored procedure in which you have set error control and you should be able to force the rollback yourself.