Search Tables and Session Variables

Temporary search-results tables are designed to be stored for the duration of the user session, which means that twenty minutes (the default session time-out value) after the last one is created, all of them should be deleted. The code to delete these tables is in the Session_OnEnd function in global.asa, shown here:

Sub Session_OnEnd
   Dim dctTables, key, cmd
   Set dctTables = Session("_TableList")
   If Not dctTables Is Nothing Then
      If dctTables.Count > 0 Then
         Set cmd = Server.CreateObject("ADODB.Command")
         cmd.ActiveConnection = Application("FmLib_ConnectionString")
         On Error Resume Next
         For Each key In dctTables.Keys
            cmd.CommandText = "DROP TABLE " & key
            cmd.Execute 
         Next
      End If
   End If
End Sub

Note  If the session times out or the Web server is shut down, this Session_OnEnd function is not always called, which means that these tables are not always deleted from the database. (IIS does not automatically call Session_OnEnd when it is shut down.) For this reason, it is recommended that you avoid coding critical functionality into your application that depends on having Session_OnEnd called automatically.

Since the CML application uses temporary search tables, it is necessary to delete them periodically. The fm_fts_drop_tables stored procedure performs this task. To automate this process an administrator can schedule a SQL Server task that executes this stored procedure on a regular basis. This action would be performed as part of deployment of the CML application.

For more information, see Scheduling SQL Server Tasks in the Deployment section of this guide.