SQL interruption

Interrupting long running SQL queries, or waiting queries because data is locked.

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

SQL interruption is not enabled by default. use the OPTIONS SQL INTERRUPT ON program option to turn in SQL interruption.

With OPTIONS SQL INTERRUPT ON, when the program gets an interruption event (a SIGINT signal from the system, or an interrupt event from the front-end), the running SQL statement is stopped and the INT_FLAG global variable is set to TRUE, and SQLCA.SQLCODE is set with error -213.

SQL interrupt must be used in conjunction with signal handling instructions DEFER INTERRUPT and DEFER QUIT, otherwise the program would stop immediately in case of interruption event.

SQL interruption results in abnormal SQL statement execution and therefore raises a runtime error. Therefore, the SQL statement that can be subject of interruption must be protected by a WHENEVER ERROR exception handler.

Pay attention to the fact that not all database servers support SQL interruption.

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 the database server type that is different from IBM® Informix®, the database drivers will return error -213 in case of interruption, to behave as in IBM Informix.

Not all database servers support SQL interruption.

Table 1. Database server support of SQL interruption
Database Server Type SQL Interruption API SQL error code for interrupted query
Genero db (Since version 3.80) SQLCancel() Native error -30005
IBM DB2® UDB (Since version 9.x) SQLCancel() Native error -952
IBM Informix sqlbreak() Native error -213
Microsoft™ SQL Server (Only 2005+ with SNC driver) SQLCancel() SQLSTATE HY008
MySQL No API to interrupt N/A
Oracle Database Server OCIBreak() Native error -1013
PostgreSQL PQCancel() SQLSTATE 57014
Sybase ASE ct_cancel() SQLSTATE HY008
SQLite sqlite3_interrupt() Native error SQLITE_ABORT