Stored Procedures
The CML development team decided to use stored procedures whenever possible. The stored procedures, called from CML COM components using ADO, select, add, change, and delete records in the CML's SQL Server database. Stored procedures are also used to send overdue notices to delinquent borrowers and to send recall notices.
The alternative to stored procedures is to embed the query in the VBScript of the ASP page or the Visual Basic coding of the component. The topics RDS in the CML Application, CML Data Access Using RDS, and Using RDS ActiveX Controls discuss how RDS executes queries. In summary, the advantages of stored procedures are:
- Improved performance. Precompiled stored procedures execute more quickly.
- Flexibility. Stored procedures can call other procedures.
- Reusability. A stored procedure can be called from any number of other stored procedures.
- Ability to return status value to calling procedure to indicate success or failure.
The application uses stored procedures to perform updates to the database, create and mail recall and overdue notices, support full-text search of the library, and perform housekeeping tasks in the database.
CML Stored Procedures
- fm_admin_checkin
- Checks in an item. Changes the status of the item to "in" indicating the item is available in the item table. The item is identified by its bar code.
- fm_admin_checkout
- Checks out an item. Changes the status of the item to "out" and updates the last date of checkout and the due date, increments the number of times the item has been checked out, and enters the borrower. Calls the fm_queue_dequeue stored procedure.
- fm_admin_overdue_notices
- Selects all recipients of overdue notices and creates an overdue message. Calls the fm_admin_send_notice stored procedure, which e-mails the message.
- fm_admin_recall_notices
- Selects all recipients of recall notices and creates a recall message. Calls the fm_admin_send_recall stored procedure, which e-mails the message.
- fm_admin_send_notice
- E-mails overdue notices.
- fm_admin_send_recall
- E-mails recall notices.
- fm_admin_update_item
- Updates an item record.
- fm_admin_update_title
- Updates a title record.
- fm_fts_create_table
- Creates a table where the results of a library search are stored. This procedure is called from the fm_fts_optAuthor_contains, fm_fts_optSubject_contains, and fm_fts_optTitle_contains stored procedures.
- fm_fts_drop_table
- Deletes temporary search result tables. This process is an administrative tool for deleting any residual temporary tables.
- fm_fts_optAuthor_contains
- Calls the fm_fts_create_table stored procedure, performs a full-text search on the author table, and inserts the results of the search into a temporary table.
- fm_fts_optSubject_contains
- Calls the fm_fts_create_table procedure, performs a full-text search on the subject table, and inserts the results of the search into a temporary table.
- fm_fts_optTitle_contains
- Calls the fm_fts_create_table procedure, performs a full-text search on the title table, and inserts the results of the search into a temporary table.
- fm_fts_setup
- Creates the CML database full-text catalog. This is a one-time administrative procedure.
- fm_queue_cancel
- Deletes a specified request for a title. The users need to be able to cancel a request.
- fm_queue_dequeue
- Deletes the first request for a title. Called by the fm_admin_checkout stored procedure.
- fm_queue_enqueue
- Deletes a specified request for a title.
- fm_queue_length
- Finds the length of the request queue for a title.
- fm_queue_location
- Finds where a person is in the request queue for a title.
- fm_user_update
- Adds and updates information about a specified borrower.
Note This list of stored procedures evolved throughout the design of the CML application. An earlier design included the functionality of the stored procedure fm_fts_create_table within each of the three procedures, fm_fts_optAuthor_contains, fm_fts_optSubject_contains, and fm_fts_optTitle_contains. The developers recognized common statements in the three procedures and created one new stored procedure (fm_fts_create_table) from the common statements. Now common functionality only needs to be maintained in one rather than three places.
Naming CML Stored Procedures
The development team decided to use the prefix "fm" for all the stored procedures that belong to the Fitch & Mather Corporation applications. This makes it easy to distinguish the F & M procedures from those of other applications. The second segment of the procedure name identifies the process the procedure is associated with. For example, all the procedures for full-text search contain "fts" in the second segment and the administrative procedures show "admin". The remainder of the name describes what the stored procedure does.