Feb 3, 2009
(0 votes)

Creating an Oracle view of articles to display EPiServer Content in Lotus Notes

We’re currently building out our corporate intranet pages in EPiServer, after many years of being housed in the Lotus Domino arena. While EPiServer does a great job of serving pages, we’ve got lots and lots of custom Domino apps that still need to be accessed that we probably won’t be converting completely to .NET any time soon. For this reason, we’ve got to make EPiServer a bit more flexible to work with the other systems.

One of these applications is a mail program that allows our Corporate Communications to select an article posted to our Intranet, pull some content from it, and mail a snapshot of the content to a particular group (managed in our Lotus Notes global directory). So in this instance, what I had to do was create a view that Lotus Notes could access in order to generate these messages.

The first step was determining which fields needed to be available to our Lotus Notes program. For this program, Lotus Notes needed to access the Page ID, Name, the SEO-friendly link name, the date the article was published, the type of page being published, and a portion of the body’s content.

After some investigation, I found all of these fields in a combination of the TBLTREE, TLPROPERTY and TBLPAGELANGUAGE tables in the EPiServer database. I then isolated the two parent ID’s that I wanted to grab children of. For our instance, we wanted our blog posts (ID = 566), and our news items (ID = 572). I then determined the Page Definition ID’s for the body (content) fields that we needed, decided upon a nesting level (which directly correlated with our URL or page structure syntax) for each of these (2 and 4) and decided to grab only articles posted in English.

As an aside, I’m not a big fan of hard coding values, but this needed to be a purely Oracle View, meaning the EPi libraries were not used at all in this determination. Furthermore, our page structure is always going to follow the same nesting syntax and the base parents 566 and 572 will not be going anywhere – so I think we were safe in this instance.

Without further ado, the code itself. .

    , lang.NAME
    , lang.URLSEGMENT
    , dbms_lob.SUBSTR(LONGSTRING, 4000, 1)
    TBLTREE tree
    , TBLPROPERTY prop
            tree.FKPARENTID = 572 -- 572 is news parent
                AND tree.NESTINGLEVEL = 2 -- level two using the syntax Month-Year/article-name
                AND (prop.FKPAGEDEFINITIONID = 244) --244 is main body field for news articles
                AND (lang.FKPAGEID = prop.FKPAGEID)
            tree.FKPARENTID = 566 -- 566 is blog parent
                AND tree.NESTINGLEVEL = 4 -- level four using the syntax Dates/Year/Month/article-name
                AND (prop.FKPAGEDEFINITIONID = 289) --289 is main body field for blog articles
                AND (lang.FKPAGEID = prop.FKPAGEID)
    AND (lang.FKLANGUAGEBRANCHID = 1) -- Only grab English titles
    AND (tree.FKCHILDID = lang.FKPAGEID)


So now we have a nice little view containing the ID, Name, URL Segment, Publish Date, Nesting Level and 4000 characters of the Body’s content. On the Lotus Notes side, a query is made against this view to determine whether or not the nesting level is either a 2 (indicating a news post) or a 4 (indicating a blog post), and then the ID is appended to the proper link format based on the type of post:  http://mydomain/public/PageTemplates/Blog/Pages/item.aspx?id= for blog items or http://mydomain/public/PageTemplates/News.aspx?id= for news items. That’s all there is to it.

Feb 03, 2009


Please login to comment.
Latest blogs
Fixing Optimizely Content Syncing/Caching Issues on the DXP pre CMS.Core 12.13.0

Hi all, With our recent deployments to the DXP for .NET 6 projects (one a new build and one an upgrade) our clients had raised issues where there...

Scott Reed | Mar 23, 2023

Handle hostnames, schedule jobs and role access when synchronizing content

The Environment Synchronizer module helps you to set your environment into a known state after synchronizing databases between environments. In thi...

Ove Lartelius | Mar 23, 2023 | Syndicated blog

4 tips and tricks for Hangfire on Optimizely CMS

Here are four useful tricks I always apply to any site where I use Hangfire (code included).

Stefan Holm Olsen | Mar 21, 2023 | Syndicated blog

The new LinkItem property in Optimizely CMS, and why it matters

In the past, we have used different tricks to achieve this. Now, the LinkItem property is finally built-in in Optimizely CMS 12!

Tomas Hensrud Gulla | Mar 20, 2023 | Syndicated blog