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
Caching & Rendering of metadata from the DAM

For the EPiServer.Cms.WelcomeIntegration.UI 1.2.0 release, we have facilitated the ability to cache and render metadata from assets within the DAM....

Matthew Slim | Jun 2, 2023

Conversion Rate Optimization: Maximizing ROI through Data-Driven Decisions

In today's digital landscape, businesses are constantly looking for ways to improve their online presence and drive more conversions. One effective...

JessWade | Jun 1, 2023

Enhance Your Writing with Suggestions

Are you tired of staring at a blank screen, struggling to find the right words? The Epicweb.Optimizely.AIAssistant Addon is here to revolutionize...

Luc Gosso (MVP) | May 31, 2023 | Syndicated blog

Content Graph - Letting GraphQL do all the hard work for you

Background As we have seen before, setting up Content Graph on the CMS side is pretty easy. However, when it comes to the “head” part of the setup,...

Kunal Shetye | May 26, 2023 | Syndicated blog