If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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

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;

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?

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.

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.