SQL Interrupt

bhenk02@sun.cps.plnin.gmeds.com (Brian Henk) writes:


>Is there a way to gracefully interrupt an Ingres SQL statement during its
>execution?
>
>1. sprintf (sql_stmt, "some table join work", ...)
>
>2. EXEC SQL EXECUTE IMMEDIATE :sql_stmt;
>   
>
>For larger table joins I'd like to be able to interrupt the transaction 
>during step 2.
>
>Any ideas/thoughts would be helpful.

Somebody at Ingres Tech Support is going to hate me ...

The SQL Terminal Monitor does this just fine.  So why can't we do the
same in our embedded SQL?  Well... we can.  It's just a bit hairy.

I WILL NOT explain it fully, as I am not going to teach others to use
signals and setjmp(), sigsetjmp(), longjmp(), and siglongjmp().  It's too
messy, and there are too many nooks and crannies I don't have time to 
support.

However, there is an UNDOCUMENTED routine in the Ingres library called

     iiresync();

that "tells" the Ingres server that the transaction should be aborted.

I've used this in signal handlers before I've shut down.  I've not
used it to "interrupt and resume" processing (which is what all the ***jmp()
routines would be needed for).

IT'S COMPLETELY UNSUPPORTED!!!!!
IT'S COMPLETELY UNSUPPORTED!!!!!
IT'S COMPLETELY UNSUPPORTED!!!!!

-- 

|---------------------------------------------------|
| Michael Leo              York & Associates, Inc.  |
| mal@winternet.com        (612) 921-8083 (voice)   |
|                          Minneapolis, MN, USA     |
|---------------------------------------------------| 
| NAIUA = North American Ingres Users Association   |
|         http://www.naiua.org/ingres/              |
|                                                   |
|   Ingres FAQ is at ftp.naiua.org in /pub/ingres   |
|---------------------------------------------------|




bhenk02@sun.cps.plnin.gmeds.com (Brian Henk) writes:

> Is there a way to gracefully interrupt an Ingres SQL statement during its
> execution?
> 
> 1. sprintf (sql_stmt, "some table join work", ...)
> 
> 2. EXEC SQL EXECUTE IMMEDIATE :sql_stmt;
>    
> 
> For larger table joins I'd like to be able to interrupt the transaction 
> during step 2.
> 
> Any ideas/thoughts would be helpful.

There is a function called IIbreak(), that interrupts a running query.

The steps, you should probably do in your program, are these (I expect
you're programming in an Unix environment, otherwise you have translate
the description into the environment you are using):

1. Define a safe point for continuing after an interrupt with setjmp().

2. Create a signal handler for SIGALRM, SIGINT or whatever.  This signal
   handler should be called to abort the query, either by the user
   pressing CTRL-C or after a specified time has elapsed (using alarm).
   
   The signal handler should call IIbreak() (The prototype probably is:
   "void IIbreak (void)"), perhaps do a "ROLLBACK" and resume execution
   at the point defined in step 1 by calling longjmp().

3. Establish the signal handler with signal() or sigaction().

4. If you plan to use SIGALRM, start the alarm clock using alarm().

5. Perform your query.

Hope I explained clear enough, otherwise feel free to ask again,
	Christopher Etz
-- 
________________________________________________________________________
Christopher Etz           Kopernikusstr. 28       D-65929 Frankfurt/Main
cetz@cetz.rhein-main.de   Tel.: +49 69 318091     Telefax: +49 69 318091
Ingres Q & A
To William's Home Page
Email William