Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2011
    Posts
    13

    Unanswered: Error: 255: Not in transaction Error

    Hi All Informix Gurus.

    I have no informix dba knowledge and encountered error 255. I did a finderr 255 and it talked about inability to commit or rollback work. But there are related thread on this forrum and it talked about the login shell. So I am clueless as what the problem really is.

    the login shell for the account that i'm running the procedure is set to be "nologin" due to security requirement.

    the execution of the procedure is within a shell script

    Here is the content of the shell script call test.sh
    dbaccess somedatabase <<!
    execute procedure some_procedure();
    !

    When I run the test.sh, I got the following

    Database selected.

    (expression) (expression)

    255: Not in transaction.
    Error in line 1
    Near character position 31

    question:
    how do I fix the problem
    where would the code for some_procedure live if I were to modify the code?

    Appreciate your time.

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Hi,


    apparently, totally blind though, it seems you procedure is trying to commit work or rollback work although no begin work has been executed.

    to check your stored procedure code:
    dbschema -d your_database -f yourstoredprocedurename


    Your problem may be consecutive to a change in database logging mode from ANSI to "informix standard" ( buffered log or log).
    When in ANSI mode, a begin work is executed, although not explicitly written in the code, as soon as a commit or rollback is issued.

    When not in ANSI mode, you need to explicaitly write begin work at the beginning of your transaction.

    This might explain the difference... In any case, check you code.
    Hope this helps
    Eric

  3. #3
    Join Date
    Sep 2011
    Posts
    13
    Thanks for the command. I ran the command and saw "begin" statement within the function.

    So does this mean I need to change to standard informix rather than ANSI informix?

  4. #4
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    this is what I would recommand:
    1) check in which mode your database is: ie "logging", "buffered_logging" or ar "ansi"
    to do this, run dbaccess sysmaster, then run the following query:
    "select * from sysdatabases"
    the output is quite explicit.
    I call "logging" and buffered_logging" the standard modes of Informix, as opposed to ansi which is used in Or.... ( sorry I can't write this word ;-)

    So if you are in logging or buffered logging, you need to explicly write the BEGIN WORK statement. This is the default and usual mode for Informix.
    If you are in ANSI mode, the BEGIN WORK is never used ( as far as I know...), meaning that as soon as you terminate a transaction by COMMIT or ROLLBACK, a new transactions is automatically started. So in this mode you are always in a transaction.

    2) If in logging or buffered logging ( functionally the same ), check the logic of you procedure code ( commit executed though begin has not been issued for instance.. ( IF, WHILE etc...). You can also trace your procedure to a file. ( set debug file to "file name" ; trace on/off;

    Hope this helps...
    Eric

  5. #5
    Join Date
    Sep 2011
    Posts
    13
    the database is running with logging on. at least the output of query to the sysdatabases table show is_logging is set to 1 and the others are set to 0.

    and my function has the "begin work" statement.

  6. #6
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    check the logics of the stored procedure, search for the execution of a commit work in a hiiden place.
    also check about eventual invocation of triggers that would fire other stored procedures.
    can you debug the application, use breakpoints or so?

  7. #7
    Join Date
    Sep 2011
    Posts
    13
    It has to be some kind of environmental settings.
    The same function works on an old server.

    The old server is being replaced w/ a new one. The new server is running on informix version 11 and the old one is on version 9.

  8. #8
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Hi,

    at this point, this needs to be troubleshooted. There is not magic, and nor env setting or so that would influence on the transaction behaviour, except checking your database is logged or not, what you have done.

    You may hit a permissivity issue in 9,or why not a bug, that may have been fixed in the meantime.

    I have seen no bug related to this error, but didnt check the entire history though.

    If you mind posting your db schema including the incriminated stored procedure, I can try reproducing the issue.

    PM me if you prefer

    Eric

  9. #9
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    After analysis of the function code, there is an ON EXCEPTION, just after
    the variables DEFINE section, that states to ROLLBACK and return error codes.

    There is a FOREACH... SELECT statement executed before the first BEGIN WORK statement. If an error is detected, the first exception is fired, thus executing a ROLLBACK although BEGIN WORK has not yet been issued.

    this give an -255 "Not in transaction" error and this is the expected behaviour.

    Errors on the FOREACH...SELECT statement can be lock detection for instance.

    Code:
    CREATE FUNCTION my_function()
    RETURNING integer, char(50);
    DEFINE sql_err, isam_err integer;
    DEFINE sql_err_txt char(50);
    DEFINE var1, var2,var3 integer;
    DEFINE var4 char(25);
    DEFINE var5 char(7);
    
    ON EXCEPTION SET sql_err, isam_err, sql_err_txt
    ROLLBACK WORK;
    RETURN sql_err, sql_err_txt;
    END EXCEPTION
    
    --SET DEBUG FILE TO '/home/me/my_function.dbg';
    --TRACE ON;
    
    LET sql_err = 0;
    FOREACH WITH HOLD SELECT columns list
    INTO variables list
    FROM table1, table2, table3
    WHERE tables joins + Where clause
    
    ON EXCEPTION SET sql_err, isam_err, sql_err_txt
    ROLLBACK WORK;
    END EXCEPTION WITH RESUME
    
    BEGIN WORK;
    UPDATE table1 SET the_date = TODAY, column = '1'
    WHERE where clause
    
    UPDATE table2 SET the_date = TODAY, column = value
    WHERE where clause
    
    UPDATE table3 SET the_date = TODAY, column = value
    WHERE where clause
    
    
    IF (DBINFO('sqlca.sqlerrd2') > 0) THEN
    More updates
    END IF;
    COMMIT WORK;
    LET sql_err = sql_err + 1;
    END FOREACH;
    
    RETURN sql_err, 'Completed Successfully.';
    END FUNCTION;
    Last edited by begooden-it; 10-07-11 at 07:00. Reason: code missing

Posting Permissions

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