How to get previous executed sql in informix

Question

On my esql program when an sql fails and generates the exception I want to print the SQL that generated the exception. For that I need to find out how to get the previously executed SQL. I am running informix 11.5. I tried the following but nothing works

select * from sysmaster:sysconblock where cbl_sessionid in (select dbinfo('SessionId') from sysmaster:syssqlstat);

SELECT scs_sqlstatement FROM sysmaster:syssqlcurses WHERE scs_sessionid in (select dbinfo('SessionId') from sysmaster:syssqlstat);

All these get the sql of it self. For example if I run select * from sysmaster:sysconblock it show "select * from sysmaster:sysconblock" in the last executed. Is there any way to get this in informix? and is it [possible to do it on ESQL program?

Many Thanks

Answer 1

You're on the right track, but if you're using the same connection to run those SQL statements, then of course their successful execution obliterates the information from the previous statement. (In fact it's almost a perfect example of a heisenbug.)

What you need to do is create a second connection to the database, and use that to interrogate sysmaster content for the main connection that failed.

1. Connect to database for main program processing.
2. Identify SessionID and capture to a variable.
3. Connect to sysmaster database with a fresh connection.
4. Start processing on main connection.
5. When main connection processing fails with an error, use secondary connection with SessionID as parameter to obtain SQL etc.

Hope that's helpful.