SQL programming / SQL basics |
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.
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 |