Error: 255: Not in transaction Error

m39666 · #1 (**permalink**) 10-03-11, 17:51

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.

begooden-it · #2 (**permalink**) 10-04-11, 03:28

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

m39666 · #3 (**permalink**) 10-04-11, 11:38

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?

begooden-it · #4 (**permalink**) 10-04-11, 12:23

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

m39666 · #5 (**permalink**) 10-04-11, 14:22

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.

begooden-it · #6 (**permalink**) 10-04-11, 14:41

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?

m39666 · #7 (**permalink**) 10-04-11, 17:10

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.

begooden-it · #8 (**permalink**) 10-06-11, 05:03

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

begooden-it · #9 (**permalink**) 10-07-11, 05:52

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;

Internet Services

Web Development

Digital Marketing

Consumer Tech