Adding CML/LitCrit Stored Procedures

The stored procedures used by the critique-enhanced CML and LitCrit Outlook applications are called from COM components that reside in the business-services tier of the CML application, from VBScript in the ASP pages of the library Web application, and from other stored procedures. The stored procedures written for the previous version of the library application remain in use.

Most procedures continue to work with no modifications but some did require changes, specifically:

• Modify the Fm_admin_checkin procedure
• Create Critique Stored Procedures

Modifying the Fm_admin_checkin Procedure

With the addition of critique features to the CML application the fm_admin_checkin stored procedure needs to be changed.

The fm_admin_checkin stored procedure, developed for the previous version of the CML, is used to check in library items. The procedure updates information (status, location, and borrower) in the item table record. Now the check-in procedure needs to mail a message (with a LitCrit Outlook form as an attachment) that invites the borrower to submit a critique of the checked-in title.

There are two ways to accomplish this: The existing procedure can be changed to include statements that send the message or the procedure can be modified to call another procedure that handles the mailing of the message. The latter is the better solution, as it keeps the function of each stored procedure clear and separate. In summary, these are the necessary changes:

• Modify the fm_admin_checkin stored procedure to call another stored procedure.
• Create a new stored procedure to send a message to the borrower with a blank LitCrit Outlook form as an attachment on the message.

Note  An alternative is to add a second update trigger to the item table. The trigger sends an e-mail message to the borrower when the status of the item is updated from "out" to "in."

Creating Critique Stored Procedures

The critique enhancements to the CML Web application and the LitCrit Outlook application require a number of stored procedures to select or modify information in the tables of the FmLib SQL Server database. Server-Side Critique COM Component describes how the database updates are implemented. The critique applications use stored procedures to:

• Add a critique record to the Critique table. Critique information can be user input from the Submit Critique CML/LitCrit Web page or the LitCrit Outlook form. Input parameters, one for each column except the critique# (an identity column), pass data to the table.
• Update a critique in the Critique table. Use the input parameter that contains the critique# to select the critique record, then update columns with input parameter values.
• Delete a critique in the Critique table. Permissions, described in Setting Permissions on the LitCrit Public Folder, specify who can delete which LitCrit item. To maintain data integrity between the Fitch & Mather Exchange information store and the FmLib SQL Server tables, the critique record must be deleted and the avg_rating in the title table updated whenever a critique is deleted from the LitCrit public folder. The critique#, stored as a hidden property of the LitCrit Outlook form, is passed to the stored procedure as an input parameter.
• Select data from the Critique table for all critiques of a title. The CML/LitCrit Choose Critique Web page displays the critique title, rating, reviewer name and job title, and the critique date. All information on this Web page is from the FmLib database; no information from Exchange is used on this Web page. The bib#, available from the Search Details Web page, is passed to the stored procedure as an input parameter.
• Select all information for one critique. The Display Critique CML/LitCrit Web page displays information from the FmLib SQL Server database and the Exchange Information Store. The critique#, available from the Choose Critique Web page is passed to the stored procedure as an input parameter. The SQL Server stored procedure cannot retrieve information from Exchange, but it can return the unique identifier (the ID of the message object) stored in the Critique table as an output parameter. This identifier is then used by CDO to locate critique information in Exchange.