How do you check the status in a procedure using a 4gl database?

Statement of Problem: I have an Informix SQL procedure running against an Informix-4gl database which is getting a deadlock error (-239). My java program which calls this procedure is not detecting the deadlock. Desired Outcome: To be able to detect the deadlock in the SQL procedure OR To be able to detect the deadlock in the java program that calls the procedure. Example: I have a procedure which executes multiple table updates on an Informix-4gl database. (Obviously there is another program somewhere executing these 2 updates in reverse but this is an enormous system and I cannot find the other program which is conflicting with this one.) ie: create procedure do_updates() set isolation to dirty read; set lock mode to wait; update table1 set column1 = 'AAA' where key_column = '111'; update table2 set column2 = 'BBB' where key_column2 = '222'; end procedure Is there a way within the stored procedure to check the status after each of the updates? In the Informix-4gl language I could check status, sqlca.sqlcode or sqlca.sqlerrd[2], but within the procedure it won't let me do that. Any clues? I am calling this from a java program - so if I could detect that there was a negative error returned from any of the updates in the java program that would work too, but I don't know how to do that either. ie: String s_Exec = "{ call do_updates() } "; boolean b_Success = false; try { PreparedStatement st = _jdbc_con.prepareStatement(s_Exec); st.executeUpdate(); b_Success = true; } catch (SQLException e) { System.out.println("ERROR: " + s_Exec + " ) failed: " + e.getErrorCode()); System.out.println(e); e.printStackTrace(); } The above catch does not catch the -239 deadlock error that is produced by the executeUpdate (I would have thought it would). Thanks! Karen

KLD wrote: > Statement of Problem: I have an Informix SQL procedure running against > an Informix-4gl database which is getting a deadlock error (-239). No such thing as a 4GL database. But still, see below: > My java program which calls this > procedure is not detecting the deadlock. > Desired Outcome: To be able to detect the deadlock in the SQL > procedure OR > To be able to detect the deadlock in > the java program that calls the procedure. > > Example: > I have a procedure which executes multiple table updates on an Informix-4gl > database. (Obviously there is another program somewhere executing these 2 > updates in reverse but this is an enormous system and I cannot find the > other program which is conflicting with this one.) <SNIP>> > Is there a way within the stored procedure to check the status after each of > the updates? In the Informix-4gl language I could check status, > sqlca.sqlcode or sqlca.sqlerrd[2], but within the procedure it won't let me > do that. Any clues? Add an EXCEPTION block to trap the error. In the exception block the sqlcode and sqlca fields are available. See the Guide to SQL Syntax for details. Art S. Kagel <SNIP>