Sample code: How to get full control over a SQL Anywhere server

[Breck_Carter]

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;

[Breck_Carter]

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

[VolkerBarth]

@Breck: Great idea:)

[Breck_Carter]

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

[Former Member · Accepted solution]

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;

[Breck_Carter · Accepted solution]

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

[Breck_Carter · Accepted solution]

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

[MCMartin · Accepted solution]

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?

[Former Member · Accepted solution]

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

[Breck_Carter · Accepted solution]

@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 🙂

[Breck_Carter · Accepted solution]

@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 🙂

[Former Member · Accepted solution]

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.

[thomas_duemesnil · Accepted solution]

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

[Former Member · Accepted solution]

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