I wrote a number of articles in THE VIEW some time back on writing custom Sametime blackboxes (see the March/April and May/June 2008 issues). If you implemented the Sametime blackbox that read data from Lotus Connections (like it’s done on bleedyellow.com) and you have upgraded (or are planning to upgrade) to Lotus Connections 2.0 this post is for you.
The database schema for Lotus Connections 2.0 has changed slightly in comparison with Lotus Connections 1.0.x so if you’re using a custom Sametime blackbox to read data from Lotus Connections it needs to be changed. The only thing that needs to change is the SQL since the key column in the EMPINST.PHOTO table has been renamed from PROF_UID to PROF_KEY (hightlighted in the SQL below).
SELECT EMPL.PROF_UID UID, PROF_DISPLAY_NAME DSP_NAME, PROF_MAIL_LOWER EMAIL, PROF_MOBILE MOBILE, PROF_PAGER PAGER, PROF_TITLE TITLE, PROF_TIMEZONE TIMEZONE, PROF_TELEPHONE_NUMBER PHONE, PROF_BLOG_URL BLOG_URL, EMPL.PROF_DEPARTMENT_NUMBER DEPT_NO, DEPT.PROF_DEPARTMENT_TITLE DEPT_NAME, EMPL.PROF_ORGANIZATION_IDENTIFIER ORG_IDENT, ORG.PROF_ORGANIZATION_TITLE ORG_NAME, EMPL.PROF_ISO_COUNTRY_CODE CTRY_ISOCODE, CTRY.PROF_COUNTRY_DESC CTRY_NAME, P.PROF_IMAGE PHOTO_BYTES, P.PROF_FILE_TYPE PHOTO_MIMETYPE FROM EMPINST.EMPLOYEE EMPL LEFT OUTER JOIN EMPINST.PHOTO P ON EMPL.PROF_KEY=P.PROF_KEY LEFT OUTER JOIN EMPINST.DEPARTMENT DEPT ON EMPL.PROF_DEPARTMENT_NUMBER=DEPT.PROF_DEPARTMENT_CODE LEFT OUTER JOIN EMPINST.COUNTRY CTRY ON EMPL.PROF_ISO_COUNTRY_CODE=CTRY.PROF_ISO_COUNTRY_CODE LEFT OUTER JOIN EMPINST.ORGANIZATION ORG ON EMPL.PROF_ORGANIZATION_IDENTIFIER=ORG.PROF_ORGANIZATION_CODE WHERE EMPL.PROF_MAIL_LOWER=?