SQLitening Support Forum

SQLitening Support => You've got Questions? We've got Answers! => Topic started by: Bern Ertl on April 30, 2013, 02:38:37 pm

Title: slKill
Post by: Bern Ertl on April 30, 2013, 02:38:37 pm
A long time ago, I had asked Fred about the possibility of adding a function to kill an active connection.  I'm developing an application that uses a login system to manage licenses and I wanted to implement an administrator function that would allow an admin to manage access to the system (including logging out / disconnecting users when required). 

The SQLitening Server Admin program has this function.  There are a couple of functions implemented in SQLIitening that are "admin" only - not exposed through the include file for the client side.

It's actually not too difficult to expose these admin functions.  Here's what I did to implement slKill in case it is of use to anyone else:

In SQLitening.INC, at the end, add:DECLARE FUNCTION slKill LIB "SQLitening.Dll" ALIAS "slKill" ( BYVAL rlTcpFileNumber AS LONG) AS LONG

In SQLitening.BAS, declares section, add:DECLARE FUNCTION UsingKill  CDECL( BYVAL rlTcpFileNumber AS LONG) AS LONG

In SQLitening.BAS, after slDisconnect, add:FUNCTION slKill ALIAS "slKill" ( BYVAL rlTcpFileNumber AS LONG) EXPORT AS LONG
'   Posts a request to kill another connection.  The other connection won't
'   be killed until it attempts a communication with the server.

   LOCAL lhRutAddr AS DWORD

   'Init Stuff
   RESET tlLastError

   'Disconnect from server
   irGetRutAddress "SQLiteKill", lhRutAddr
   CALL DWORD lhRutAddr USING UsingKill( rlTcpFileNumber) TO tlLastError

   FUNCTION = tlLastError

END FUNCTION


In SQLiteningClient.BAS, after SQLiteDisconnect SUB, add:FUNCTION SQLiteKill ALIAS "SQLiteKill" ( BYVAL rlTcpFileNumber AS LONG) EXPORT AS LONG

   'Post kill request
   FUNCTION = DoRequest( %reqAdmin, 3, 0, MKDWD$( rlTcpFileNumber), 0)

END FUNCTION


~~~

To use this, you will need to track the Tcp file numbers for the active connections in a database table.  I'm adding records to a master/admin table when users log in and using SQLIitening server exits to clean up when they disconnect.
Title: Re: slKill
Post by: Bern Ertl on April 30, 2013, 06:23:42 pm
Fred's kill handler in the server didn't check whether or not the tcp file handle was actually a valid (active) value, so I modified it.

In SQLiteningServer.BAS : ProcessRequest() : %reqAdmin case handler, replace CASE 3 with the following:      CASE 3   ' Kill connection
         llC = CVL(rsDataIn, 11)
         llRC = GetStatus(2, lsA)
         IF llRC = %SQLITE3_OK THEN
             FOR llB = 1 TO PARSECOUNT( lsA, $VT)
                 IF VAL( PARSE$( lsA, $VT, llB)) = llC THEN
                     'Requesting to kill an active connection
                     llA = %True
                     EXIT FOR
                 END IF
             NEXT

             IF llA = %True THEN
                 IF glKillTcpFileNumber THEN
                    glKillTcpFileNumber = llC
                    wsDataOut = MKI$(1)
                 ELSE
                    glKillTcpFileNumber = llC
                    wsDataOut = MKI$(0)
                 END IF
                 LogIt 1, "Kill Task #" & FORMAT$(glKillTcpFileNumber)
             ELSE
                 wsDataOut = MKI$( %SQLitening_InvalidKeyOrNotFound)
             END IF
         ELSE
            wsDataOut = MKI$( llRC)
         END IF


You will also need to add a declare for variable llC AS LONG.
Title: Re: slKill
Post by: Paul Squires on April 30, 2013, 10:48:36 pm
Thanks Bern, I have updated the master copies of the source with your changes.

Thanks so much for sharing your work.   :-)
Title: Re: slKill
Post by: Rolf Brandt on May 01, 2013, 04:41:56 am
@Paul:
Whenever you are ready to publish it let me know so that I can make the necessary changes to the documentation.

@Bern:
Thanks for this useful addition.

Rolf
Title: Re: slKill
Post by: Bern Ertl on May 01, 2013, 12:28:51 pm
Thanks Paul / Rolf.

I don't mind sharing - especially if it means less work for me when a new release is issued containing updates contributed by someone else.   ;)

In my application code, I am now able (with the improvement to the kill handler in server) to check the return value from slKill and handle cases where my tables still show a connection, but where the server doesn't.  Ie. in case the server crashed and SQLitening was unable to close elegantly - firing the server exit that would update my table.

Unfortunately, the kill handling that Fred implemented is "passive".  It doesn't forceably kill a connection until the connection owner attempts a communication with the server.  So, an administrator that is attempting to kill a live connection can only post a request and then wait as it stands now.  If anyone else has the inclination, a modification (option / additional feature) for an active solution that doesn't wait would seem to be very useful.  I haven't dug into the SQLitening code deep enough yet to figure out if that's even possible.  I'm guessing Fred might have coded it from the beginning if it were straightforward.
Title: Re: slKill
Post by: Bern Ertl on May 02, 2013, 03:33:49 pm
Well, I couldn't resist digging a little deeper, but I'm puzzled by what I'm seeing.  Maybe one of you can explain it.

In SQLiteningServer.BAS : ConnectionMain(), I made the following edit:     ' Check if they want to kill me
LogIt 7, "llTcpFileNumber = " + FORMAT$( llTcpFileNumber) + ", glKillTcpFileNumber = " + FORMAT$( glKillTcpFileNumber) + ", lhLastMsgTime = " + FORMAT$( lhLastMsgTime)  '<<< added this line
     IF llTcpFileNumber = glKillTcpFileNumber THEN

I ran my application and logged in as a user.  I then ran a second instance of my application and logged in as the administrator.  As the Admin, I then killed the user connection.  I then exited the Admin session.  After a pause, I then attempted to use the user session to force a message to SQLitening which prompted the session termination.

Upon checking the log, I see the entries for the line I added when I log in as the user.  They repeat several times per second.   However, when I log in as the administrator, the messages for the user connection stop and I only see messsages for the admin connection start/repeat.  When I log out as the admin, there are no more messages posted to the log file until I force the user account to attempt a communication with the server.

As ConnectionMain() is a thread function, I was expecting to see log entries for both the user and admin being posted for the entire time the connections were live.  Ideas?
Title: Re: slKill
Post by: Bern Ertl on May 02, 2013, 03:39:40 pm
This is what I'm seeing in the log file:
Quote130502123954 Conn #3 SK 196 BernardHAL2006127.0.0.1
130502123954 Note llTcpFileNumber = 3, glKillTcpFileNumber = 0, lhLastMsgTime = 45594
130502123954 Note llTcpFileNumber = 3, glKillTcpFileNumber = 0, lhLastMsgTime = 45594
130502123954 Note llTcpFileNumber = 3, glKillTcpFileNumber = 0, lhLastMsgTime = 45594
130502123954 Note llTcpFileNumber = 3, glKillTcpFileNumber = 0, lhLastMsgTime = 45594
130502123954 Note llTcpFileNumber = 3, glKillTcpFileNumber = 0, lhLastMsgTime = 45594
130502123955 Note llTcpFileNumber = 3, glKillTcpFileNumber = 0, lhLastMsgTime = 45595
130502123955 Note llTcpFileNumber = 3, glKillTcpFileNumber = 0, lhLastMsgTime = 45595
130502123955 Note llTcpFileNumber = 3, glKillTcpFileNumber = 0, lhLastMsgTime = 45595
130502123955 Note llTcpFileNumber = 3, glKillTcpFileNumber = 0, lhLastMsgTime = 45595
130502123955 Note llTcpFileNumber = 3, glKillTcpFileNumber = 0, lhLastMsgTime = 45595
130502123955 Note llTcpFileNumber = 3, glKillTcpFileNumber = 0, lhLastMsgTime = 45595
130502123955 Note llTcpFileNumber = 3, glKillTcpFileNumber = 0, lhLastMsgTime = 45595
130502123955 Note llTcpFileNumber = 3, glKillTcpFileNumber = 0, lhLastMsgTime = 45595
130502123955 Note llTcpFileNumber = 3, glKillTcpFileNumber = 0, lhLastMsgTime = 45595
130502123955 Note llTcpFileNumber = 3, glKillTcpFileNumber = 0, lhLastMsgTime = 45595
130502123955 Note llTcpFileNumber = 3, glKillTcpFileNumber = 0, lhLastMsgTime = 45596
130502123955 Note llTcpFileNumber = 3, glKillTcpFileNumber = 0, lhLastMsgTime = 45596
130502123955 Note llTcpFileNumber = 3, glKillTcpFileNumber = 0, lhLastMsgTime = 45596
130502123958 Note llTcpFileNumber = 4, glKillTcpFileNumber = 0, lhLastMsgTime = 0
130502123958 Conn #4 SK 196 BernardHAL2006127.0.0.1
130502123958 Note llTcpFileNumber = 4, glKillTcpFileNumber = 0, lhLastMsgTime = 45599
130502123958 Note llTcpFileNumber = 4, glKillTcpFileNumber = 0, lhLastMsgTime = 45599
130502123958 Note llTcpFileNumber = 4, glKillTcpFileNumber = 0, lhLastMsgTime = 45599
130502123958 Note llTcpFileNumber = 4, glKillTcpFileNumber = 0, lhLastMsgTime = 45599
130502123958 Note llTcpFileNumber = 4, glKillTcpFileNumber = 0, lhLastMsgTime = 45599
130502124000 Note llTcpFileNumber = 4, glKillTcpFileNumber = 0, lhLastMsgTime = 45601
130502124000 Note llTcpFileNumber = 4, glKillTcpFileNumber = 0, lhLastMsgTime = 45601
130502124000 Note llTcpFileNumber = 4, glKillTcpFileNumber = 0, lhLastMsgTime = 45601
130502124000 Note llTcpFileNumber = 4, glKillTcpFileNumber = 0, lhLastMsgTime = 45601
130502124000 Note llTcpFileNumber = 4, glKillTcpFileNumber = 0, lhLastMsgTime = 45601
130502124007 Admn Kill Task #3
130502124007 Note llTcpFileNumber = 4, glKillTcpFileNumber = 3, lhLastMsgTime = 45607
130502124012 Note llTcpFileNumber = 4, glKillTcpFileNumber = 3, lhLastMsgTime = 45613
130502124012 Dcon #4 Dropped
130502124012 Exit Exit #2 has run -- TcpFile=4 was not logged.
130502124018 Note llTcpFileNumber = 3, glKillTcpFileNumber = 3, lhLastMsgTime = 45619
130502124018 Dcon #3 Killed
130502124019 Exit Exit #2 has run -- TcpFile=3, Changed 1 records.
Title: Re: slKill
Post by: Bern Ertl on May 10, 2013, 10:02:23 am
Nevermind.  I figured it out.  The TCP RECV command has a built-in timeout interval.  Default setting is for 60 seconds.  I didn't wait long enough to see it in the log data I posted earlier. 

So, slKill will force a connection to die within the next 60 seconds whether that connection attempts to send any data/requests or not.
Title: Re: slKill
Post by: Paul Squires on May 10, 2013, 05:12:34 pm
Thanks Bern - I hadn't looked into your question. I can't believe how little time I have lately for coding. Arrrrgh!  :-)