Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Location
    India
    Posts
    262

    Unanswered: Problems with transactions

    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

  2. #2
    Join Date
    May 2004
    Location
    New York
    Posts
    248
    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

  3. #3
    Join Date
    Aug 2003
    Location
    India
    Posts
    262
    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

  4. #4
    Join Date
    May 2004
    Location
    New York
    Posts
    248
    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.
    Last edited by artemka; 10-26-04 at 18:35.

  5. #5
    Join Date
    Aug 2003
    Location
    India
    Posts
    262
    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

  6. #6
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    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.
    Fred Prose

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •