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; |
1
Cool! Except for the ConnsDisabled OFF, which should follow the "(3) Do whatever you need to do." step. So, I'm guessing you vote "yes" for the idea of posting sample code in SQLA... ? 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? Replies hidden
Yeah, I do. I just wish the syntax highlighting was a little better though. @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 :) @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 :) 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. 1
@Markus: You can also use ConnsDisabledForDB to avoid blocking other Databases on the same Engine. 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
|
This is an experiment: Can SQLA replace CodeXchange as well as the NNTP newsgroups?
@Breck: Great idea:)
@Everyone: Calvin's got a better solution, scroll down :)