> I'm using a table containing information for each logged on user to
> regulate the number of licensed users allowed for our product. Based
> on a timer in the application, the usage record is updated
> periodically to show the user is still active (and the app didn't
> abort due to a bug or lost connection). If the timespan in the
> usage record exceeds a certain duration, I know the user is no
> longer connected, the record can be deleted and a seat opened up for
> another user. Also, a user logging off normally deletes this usage
> record. Since the app uses a single Firebird user ID for all client
> application accesses, I cannot use the Firebird list of connected
> users.

Remember also that gbak makes a connection as well, and you may not
want to have such services counting towards your active user count.

Unlike a lot of other DBMS, there is no internal mechanism required to
restrict active connections etc for licensing, so there is a bit of
irony there I am sure.

Our implementation of the same requirement is similar. The login
process inserts a record into a table. If there are too many, the
login fails and the appropriate message is generated. Of course we all
know that select count queries are bad, but we are realistically
talking < 5 active connections 99% of the time and pretty much always
< 100 active.

There is the possibility due to isolation that two near simultaneous
logins could allow the second in when really they shouldn't but we
decided that was a) incredibly unlikely and b) we aren't that fussed
about it. If they are that short of seats, then they will buy more at
some stage.

99% of our deployments are using Terminal Services, so it is not a
usual occurance to just lose a client. (It is normal for the odd
connection to the terminal server to be broken, but the session
timeout rules will apply and if they don't reconnect within the
specified timeframe the application will gracefully exit removing the
record).

In any case, part of the login stored procedure removes the user
logging in from the active user table if they are already there first.
So if a user crashes, if they log back in the seat is reused.

If the user attempting to log in has enough privileges, we provide
them with a list of identifyable information that tells them who
exactly is logged into each seat and computer details (name / ip / etc).

We are also able to reserve seats for given locations, so we make sure
that at least one seat is always available for a head office connection.

You need to balance the need for locking down your system with the
need to not annoy the heck out of anyone who wants to use it. For our
situation, we have a nice balance. You may want to lock down a bit
tighter or be a bit more flexible depending on how likely someone is
to abuse your licensing policy and how you are resourced to deal with
abandoned client connections.

>
> This has worked OK in the past, but there are a couple of problems..
>
> 1. The usage record is not immediately eliminated after an app
> abend. Sometimes a user has to wait to logon.
> 2. I now want to implement a logoff strategy if a user has had no
> activity for a period of time. I'm using FIBDatabase component which
> can be used to log off the user, but the update of the usage record
> periodically fools the component into thinking there is activity.
>
> I could use a 2nd database connection to perform the periodic
> updates of the usage record, but I shy away from this due to Windows
> limits on network connections.

Really?? I suppose if you left the second connection open for the
whole time this may be an issue, but if you just made the connection
(it would have to be a background thread or the seat may be released
during a long running process) it would be subsecond running surely.

Even with 500 active clients, I would expect no more than 10
additional connections at any point in time.

You could use an event that fires when a new record is inserted into
the active users, but be prepared for a self induced DDOS attack if
you don't delay for a random time. When user 501 comes along, 500
clients attempt to update their usage records within a few seconds
which may not look pretty. If they only connect if they haven't
connected for 10 minutes, and randomly wait up to 5 minutes, or you
only have a handful of users, this may work

>
> So, I'm wondering if the database connection information can be
> used. I'm aware that the "CURRENT_CONNECTION" is available for use,
> but is there any way to determine if another connection is still
> running?
>

No

> Is there any way to post an event if a connection is closed?
>

No



Adam