Replacing SQL Query Statements

Script on ASP pages, used in Scenario 1: Fitch & Mather Corporate Media Library Application, is changed to implement an RDS customization handler. The SQL queries that were previously coded on the ASP pages are now calls to queries in the [sql identifier] section of the FmLibMap.ini file. Changes were made to these ASP files:

Authors.asp
Details.asp
Search.asp
Subject.asp

Implementing a Handler in Authors.asp

The query that retrieves detail information about a title based on a full-text search of the author name is now a call to the SearchByAuthor query in the [sql identifier] section of the FmLibMap.ini file.

Query on Authors.asp

' Retrieve the details of this title from the database
  strSQL = "SELECT STUFF('../images/icon.gif',15,0,coll) AS Icon," & _
           "       STUFF('javascript:DoLong()',19,0,LTRIM(STR(t.bib#))) AS Link," &_
           "       t.title,t.coll,t.call,convert(char(4),t.pubdate,112) AS Pubdate" &_
           " FROM Title AS t, Author AS a, TitleAuth AS ta" &_
           " WHERE t.bib#=ta.bib# AND ta.auth#=a.auth# AND " &_
           "       ta.auth#=<%= nAuthorNo %> AND t.n_items>0"
      
' Instruct RDS control to retrieve search results from table of results
RDS.ExecuteOptions = adcExecAsync
RDS.FetchOptions = adcFetchAsync
RDS.Server = "http://<%= Request.ServerVariables("SERVER_NAME") %>"
RDS.Connect = "<%= Application("FmLib_ConnectionString") %>"
RDS.SQL = strSQL
RDS.Refresh

Using FmLibMap.ini

The SearchByAuthor query is executed passing the nAuthorNo (auth#, the unique identifier of an author in the author table) as a parameter.

' Retrieve the details of this title from the database
RDS.ExecuteOptions = adcExecAsync
RDS.FetchOptions = adcFetchAsync
RDS.Handler = "MSDFMAP.Handler,FmLibMap.ini"
RDS.Server = "http://<%= Request.ServerVariables("SERVER_NAME") %>"
RDS.Connect = "Data Source=FmLib"
RDS.SQL = "SearchByAuthor(<%= nAuthorNo %>)"
RDS.Refresh

Implementing a Handler in Details.asp

The previous version of Details.asp implemented three RDS recordsets:

A recordset to retrieve the details of this title from the FmLib database
A recordset to retrieve the authors keywords from the FmLib database
A recordset to retrieve the subjects keywords from the FmLib database

The three queries, one for each recordset, were replaced with calls to queries in the [sql identifier] section of the FmLibMap.ini file.

Query on Details.asp

This is the query that retrieves the details of a title from the database. See Details.asp for the queries that select by authors and subjects keywords.

' Retrieve the details of this title from the database
strSQL = "SELECT STUFF('../images/icon.gif',15,0,coll) AS icon," &_
         " CONVERT(char(4),t.pubdate,112) AS pubdate," &_
         " t.bib#,t.title,t.coll,t.call,t.isbn,t.notes,t.description" &_
         " FROM Title AS t WHERE t.bib#=<%= nBibNo %>"
         
' Instruct RDS control to retrieve search results from table of results
Set RDS = Parent.RDS_LongDisplay
RDS.ExecuteOptions = adcExecSync
RDS.FetchOptions = adcFetchUpFront
RDS.Server = "http://<%= Request.ServerVariables("SERVER_NAME") %>"
RDS.Connect = "<%= Application("FmLib_ConnectionString") %>"
RDS.SQL = strSQL
RDS.Refresh

Using FmLibMap.ini

The TitleDetailLong query is executed passing the nBibNo (bib#, the unique identifier of a title in the title table) as a parameter. See Details.asp for the calls that select by authors and subjects keywords.

' Retrieve the details of this title from the database
Set RDS = Parent.RDS_LongDisplay
RDS.ExecuteOptions = adcExecSync
RDS.FetchOptions = adcFetchUpFront
RDS.Handler = "MSDFMAP.Handler,FmLibMap.ini"
RDS.Server = "http://<%= Request.ServerVariables("SERVER_NAME") %>"
RDS.Connect = "Data Source=FmLib"
RDS.SQL = "TitleDetailLong(<%= nBibNo %>)"
RDS.Refresh

Implementing a Handler in Search.asp

This query retrieves all records from a temporary search result table. The table contains the results of a productive search of the FmLib database; a user can create a number of these tables during a session. The temporary search result table has one record for every item in the title table of the FmLib database that matches the criteria the user specified on the Library Search Web page of the CML/LitCrit application.

Query on Search.asp

' Instruct RDS control to retrieve search results from table of results
RDS.ExecuteOptions = adcExecAsync
RDS.FetchOptions = adcFetchAsync
RDS.Server = "http://<%= Request.ServerVariables("SERVER_NAME") %>"
RDS.Connect = "<%= Application("FmLib_ConnectionString") %>"
RDS.SQL = "SELECT * FROM <%= strTableName %> ORDER BY PubDate"
RDS.Refresh

Using FmLibMap.ini

The DetailsFromSearch query is executed passing the strTableName as a parameter.

' Instruct RDS control to retrieve search results from table of results
RDS.ExecuteOptions = adcExecAsync
RDS.FetchOptions = adcFetchAsync
RDS.Handler = "MSDFMAP.Handler,FmLibMap.ini"
RDS.Server = "http://<%= Request.ServerVariables("SERVER_NAME") %>"
RDS.Connect = "Data Source=FmLib"
RDS.SQL = "DetailsFromSearch(<%= strTableName %>)"
RDS.Refresh

Implementing a Handler in Subject.asp

The query that retrieves detail information about a title based on a full-text search of subjects is now a call to the SearchBySubject query in the [sql identifier] section of the FmLibMap.ini file.

Query on Subject.asp

' Retrieve the details of this title from the database
strSQL = "SELECT STUFF('../images/icon.gif',15,0,coll) AS Icon," & _
         "       STUFF('javascript:DoLong()',19,0,LTRIM(STR(t.bib#))) AS Link," &_
         "       t.title,t.coll,t.call,convert(char(4),t.pubdate,112) AS Pubdate" &_
         " FROM Title AS t, Subject AS s, TitleSubj AS ts" &_
         " WHERE t.bib#=ts.bib# AND ts.subj#=s.subj# AND " &_
         "       s.subj#=<%= nSubjectNo %> AND t.n_items>0"

' Instruct RDS control to retrieve search results from table of results
RDS.ExecuteOptions = adcExecAsync
RDS.FetchOptions = adcFetchAsync
RDS.Server = "http://<%= Request.ServerVariables("SERVER_NAME") %>"
RDS.Connect = "<%= Application("FmLib_ConnectionString") %>"
RDS.SQL = strSQL
RDS.Refresh

Using FmLibMap.ini

The SearchBySubject query is executed passing the nSubjectNo (subj#, the unique identifier of a subject in the subject table) as a parameter.

' Retrieve the details of this title from the database
RDS.ExecuteOptions = adcExecAsync
RDS.FetchOptions = adcFetchAsync
RDS.Handler = "MSDFMAP.Handler,FmLibMap.ini"
RDS.Server = "http://<%= Request.ServerVariables("SERVER_NAME") %>"
RDS.Connect = "Data Source=FmLib"
RDS.SQL = "SearchBySubject(<%= nSubjectNo %>)"
RDS.Refresh