Session_OnEnd ASP Classic

Tony Owers
Tony Owers used Ask the Experts™
on
I need to tap into the Session_OnEnd event in the session object. and call a stored procedure in MS SQL

My site is built in ASP Classic I want to perform a database clean up when the users session ends or they log out

the logout is handled, but i need a way to cover the user just leaving the site without logging out..

Can anyone help me with this please..
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013

Commented:
Hi Tony, I have never used that function or a global.asa for that matter. I always thought that Session_OnEnd was not reliable and more so in iis7 and up.  

You might try to create a vbs that will do what you want with sql server then hit the vbs inside of your Session_OnEnd.

Can you just use a scheduled task that runs every x minutes that will run your SP and clean up anything old?

Author

Commented:
what the SP will be doing is deleting a database that was created at the start of the session, the user can update and modify anything in the db, but when they have finished it gets deleted.

it is for a demonstration site, i have read that the on session end isnt reliable, due to other factors ending the session so that event doesnt get fired..

i thought about putting in a 1 hour automated cleanup, but it is possible that someone could still be looking at the app, is there a way in MS SQL to see how long since the last query was made without that query being counted, (the database will only be getting hit from the one user)..
Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013

Commented:
What about creating the database with the name

 session("user") = year(now)&"_"&month(now)&"_"&day(now)&"_"&Session.SessionID

Open in new window


Create another database to track active sessions and users.

The active users table may contain the information they used to sign up with as well as field that stores the current time stamp (now) as well as a field that stores session("user") and status flag (active/inactive).  Default the status to active.  Generate the sql database with the name from the value of session("user").

Another table that tracks page loads for the current user. Fields can be session("user"), page_name and a time stamp from now().

Now run a scheduled task that
1) Finds all active users from your user table where the status is set to active.  
2) Grab the timestamp for that user.
3) Look up the last row of data from the table that tracks page loads and grab that timestamp.
4) Check the differences between the two timestamps DateDiff("n",fromDate,toDate)
5) If the difference is greater than 20 minutes, set that user to inactive and remove the corresponding database.

In general, I found using sessions not reliable. For log in systems, I started using a token and wrote an article on the method I use https://www.experts-exchange.com/articles/18259/Classic-ASP-Login-System-Utilizing-a-Token.html.  You may be able to take part of it for this use or even just revamp how you are doing log ins for this.
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

"is there a way in MS SQL to see how long since the last query was made"

Beside the way described above and which is IMO good you may also use pure SQL approach: If you ensure all the user queries will be executed under the specific database connection which remains open throughout the whole session then you may execute e.g.  sp_who2  from a different connection and it will show all inactive user connections (see the LastBatch column). And this check can be done in a scheduled job as mentioned above.

Author

Commented:
thank you, i was halfway there i was going to use a random word generator to name the db but using session and date info is easier, but this does highlight another issue i have..

i cant get agent jobs working
everything looks ok, it is running but always fails on not being able to connect to the db

i did try for weeks to get this working but was spending too much time on it so i used bat files instead and used windows scheduler to do what i needed,

ill open another question on the SQL Agent, its bugging me that i have had to use a work around instead of doing it right.
Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013

Commented:
Yes, that is a good idea. Just a pure sql question. Leave everything out of your new question except for what you want to do with mssql server and attack it that way since this is really about housekeeping tasks.

Author

Commented:
ok i have SQL Agent working now,

so what i think i will need to make this work,

  1. i need to check what databases exist,
  2. exclude databases i am not interested in,
  3. check when the last access to the ones i am interested in was,
  4. if time idle reaches certain point, delete database.

i think i am ok doing last 3, how do i determine what db's exist,
To list all available databases issue
SELECT * FROM sys.databases

Open in new window

The user must have sufficient rights for this query. Just try and you'll see the result.
Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013

Commented:
On your last step, idle time is not a good indicator that somebody is not still on your site. If you have ever watched somebody on a classic asp site that uses sessions for logging in curse at the screen after they have been staring at the screen either 'thinking' or typing only to get past that 5 minute mark and they get force logged out. Somewhere in the archives there are articles on why increasing this is not a good idea which is why I went the route of using a token.  In any case, it sounds like you have something working.  That is the most important.

Author

Commented:
yes, i understand that, this is for a demo instance of my app, user will be aware that the instance will disappear a set time from last action.

my session time out is 20 min, as the database and connection string will be dynamic, once the session times out if they log back in they will create another db and start again from the beginning.

this is the best way i could figure to automate this step, all other ways i could think of would require me to set up a demo site and give them an appointment to view

if you have a better idea how to achieve this i would be grateful
Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013

Commented:
Maybe it was just confusion but you mentioned idle timeout.  That is different from session timeout. Idle timeout is 5 minutes vs session timeout defaults to 20.

Without knowing your app and details, hard to say what is a best option other than the one that is already working and requires no additional effort :)
When to delete the db is an issue i am not sure how to handle, leaning now towards 2hrs from database creation should give them ample time to see what the app can do...

but that throws up other problems like if they log out and back in that would create a new db, not continue with the old, i could use a cookie but really dont like relying on them
Developer & EE Moderator
Fellow 2018
Most Valuable Expert 2013
I addressed that in my earlier post https://www.experts-exchange.com/questions/29164823/Session-OnEnd-ASP-Classic.html#a42983089

I personally would use a token that is stored either on the server as a file or database to track log ins and determine your logic.

The real issue is there are a lot of ways to give a user a demo and without knowing anything about your application or how it is hosted and limitations, it is hard to give a more detailed answer.

Some schools of thought are to force a user to give up at least a name and email to get access to the demo. When they submit their email, send a link to confirm and also make sure to let the user know an email has been sent to confirm on the page after they submit. From a marketing point of view, when they initially submit their name and email, create a contact and mark the contact as a prospect. Here you can also check if the email has been used before and define in your business logic how to proceed.

Once they confirm their email, you can continue to build their demo. Keep a table of your prospects that notes which database has been created and when it expires. Again, when they try and log in later, you can use your business logic to give them a message welcoming them back to continue or let them know the demo has been recreated from scratch or that they need to contact you to reinstate the demo since it was used once before.

This gives you a lot more control then trying to track sessions.

Another option is to have just one database for demo that everybody has access to (once they sign up with you). Have a duplicate demo database set up with your initial testing data. Then every x minutes or hours run a scheduled task that replaces the the live demo database from the duplicated db with the original test data.
Thanks Scott, i did read your token article, cant do that as the login is already done and too much would need changing, but will keep it in mind for any future apps i build..

i have been thinking along the same lines as you, and have decided to go with a signup page for the demo that i will create from the info they input, sending them an email with login details, and expiring the DB after 8 hours, it is a complex app so any serious clients will want enough time to go through it properly.

i will add a return clause to the logic to streamline them getting started again if they get side tracked from the original demo or run out of time.. could even add in an extend time feature within the demo..

thanks for your help..
thank you for your help both of you, think i have something i can move forward with now...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial