Using SQL interruption

Interrupt long running SQL queries, or interrupt queries waiting for locked data.

If the database server supports SQL interruption, a program can interrupt a long running SQL statement.

SQL interruption is not enabled by default. Use the OPTIONS SQL INTERRUPT ON instruction to enable SQL interruption.

SQL interruption results in abnormal SQL statement execution and raises a runtime error. Therefore, the SQL statement that can be interrupted must be protected by a WHENEVER ERROR exception handler or TRY/CATCH block.

MAIN
  DEFINE n INTEGER
  DEFER INTERRUPT
  OPTIONS SQL INTERRUPT ON
  DATABASE test1
  WHENEVER ERROR CONTINUE
  -- Start long query (self join takes time)
  -- From now on, user can hit CTRL-C in TUI mode to stop the query 
  SELECT COUNT(*) INTO n FROM customers a, customers b 
       WHERE a.cust_id <> b.cust_id 
  IF SQLCA.SQLCODE == -213 THEN
     DISPLAY "Statement was interrupted by user..."
     EXIT PROGRAM 1
  END IF
  WHENEVER ERROR STOP
  ...
END MAIN

When SQL interruption is supported by a database server type other than IBM® Informix®, the database drivers will return error -213 in case of interruption, to behave as in IBM Informix.