SQLitening Support Forum

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.

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.

Thanks Bern, I have updated the master copies of the source with your changes.

Thanks so much for sharing your work.   :-)

@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

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.

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?

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.

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.

Thanks Bern - I hadn't looked into your question. I can't believe how little time I have lately for coding. Arrrrgh!  :-)