If this sample was helpful to you, vote it up.

If you have an improved version, or a closely related sample, post it as an answer.

If you have a question or complaint, post it as a comment (or an answer, if you have a lot to say :)

BEGIN
   DROP PROCEDURE p_drop_other_connections;
   EXCEPTION WHEN OTHERS THEN
END;

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;

asked 19 Feb '10, 19:53

Breck%20Carter's gravatar image

Breck Carter
26.8k420580826
accept rate: 20%

edited 15 Mar '12, 09:08

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646

2

This is an experiment: Can SQLA replace CodeXchange as well as the NNTP newsgroups?

(19 Feb '10, 19:55) Breck Carter

@Breck: Great idea:)

(21 Feb '10, 10:04) Volker Barth
1

@Everyone: Calvin's got a better solution, scroll down :)

(21 Feb '10, 10:24) Breck Carter

create procedure sp_Drop_Connections()
begin

call sa_server_option( 'ConnsDisabled', 'ON' );

FOR CONNECTION_FOR AS CONNECTION_CURSOR DYNAMIC SCROLL CURSOR FOR
            SELECT number as CONNECTION_NUMBER
            FROM sa_conn_info()
            WHERE number <> Connection_Property('Number')
            ORDER BY number asc do
            EXECUTE IMMEDIATE 'DROP CONNECTION ' || CONNECTION_NUMBER;
        END FOR;

--DO STUFF

call sa_server_option( 'ConnsDisabled', 'OFF' );
end;
permanent link

answered 19 Feb '10, 20:17

Calvin%20Allen's gravatar image

Calvin Allen
1.5k232638
accept rate: 25%

edited 20 Feb '10, 14:08

1

Cool! Except for the ConnsDisabled OFF, which should follow the "(3) Do whatever you need to do." step.

(20 Feb '10, 08:59) Breck Carter

So, I'm guessing you vote "yes" for the idea of posting sample code in SQLA... ?

(20 Feb '10, 09:00) Breck Carter

Very good. Do you know a way how to limit the allowed connections to only local ones, so that all your administrative tools still work on the server, but any remote connections are refused?

(20 Feb '10, 09:56) Martin
Replies hidden

Yeah, I do. I just wish the syntax highlighting was a little better though.

(20 Feb '10, 14:07) Calvin Allen

@Calvin: If you post a new Sample code "question" (which I hope you do), please include the first three lines "If this sample was helpful..." or words to that effect. If folks are wondering why the wording of some of my posts is sometimes a bit repetitive, it's because the person reading it might be completely new to SQLA... this question might be the very first question the person has read. Eventually I'll fix up the FAQ but you can NEVER assume people read the Help :)

(21 Feb '10, 10:22) Breck Carter

@Calvin: I'm not clicking the "best answer" checkmark until the software lets me offer a bounty (and instantly give it). When that happens, there doesn't seem to be any visible trail except for the fact you've suddenly got more points... I wish that happened with money in bank accounts :)

(21 Feb '10, 10:29) Breck Carter
1

I think you should remind the reader that ConnsDisabled is a server option and therefore blocks all connections to all databases on that engine. But dropped are only the connections from the currently connected database. If your code has an error in "--DO STUFF" you have to restart the engine if you forget to reenable the connections in e. g. your ISQL session. I know that's all common sense, but i sometimes seem to loose that according to my personal experience.

(12 Mar '10, 10:55) Markus Dütting
1

@Markus: You can also use ConnsDisabledForDB to avoid blocking other Databases on the same Engine.

(10 Jan '11, 13:44) Thomas Dueme...

You could set a database option and issue a raiserror in the login_procedure.

(05 Oct '12, 11:52) Markus Dütting
More comments hidden
showing 5 of 9 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×124
×10
×9
×7

question asked: 19 Feb '10, 19:53

question was seen: 1,487 times

last updated: 05 Oct '12, 11:52