Friday, November 6, 2009

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;

No comments: