Integrating Aleph Course Reserves with an Existing EReserve System - PowerPoint PPT Presentation

Loading...

PPT – Integrating Aleph Course Reserves with an Existing EReserve System PowerPoint presentation | free to view - id: 4c79d-ZDc1Z



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Integrating Aleph Course Reserves with an Existing EReserve System

Description:

Faculty course management software is e-College. ... URL for course reserve page: http://lib.tcu.edu/www/ereserve/TitleList.asp?Term=Spring 2007&Course=536 ... – PowerPoint PPT presentation

Number of Views:80
Avg rating:3.0/5.0
Slides: 30
Provided by: kerrybo
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Integrating Aleph Course Reserves with an Existing EReserve System


1
Integrating Aleph Course Reserves with an
Existing E-Reserve System
Kerry BouchardTexas Christian University
Presentation for ELUNA Conference, 2007
2
Background
  • TCU is running Aleph 16.02 (migrating to version
    18 this summer). Went live January 2005.
  • Our e-reserve system was developed in-house
    starting in 1997.
  • Data stored in MS SQL Server database.
  • Staff use MS Access as front-end for managing
    e-reserves.
  • ASP scripts on web server display e-reserves to
    public.
  • Average around 50 courses with either online or
    print (or both) reserves each spring/fall.
  • Faculty course management software is e-College.

3
Why Pull Aleph Course Reserves Into E-Reserve
System?
1. Prefer to give users a browse interface rather
than a search screen for course reserves.
Course-level browse
4
Why Pull Aleph Course Reserves Into E-Reserve
System?
1. prefer to give users a browse interface
Title-level browse -- online titles
5
Why Pull Aleph Course Reserves Into E-Reserve
System?
1. prefer to give users a browse interface.
Title-level browse print titles. (If course has
both online and print, then appear on the same
page, but in separate lists.)
6
Why Pull Aleph Course Reserves Into E-Reserve
System?
2. Existing e-reserves system linked to other
systems
Applet in campus portal looks up courses student
is enrolled in and displays links to e-reserves
as appropriate.
7
Why Pull Aleph Course Reserves Into E-Reserve
System?
2. existing e-reserves system linked to other
systems
Reserves staff click on a button to generate
e-mail to instructors with links they can add to
eCollege or elsewhere
URLS for ART 30343, Modern Art II
e-reserves.URL for course reserve
pagehttp//lib.tcu.edu/www/ereserve/TitleList.as
p?TermSpring2007Course536Gerald Needham -
Japanese Influence on French Paintinghttp//libil
l.lib.tcu.edu/ereserves/EreserveID.asp?fileART
30343080_Needham.PDFCourse536TermSpring2007
Howard Lay - Pictorial Acrobaticshttp//libill.li
b.tcu.edu/ereserves/EreserveID.asp?fileART
30343080_Lay.PDFCourse536TermSpring2007
8
Why Pull Aleph Course Reserves Into E-Reserve
System?
2. existing e-reserves system linked to other
systems
Our version of a My Library application
automatically includes links to e-reserves for
courses the student is enrolled in
9
Components
  • Oracle Listener process running on Aleph server
  • Firewall port opened from web server with
    e-reserve scripts to Oracle Listener
  • ODBC definition for Aleph on the web server (a
    Windows IIS server in the case of TCU)
  • SQL statements added to e-reserve scripts to
    pull in Aleph course reserve data
  • MarcField function added to e-reserve scripts to
    pull Marc data from Aleph Z00.Z00_DATA fields
    (which contain entire MARC record)

10
ODBC Connection to Aleph
Details of setting up an ODBC connection are
described in poster presentation, also available
at http//lib.tcu.edu/ELUNA/2007.htm
11
SQL to get list of Aleph Courses
xxx30.Z108 table contains course information
(year and semester code have been passed to
script as variables).
SELECT Z108_COURSE_NAME As CourseName,
Z108_REC_KEY, Z108_INSTRUCTOR_NAME FROM
TCU30.Z108 WHERE (substr(Z108_DATE_FROM, 1, 4)
'" sYear "') AND (Z108_PERIOD Like '"
sSemester "') " ORDER BY Z108_REC_KEY
12
Combining Aleph E-Reserve Courses in One Display
  • Separate record sets are retrieved for courses
    in Aleph and in E-Reserves, sorted by course code
    and section.
  • To combine them in one list, the main loop in
    the script compares the course code (e.g.,
    ENGL20233) and section in the Aleph
    Z108_REC_KEY field to the corresponding info in
    the e-reserve records.
  • When keys are identical, a BOTH legend is
    added to the display string, signifying that both
    print and online reserves are available for that
    course.
  • Loop to do this is messy (lots of if/elses)
    but logically straight forward.

13
Combining Aleph E-Reserve Courses in One Display
  • Problem
  • E-Reserve course sections identified by same
    section numbers used in PeopleSoft, in order to
    automatically link to student enrollment data.
  • In Aleph, Reserve staff use professors initials
    to differentiate course sections, because
    otherwise is difficult for them to look things up
    (theyd have to keep a list mapping section
    numbers to professors names).

14
Combining Aleph E-Reserve Courses in One Display
  • Solution
  • Added a new field to e-reserves database so that
    staff can record professors initials to match
    Aleph data.

15
Combining Aleph E-Reserve Courses in One Display
Display illustrating mix of courses with
print-only, online-only, and both kinds of titles
16
SQL to get list of titles for course
  • This is tricky, because
  • Need to parse the Marc bibliographic data. Even
    some linking info is in a Marc field.
  • Aleph linking fields are often segmented some
    SQL JOINs dont run efficiently
  • Bibliographic info may be in either an xxx30.Z00
    record or an xxx00.Z00 record.
  • (xxx30.Z00 records are titles that only exist in
    reserves, like instructor copies. When an item in
    the main catalog is placed on reserve, a link is
    built to the xxx00.Z00 record.)

17
SQL to get list of titles for course
18
SQL to get list of titles for course
SQL to retrieve title info runs in two steps.
First step retrieves all the titles for a course,
linking from the xxx30.Z108 table to xxx30.Z00
table through the xxx30.Z11 index. For linking,
the Z11_TEXT field is used instead of the
Z11_REC_KEY field because when the Z108_REC_KEY
field includes dashes, these are converted to
spaces in the Z11_REC_KEY field.
19
SQL to get list of titles for course
Code fragment showing construction of SQL
statement to retrieve xxx30.Z00 title records
attached to an xxx30.Z108 course record
sID Request("AlephID") 'from Z108_REC_KEY sZ11_l
ink "a" sID sQ "SELECT
Z108.Z108_COURSE_NAME As CourseName, -
Z108.Z108_PERIOD, Z00_DATA, Z00_DOC_NUMBER "sQ
sQ "FROM TCU30.Z108 INNER JOIN (TCU30.Z11 INNER
JOIN - TCU30.Z00 ON Z11.Z11_DOC_NUMBERZ00.Z00_DOC
_NUMBER) " sQ sQ "ON '" sZ11_link
"'Z11_TEXT " sQ sQ "WHERE Z108_REC_KEY '"
sID "'" set RSAleph connAleph.execute(sQ)
20
SQL to get list of titles for course
  • Now that we have a record set of xxx30.Z00 title
    records, can loop through it, checking whether
    each record actually contains the bib info, or
    links back to an xxx01.Z00 bib record.
  • If xxx30 record holds the title, use the
    xxx30.Z103 linking table to link to the xxx50
    library to get item and circulation status from
    the Z30 and Z36 tables.
  • If it is a linking record, link back to the
    xxx01.Z00 bib record to get author and title, and
    use the xxx01.Z103 linking table to get item and
    circulation status.

21
SQL to get list of titles for course
Either way, the link from the Z103 record to the
xxx50.Z30 uses the last nine characters of the
Z103_REC_KEY field to link to the first nine
characters of the Z30_REC_KEY field.
TCU01 if bib info in xxx01 library
TCU30
22
SQL to get list of titles for course
Linking Z103 and Z30 was inefficient the first
way I tried it, but the following syntax in the
SQL statement runs quickly (this is part of the
SQL JOIN clause)
"ON Z30_REC_KEY LIKE substr(Z103_REC_KEY, 6,
9)'' "
23
SQL to get list of titles for course
Code fragment showing retrieval of item and
circulation data when data is in the xxx30
library
sZ103Link "TCU30" sBibLink from
Z00_DOC_NUMBER sQ "SELECT Z103_REC_KEY,
Z30_COLLECTION, Z30_ITEM_STATUS, - Z30_CALL_NO,
Z30_DESCRIPTION, Z36_DUE_DATE, Z36_DUE_HOUR, -
Z30_BARCODE " sQ sQ "FROM TCU30.Z103 INNER
JOIN (TCU50.Z30 LEFT JOIN -TCU50.Z36 ON
Z36_REC_KEYZ30_REC_KEY) " sQ sQ "ON
Z30_REC_KEY LIKE substr(Z103_REC_KEY, 6, 9)''
" "WHERE (Z103_REC_KEY_1 '" sZ103Link "')
AND - (Z103_LKR_TYPE 'ADM') " sQ sQ "AND
(Z30_COLLECTION 'RESDK' OR Z30_COLLECTION
-'MUSAR' OR Z30_COLLECTION 'MUSDR')" set
RSBib connAleph.execute(sQ)
24
Get linking data from xxx30.Z00 SID pseudo-MARC
field
When the bib data is in the xxx01 library, must
first fetch the link to the xxx01.Z00 record from
the xxx30.Z00 record. Link is contained in
characters 7-15 of the SID pseudo-MARC field of
the Z00_DATA field.
0008FMT LBK0030LDR L-----nam----------a-----0045
CAT Laweberb00c20050224lTCU30h15010018C
NO LaMANA401530034SID LaTCU01-000440516-MANA
401530057NT1 LaOn reserve upstairs in the
Audiovisual Center!!!
Example of using MarcField to retrieve SID data
sBibLink Mid(MarcField("SID", "a", sData), 7, 9)
25
SQL to get list of titles for course
MarcField is a VBscript function that takes the
field tag (e.g., SID, 245), subfield (e.g.,
a) and contents of a Z00_DATA field as
parameters, and extracts the text of the Marc
field/subfield from the Z00_DATA. Complete code
is available in the poster presentation on using
3rd-party reporting tools with Aleph, available
at http//lib.tcu.edu/ELUNA/2007.htm
26
SQL to get list of titles for course
Code fragment showing link to xxx01 bib and xxx50
item data
sBibLink Mid(MarcField("SID", "a", sData), 7,
9) sZ103Link "TCU01" sBibLink sQ "SELECT
Z00_DATA, Z103_REC_KEY, Z30_COLLECTION, -
Z30_ITEM_STATUS, Z30_CALL_NO, Z30_DESCRIPTION,
Z36_DUE_DATE,- Z36_DUE_HOUR " sQ sQ "FROM
TCU01.Z00 INNER JOIN (TCU01.Z103 INNER JOIN -
(TCU50.Z30 LEFT JOIN TCU50.Z36 ON
Z36_REC_KEYZ30_REC_KEY) " sQ sQ "ON
Z30_REC_KEY LIKE substr(Z103_REC_KEY, 6, 9)''
) " sQ sQ "ON Z103_REC_KEY_1 '" sZ103Link
"' " sQ sQ "WHERE (Z00_DOC_NUMBER '"
sBibLink "') AND - (Z103_LKR_TYPE 'ADM') AND
" sQ sQ "(Z30_COLLECTION 'RESDK' OR
Z30_COLLECTION - 'MUSAR' OR Z30_COLLECTION
'MUSDR')"
27
MarcField function to fetch Marc data from
Z00_DATA fields
So, we finally have a list of bibs, and item and
circulation info attached to those titles. Now
use MARC_FIELD to extract 245 data from either
the xxx30.Z00 or xxx01.Z00 Z00_DATA field. Then
tack on the item and circulation status
28
Reserve page with both online and print titles
29
Thank you!
Presentation available at http//lib.tcu.edu/ELUN
A/2007.htm Boy with librarian graphic from
Clipart ETC (University of South Florida), used
under terms of their license. Kerry Bouchard,
k.bouchard_at_tcu.eduDirector of Library
SystemsMary Couts Burnett LibraryTexas
Christian University
About PowerShow.com