Blackboard

Owned by Andrew Petro
Last updated: Sept 15, 2006 by Duncan AppelbeVersion comment

California Polytechnic, UNICON/IBS, University of Kansas Medical Center and Roanoke College also have a solution in the uPortal CVS.

Following some recent posts on the mailing list about accessing data from the Blackboard database and pulling it into uportal, here is some of the SQL that we use at Liverpool to extract data (please note that this is for the 6.3 version of the BB DB), I hope that it provides some useful pointers. - FYI we run our BB DB is oracle based, the portal accesses the DB via a read-only account with access to specific tables, if you have any questions/comments please email D.E.Appelbe@liverpool.ac.uk. I have attached example files to this post, but will expand on the SQL in the table below. The files GetBasicData and GetFullData can then be plugged straight into a channel or portlet, I have not included our DBPool Object for our DB, but it should be clear what you need to replace.

Explanation

SQL (displayed as prepared statements)

Get the users last login time to BB

SELECT MAX(TIMESTAMP) FROM BB_BB60.ACTIVITY_ACCUMULATOR WHERE USER_PK1 = (SELECT PK1 FROM BB_BB60.USERS WHERE USER_ID = ? )

In our version of BB (6.3) the users can specify a list of courses that they wish to have data displayed about on the front page (modules). This information is stored as a clob in the BB DB. We use this query to extract this data to determine which courses to run the subsequent queries over. This data is then stored in a local table in the portal DB.

select extra_info from bb_bb60.portal_extra_info where portal_viewer_pk1 = ( select pk1 from bb_bb60.portal_viewer where users_pk1 =  (select pk1 from bb_bb60.users where user_id = ?) ) and module_pk1 = 4

Get the Number of announcements posted in the last seven days - You need to insert the list of courses to parse

SELECT COUNT( * ) FROM (SELECT NVL(END_DATE, START_DATE) A_DATE FROM BB_BB60.ANNOUNCEMENTS WHERE CRSMAIN_PK1  IN ( ? ) AND PERMANENT_IND = 'N' ) WHERE A_DATE > SYSDATE - 7