Check if something got committed after an update in Informix 4GL program

Question

I have to check upon saving my 4GL program if changes were actually made in this update. Sometimes users will just update and save but no updates were actually made to the tables.

I would assume that throughout the update it must do a commit work to roll the changes into the table.

Is there any way for me to check at the end of the update if something actually got committed? Or? Any other suggestions?

I can't save previous_data and then compare with current_data since there are so many code in different places where updates can be done.

Thank you!

score 0 · Accepted Answer · answered Mar 15 '16 at 15:09

You can check the SQLCA record for the number of rows affected by the UPDATE immediately after it completes and before you do any other SQL operation (such as COMMIT). That may still count identity 'changes', but it the closest approximation to what you want that is available.

score 0 · Answer 2 · answered Mar 15 '16 at 22:49

My interpretation of the question is you have something like

INPUT ...
    ...
END INPUT 
IF int_flag THEN
    # Don't update database
ELSE
    # Update database
END IF

If the user accepts the dialog, the code to update the database is going to execute. If the user has not made any changes in the INPUT then this could be considered a waste of time.

I know with Genero, we have added syntax so that you can better detect that changes have occurred in the dialog and thus only update the database if a change has occurred. http://www.4js.com/online_documentation/fjs-fgl-manual-html/#c_fgl_prog_dialogs_touched_flag.html

If still on old 4gl, field_touched should be available to you. I know we can do

AFTER INPUT 
    IF int_flag THEN
        EXIT INPUT
    END IF
    IF field_touched(*) THEN
        # User has made a change in the dialog

and by using * test all fields in the dialog without explicitly having to list them, you may have to list the fields explicitly. So it might be simpler than you think to test after the dialog.

Similarly for complex data structures with records and arrays, in Genero we can do comparisons by parsing to JSON or XML, and then to string, so those techniques can also make the before/after comparison one liners.

Otherwise if you want to do the test after the database statement has executed, triggers might be an option for you. Create the triggers to insert a record into an audit table only if a genuine change has occurred in the UPDATE.

Check if something got committed after an update in Informix 4GL program

2 Answers2