IDS Forum

Re: RE: Strange warning message on Commit work in dbaccess (and isql)

Posted By: HUBERT BELLINGS
Date: Wednesday, 19 November 2003, at 7:01 a.m.

In Response To: RE: Strange warning message on Commit work in dbaccess (and isql) (John Carlson )

Hi

This message is present in 7.31UD5 NOT BEFORE and in 9.30 and 9.40 NOT in 9.20 or 9.21

In fact, when you use dbaccess in interactief mode is only a info, but
if you use dbaccess in a script then you can have some surprise.

This message mean that you have a open transaction and by default it commit in place do to the rollback

For example try this

Version IDS 9.40.FC1 and IDS.9.40.FC2 on Solaris 8
>
> Hereby the code of a stored procedure
>
> drop procedure testproc;
> create procedure testproc()
> on exception
> rollback work;
> end exception;
>
> set debug file to "/tmp/test.log";
> trace on ;
>
> begin work;
> select * from mon_tables
into temp t1 ; --
> mon_tables : some table that is large enough to be able to press
> "Control+C"
> while reading it.
> drop table test;
> commit work;
>
> end procedure;
> If we run this procedure and interrupt with "Control+C" during the select
> of
> the table the procedure doesn't fall into the "on exception"-routine.
> Result : Warning given : Warning:Data Commit is a result of an unhandled
> exception in TXN PROC/FUNC/TRI an implicit commit is given while he
should
> do a rollback.
>
> Screenshot
>
nformix@solmgt:/export/home/informix> dbaccess -e monitor < test.sql
>
> Database selected.
>
> ^Cexecute procedure testproc();
> 213: Statement interrupted by user.
> Error in line 1
> Near character position 27
>
> Warning:Data Commit is a result of an unhandled
exception in
> TXN PROC/FUNC/TRI
>
> Data committed.
>
> Database closed.
>
> informix@solmgt:/export/home/informix>
>
> Debug info
>
> trace on
>
>
> begin work;
>
> select *
> from mon_tables
> into temp t1;
> exception : looking for handler
> SQL error = -213 ISAM error = 0 error string = =
""
> exception : no appropriate handler
>
> When eg. You run the procedure twice and let the select finfish the "drop
> test table" errors : the on exception works ok.
>
> Debug info :
>
> trace on
>
>
> begin work;
>
> drop table test;
> exception : looking for handler
> SQL error = -206 ISAM error = -111 error string =
= "test"
> exception : handler FOUND
>
> rollback work;
> procedure testproc returned no data


>
So we have a case open on this problem but we receive a first answer and i ask more information but an this state of discusion support said it's normal and i said a database must never commit by default.
Other problem, you can not trap the error code -213 because you recive always
exception : no appropriate handler

I wait more info from Support.

> Best regards
>

Messages In This Thread

• RE: Strange warning message on Commit work in dbaccess (and isql)
  John Carlson -- Tuesday, 18 November 2003, at 10:22 a.m.
  • Re: RE: Strange warning message on Commit work in dbaccess (and isql)
    HUBERT BELLINGS -- Wednesday, 19 November 2003, at 7:01 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.