p_drop_other_connections

This little procedure's been floating around for years and years, and the source code sits in various inaccessible places like the NNTP newsgroups, the execrable CodeXchange and a sub-sub-sub-folder on my C drive called "miscellaneous".

Time to put it somewhere accessible: Google.

As in, "on an actual web page".

As in, here...

How to get full control over a SQL Anywhere server

CREATE PROCEDURE p_drop_other_connections ()
BEGIN

/* How to get full control over a SQL Anywhere server...

(1) CALL sa_server_option ( 'ConnsDisabled', 'ON' );

    At this point, any attempt to open a new connection will
    get "Connections to database have been disabled". 
    However, current connections will still work until...

(2) CALL p_drop_other_connections(); 

(3) Do whatever you need to do.

(4) CALL sa_server_option ( 'ConnsDisabled', 'OFF' );

*/

DECLARE @this_connection_id    INTEGER;
DECLARE @other_connection_id   INTEGER;
DECLARE @drop_command          VARCHAR ( 1000 );

SET @this_connection_id  = connection_property ( 'number' );
SET @other_connection_id = NEXT_CONNECTION ( NULL );

WHILE @other_connection_id IS NOT NULL LOOP

   IF @other_connection_id <> @this_connection_id THEN
      SET @drop_command = STRING ( 
         'DROP CONNECTION ', 
         @other_connection_id );
      EXECUTE IMMEDIATE @drop_command;
   END IF;

   SET @other_connection_id 
      = NEXT_CONNECTION ( @other_connection_id ) 

END LOOP;

END;