Pennsylvania BANNER Users Group 2006

Pennsylvania BANNER Users Group2006 Staff Directory on the Web

General Announcements: • Please turn off all cell phones/pagers • If you must leave the session early, please do so as discreetly as possible • Please avoid side conversations during the session • Questions will be answered at the end of the session Thank you for your cooperation

Contact Informatin • Calvin Deiterich • Harrisburg Area Community College • (717) 780-2542 • cedeiter@hacc.edu

Primary Goal • To provide a comprehensive staff directory with photographs.

Issues • What data to display? • Storage vs. Speed • What language to use? • What framework to use?

Accomplished So Far • Created a view to supply the data elements • Use of Oracle Intermedia 10g • Opted for speed • Language – Java • Framework – Struts

What Happened • Had base code written in Banner6/Oracle 9 • Tried to import images in Oracle 10 – errors • Oracle 10 does not seem to handle BLOBs • Needed to migrate everything to Ordimage

Data Supplied via View • Column Name ID Data Type Null • STAFF_PIDM 1 NUMBER (8) N • STAFF_ID 2 VARCHAR2 (9 Byte) Y • STAFF_NAME_LFMI 3 VARCHAR2 (120 Byte) Y • STAFF_NAME_FMIL 4 VARCHAR2 (120 Byte) Y • ROOM 5 VARCHAR2 (30 Byte) Y • CAMPUS 6 VARCHAR2 (20 Byte) Y • PHONE_NUMB 7 VARCHAR2 (8 Byte) Y • PHONE_EXT 8 VARCHAR2 (4 Byte) Y • ECLS_CODE 9 VARCHAR2 (2 Byte) Y • DIVISION 10 VARCHAR2 (30 Byte) Y • DICD_CODE 11 VARCHAR2 (3 Byte) Y • TITLE 12 VARCHAR2 (30 Byte) Y • PREF_EMAIL 13 VARCHAR2 (90 Byte) Y • HACC_EMAIL 14 VARCHAR2 (90 Byte) Y

Image_Xfr_Table • Column Name ID Pk Null? Data Type • ID 1 1 N VARCHAR2 (20 Byte) • IMG 2 Y ORDIMAGE • PIDM 4 Y NUMBER

GZTPOTO Table • Column Name ID Pk Null? Data Type • GZTPOTO_PIDM 1 1 N NUMBER • GZTPOTO_IMAGE 2 Y ORDIMAGE • GZTPOTO_ACTIVITY_DATE 3 N DATE • GZTPOTO_USER 4 N VARCHAR2 • GZTPOTO_THUMB 5 Y ORDIMAGE • GZTPOTO_STATUS 6 Y VARCHAR2

Processing Steps • Copy images to UNIX directory • Java code to read the file names into a table • Procedure to load images & set image properties • Procedure to resize images & store • fixedscale= 192 240 • fixedscale= 96 120 • Cleanup

The Java Code • CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HACCINST1."DirList“ AS • import java.io.*; • import java.sql.*; • public class DirList • { • public static void getList(String directory) • throws SQLException • { • File path = new File( directory ); • String[] list = path.list(); • String element; • for(int i = 0; i < list.length; i++) • { • element = list[i]; • #sql { INSERT INTO DIR_LIST (FILENAME) • VALUES (:element) }; • } • } • }

Loading the images • INSERT INTO IMAGE_XFR_TABLE VALUES( image_id, ORDSYS.ORDIMAGE.init('FILE','IMAGEDIR',filename), NULL); • SELECT img INTO myImage FROM IMAGE_XFR_TABL • WHERE ID = image_id FOR UPDATE; --- we import the image into the table using a buffer (ctx). • myImage.import(ctx); --- we do a setproperties on the object which sets the --- attributes of the object such as mimetype from the metadata in --- the image file. • myImage.setproperties(); • UPDATE IMAGE_XFR_TABLE • SET img=myImage WHERE ID=image_id;

The Move Code • PROCEDURE move_photo(pidm in number, initialImage in ORDSYS.ORDImage) • IS • myNewImage ORDSYS.ORDImage; • myThumbnail ORDSYS.ORDImage; • tempImage ORDSYS.ORDImage; • BEGIN • INSERT INTO gztpoto(gztpoto_pidm,gztpoto_image,gztpoto_activity_date, gztpoto_user, gztpoto_thumb, gztpoto_status) • VALUES(pidm, ORDSYS.ORDIMAGE.init(), trunc(sysdate), USER, ORDSYS.ORDIMAGE.init(),NULL); • SELECT gztpoto_image, gztpoto_thumb • INTO myNewImage, myThumbnail from gztpoto • WHERE gztpoto_pidm = pidm • FOR UPDATE; • tempImage := initialImage; • tempImage.processcopy('fileFormat=JFIF fixedscale= 192 240', myNewImage); • tempImage.processcopy('fileFormat=JFIF fixedscale= 96 120', myThumbnail); • UPDATE gztpoto SET gztpoto_image = myNewImage, • gztpoto_thumb = myThumbnail • WHERE gztpoto_pidm = pidm;

Needs to be done. • Figure out how to display the images • Finalize new code • Migrate JSP’s to Struts • Migrate Guard-Card database to Oracle • Create triggers to move information • Create Scripts to copy & resize photos • Archiving images(5 year plan)

Pennsylvania BANNER Users Group2006 Questions?

Pennsylvania BANNER Users Group2006 Have a great day!

Pennsylvania BANNER Users Group 2006