lekkimworld.com

I updated my LocationDocument LotusScript class so I could get/set information about the Sametime and Directory server to use. Quite handy when rolling out Sametime in the organization.

I accept that it is so, but I still find it strange what you can and cannot do in triggers. If anyone knows the answers to my problems with the below trigger code I would be more than happy to hear from them… 🙂

Well it all started with me having to do a simple trigger that calculates a simple search rank when a row is updated. Instead of having this is the Java application I thought this was the perfect job for a trigger. A trigger is code you can have executed at specific times before or after a row of data is inserted. You have much more options than that but you get the point.

The trigger should run after update and end up updating the search_rank column with a calculated integer value. The search rank is quite simple – 1 point for a company name, 3 points for an address, 3 points for an e-mail address etc. After adding the different scores the final store should be written to a column. I immediately thought that a trigger should do the job so I wrote one (not as easy as it sounds since there were some trial and error included).

CREATE TRIGGER CALC_SEARCHRANK
AFTER UPDATE ON COMPANY
REFERENCING NEW AS N OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
   -- declare temp variable and reset out variable
   declare calc_rank smallint default 0;
   declare temp_rank smallint default 0;
   declare temp_string varchar(50) default null;

   -- get value for logo
   declare cur_logo cursor for select name_1 from company_logo cl where cl.company_id=o.company_id;
   open cur_logo;
   fetch cur_logo into temp_rank;
   close cur_logo;
   set calc_rank = calc_rank + temp_rank;

   -- get value for products
   select count(*) into temp_rank from company c where c.company_id in (select company_id from company_logo cl where cl.company_id=o.company_id);
   set calc_rank = calc_rank + temp_rank;

   -- get value for company name
   select name_1 into temp_string from company where company_id=o.company_id;
   if temp_string != '' then
      set calc_rank = calc_rank + 1;
   end if;

   -- get value for zip/city
   select zipcode into temp_string from company where company_id=o.company_id;
   if temp_string != '' then
      set calc_rank = calc_rank + 2;
   end if;

   -- get value for address1
   select address_1 into temp_string from company where company_id=o.company_id;
   if temp_string != '' then
      set calc_rank = calc_rank + 2;
   end if;

   -- get value for phone
   select phone into temp_string from company where company_id=o.company_id;
   if temp_string != '' then
      set calc_rank = calc_rank + 3;
   end if;

   -- get value for fax
   select fax into temp_string from company where company_id=o.company_id;
   if temp_string != '' then
      set calc_rank = calc_rank + 2;
   end if;

   -- get value for e-mail
   select email into temp_string from company where company_id=o.company_id;
   if temp_string != '' then
      set calc_rank = calc_rank + 3;
   end if;

   -- get value for web
   select url into temp_string from company where company_id=o.company_id;
   if temp_string != '' then
      set calc_rank = calc_rank + 4;
   end if;

   -- update company table
   update company set search_rank=calc_rank where company_id=o.company_id;
end
@

But that wasn’t good enough. I kept getting errors when trying to add the trigger to the database:

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "for" was found following "lare cur_logo
cursor".  Expected tokens may include:
"".  LINE NUMBER=12.  SQLSTATE=42601

After a LOT of trying different stuff out and searching on Google I have found out there is something called dynamic statements and what have you. Apparently you cannot do something like the above – I’ll have to look more deeply into why not. I would like to understand.

Since I hadn’t solved the problem I set out to solve yet I turned to trying my SQL out as a stored procedure. This worked better. It only took me a couple of minutes to convert the SQL into a procedure and load the procedure into my database.

CREATE PROCEDURE CALCSEARCHRANK(IN id INT)
LANGUAGE SQL
DYNAMIC RESULT SETS 0
MODIFIES SQL DATA
BEGIN
   -- declare temp variable and reset out variable
   declare calc_rank smallint default 0;
   declare temp_rank smallint default 0;
   declare temp_string varchar(50) default null;

   -- get value for logo
   select count(*) into temp_rank from company_logo cl where cl.company_id=id;
   set calc_rank = calc_rank + temp_rank;

   ... (same as above)
   ... (same as above)
   ... (same as above)

   -- update company table
   update company set search_rank=calc_rank where company_id=id;
END @

The stored procedure worked but I really would like to have the code called automatically hence the need for the trigger. After a couple of hours trying to call my procedure from my trigger I finally gave up. Apparently calling stored procedures from triggers is only supported from DB2 v.8.2 and up and we’re running DB2 v.7.2 due to some legacy stuff. Bummer!

So now I am basically back where I started and I have implemented the updating of the search rank in the application instead in the database. The only thing left to do is the updating of the existing records but that should be simple (famous last words)) using the stored procedure I ended up writing. The only issue there is installing the stored procedure in DB2 on the Linux production box.

I am having an issue with this on Linux where DB2 keeps complaining about not be able to load the db2udp library but I might have found the error. Apparently you need to install the Application Development package in order for you to install stored procedures.

This is a logical prerequisite I think… 🙂

IBM has released a fixpack for Lotus Domino 6.5.4.

• List of fixes
• Announcement
• Download

We are having some problems with a JBoss 3.2.6 instance running out of memory (java.lang.OutOfMemoryError) with an error message about not being able to create native threads. First of I though it was memory for the instance so I added a bunch of more memory that that didn’t seem to fix the problem. I poked a little around on Google and found JBoss issue 1369 which is basically what we are experiencing. For the moment I have tried disabling the UIL2 (Universal Invocation Layer 2) and just leaving the JVM Invocation Layer (jvm-il) to see if that solves the problem. Only using the jvm-il should solve the need to using separate threads etc.

We are crossing our fingers.

Quoted from “New features in Lotus Domino 7.0 Beta 4” on developerWorks:

For a web application we are hosting for some customers we use the Ultimate Dropdown Menu which is a CSS formatted menu product. The UDM is a third-party product – not something we did at my company.

The primary reason that we are using it is that it allows the developer to set a shortcut key so the users can access and use the menu using only the keyboard. This is a must for a web application that users use each day as their primary tool for data entry. Another reason is that the license is cheap! At 70 USD per server it is very competitive – you can’t even code a decent stylesheet for that amount of money.

The menu had one short coming though. The menu is based on unordered HTML lists (<ul>) which may be nested to desired level of nesting and we needed to add menu items to the menu dynamically using the DOM (Document Object Model) through JavaScript. However the menu API was missing a way to have it rebuild its internal state and make it update dynamically. This short coming has been solved in the new 4.43 release.

The Load XML extension is only possible because of a new public method called um.refresh, which re-initialises the navigation tree as though the page had been reloaded – many thanks to Mikkel Heisterberg for suggesting this.

Well it would but first things first… VERY nice. I really like the DominoWiki – the wiki is really easy to install, configure and work with. I think I’ll introduce the other guys at the office to the concept.

After proper credit has been given let me get back to the initial focus of the post. The wiki should mainly be used to share code examples within my team of developers but I like code to look like code which it doesn’t when you just paste it into DominoWiki. My first and obvious problem was that all datatypes showed up as WikiWords. This is understandable due to their CamelCase nature. The fix was easy though – enclose all code in the <nowiki> tag which basically tells DominoWiki not to parse the text. Nice. This a new problem emerged – the code was displayed using the same font as the rest of the text. This made it hard to read and see what was introduction and what was code. Hmmm… Using the <code> HTML tag inside the <nowiki> tag helped me there. I now had nice Courier formatted code. But wait a minute… Didn’t Julian Robichaux do a script library for converting LotusScript code into HTML so it would display on the web like it does in the Domino Designer. Well I believe he did. 🙂 I went and fetched the code and hacked a little at the WikiPage.class script library. Now I can embed the <lotusscript> tag inside a <nowiki> tag and have nice formatted LotusScript in my wiki. <nowiki> <lotusscript> Some LotusScript code... </lotusscript> </nowiki> Really nice if I have to say so myself.

(Click image to open a bigger version)

Below is the patched version of the wikiFormat method from the WikiPage.class script library (remember to also get the ls2html script library from http://www.nsftools.com (I had to make small tweaks to the library to make it compile under Notes 6.5.4). The code has been formatted with ls2html – what else… 🙂

.lotusscript { font-family: courier; font-size: 9pt; color: black; }
.ls-comment { color: green; }
.ls-quote { color: black; }
.ls-datatype { color: black; }
.ls-operator { color: blue; }
.ls-keyword { color: blue; }
.ls-statement { color: blue; }
.ls-function { color: blue; }
.ls-class { color: black; }
.ls-constant { color: purple; }

Public Function wikiFormat (txt As String) As String
  '** call all of the wiki formatting functions, in a somewhat specific
  '** order (we also need to ignore anything in a <nowiki></nowiki>
  '** block, so you'll see the logic for that in here as well)
  Dim s1 As String, s2 As String, s3 As String, s4 As String
  Dim a1 As Variant
  Dim i As Integer
  Dim pos As Long
  Dim style As StyleDef

  Const NOWIKI_START = "&amp;lt;nowiki&amp;gt;"
  Const NOWIKI_END = "&amp;lt;/nowiki&amp;gt;"
  Const LS_START = "&amp;lt;lotusscript&amp;gt;"
  Const LS_END = "&amp;lt;/lotusscript&amp;gt;"

  'create default style as I like it (using courier for code)
  Call GetDefaultLsStyleDef(style)
  style.script = "font-family: courier,sans-serif; font-size: 9pt; color: black;"

  '** convert rn linefeeds to just n
  s1 = parseLinefeeds(txt)

  '** remove all user-entered HTML from the page
  s1 = removeHTML(s1)

  '** ignore things that are within <nowiki></nowiki> sections
  a1 = Split(s1, NOWIKI_START, -1, 5)
  For i = 0 To Ubound(a1)
     If (i = 0) Then
        '** text before the first <nowiki> tag (if any), so we need to
        '** parse the whole block of text
        s1 = a1(i)
        s2 = ""
     Else
        '** check to see if there's a </nowiki> tag in here anywhere;
        '** if there is, we only want to parse everything after it, but if
        '** there's not we have to assume that all the text in this block
        '** is inside of the <nowiki> block, so we'll ignore everything
        '** in that case.
        pos = Instr(1, a1(i), NOWIKI_END, 5)
        If (pos > 0) Then
           s1 = Mid(a1(i), pos + Len(NOWIKI_END))
           s2 = Left(a1(i), pos - 1)
        Else
           s1 = ""
           s2 = a1(i)
        End If
     End If

     s1 = parseWikiWords(s1)
     s1 = parseLists(s1)
     s1 = parseRules(s1)

     '** it's possible that the user would expect us to only look for
     '** bold, italic, and header matches along a single line. If that's
     '** the case, you can adjust the matchCondition filter.
     matchCondition = "*"
     matchCriteria = True
     'matchCondition = "*[" &amp; chr(10) &amp; "]*" ' on a single line only
     'matchCriteria = False

     s1 = parseBold(s1)
     s1 = parseItalics(s1)
     s1 = parseHeaders(s1)
     s1 = parseMonospace(s1)

     '** parse anything inside of single or double brackets ( [ ] or [[ ]] )
     s1 = parseBrackets(s1)

     s3 = s3 &amp; s2 &amp; s1
  Next

  a1 = Split(s3, LS_START, -1, 5)
  For i = 0 To Ubound(a1)
     If (i = 0) Then
        '** text before the first <lotusscript> tag - just add it to the 
        '** result string
        s4 = s4 &amp; a1(i)
     Else
        '** check to see if there's a </lotusscript> tag in here anywhere;
        '** if there is, we only want to get the text before it, but if
        '** there's not we have to assume that all the text in this block
        '** is inside of the <lotusscript> block, so we'll get all remaining 
        '** text in that case.
        pos = Instr(1, a1(i), LS_END, 5)
        If (pos > 0) Then
           '** there is a </lotusscript> tag
           s1 = Mid(a1(i), pos + Len(LS_END)) 'text after the ending tag
           s2 = Left(a1(i), pos - 1) 'text before the ending tag
        Else
           '** no stop tag - get rest of text
           s1 = ""
           s2 = a1(i)
        End If

        '** convert s2 using lotusscript2html
        s2 = ConvertStringEx(s2, style, False)

        '** add to s4
        s4 = s4 &amp; s2 &amp; s1
     End If
  Next
  s3 = s4

  '** a few additional fixes
  s3 = fixAmp(s3)
  s3 = fixRelativeLinks(s3)

  '** put back any "safe" HTML that was fixed
  s3 = allowSafeHTML(s3)

  '** convert linefeeds to <p> or <br>
  s3 = parseParas(s3)

  wikiFormat = s3
End Function

Very, very nice… This article on xml.com Joe Gregorio shows how you could encrypt data in RSS feeds by combining Blowfish, Greasemonkey and RSS.

When combining OOP with the basis design patterns from the GoF book your code really becomes better. It should be obvious but sometimes it takes a success to fully realize and appreciate it.

I just spent some time extending a framework I am developing in LotusScript with some new functionality. Because I used the Factory and Template Method pattern I was able to add the functionality in mere 20 minutes. My initial guess was 2 hours!!

Quite a difference.

Now that I’m ranting about OOP in Notes I am really surprised how difficult it is to get Notes developers to adopt OOP in LotusScript. Maybe because Lotus doesn’t push it harder by providing a decent IDE supporting the task. Maybe it is because everything else in Lotus Notes is so easy that the learning curve is too steep. OOP is hard to learn and use properly, but once you get there you reap the reward. Big time.