Parse a Lotus comment field into multiple database fields

This tip describes how to parse a Lotus comment field into multiple database fields.

I recently had a request to convert a comments field on my Domino form into multiple comment fields in DB2. The requirement was that the comment field had to be placed in the DB2 fields; linecomment1, linecomment2, linecomment3, linecomment4 and linecomment5.

Each of the DB2 fields could only be 50 characters long, but we had to preserve the users new lines AND could not split words. What's more, passing the characters 13 and 10 as carriage return did not work because the DB2 tables were on the AS/400. Here is the code I used to split the comment line at either the word nearest 50 characters OR at the carriage return if the user had a carriage return in the field.

crlf$ = Chr(13)& Chr(10) ' find 
user carriage returns
bigstr$ = thisdoc.Comments(0) 
'thisdoc is a NoetsDocument object
IF Len(bigstr$) > 0 THEN
 pbcount = 1  ' I need to know when 
to stop
 WHILE Len(bigstr$) >  0 AND 
pbcount < 6)  ' if i have characters, 
and still need to fill lines.
  findcrlf = Instr(bigstr$, crlf$)
  IF findcrlf <= 50 and 
findcrlf <> 0 THEN
   tempstr$ = StrLeft(bigstr$, crlf$)
   newstr$ = StrRight(bigstr$, crlf$)
   bigstr$ = newstr$
  ELSE  ' no carriage return found
   IF Len(bigstr$) <= 50 THEN
    tempstr$ = bigstr$
    firsttempstr$ = Left(bigstr$, 51) 
' go 1 beyond my limit so I
 can back up for space
    secondtempstr$ = StrLeftBack
(firsttempstr$, " ") ' make sure I 
do not split a word
    thirdtempstr$ = StrRightBack
(firsttempstr$, " ") ' if I do split
 a word, save leftovers
    tempstr$ = secondtempstr$
    righlen% = Len(bigstr$) - 51
    IF rightlen% 7lt; 0 THEN
     rightlen% = 0
    END IF
    newstr$ = Right(bigstr$, rightlen%)
    bigstr$ = thirdtempstr$ & newstr$ '
 put leftovers back on front
  IF findcrlf <> 1 THEN
   SELECT CASE pbcount ' this 
is whatever you want to do with 
each new string.
   CASE 1 : ' process the 1st parsed
 line, set my first db2 field in my case. 
    set field = fields.Apppend
("linecomment1", LCTYPE_TEXT)
    field.value = Pad(1, tempstr$, 50)
   CASE 2 : 'process the 2nd parsed line
   CASE 3 : 'process the 3rd parsed line
   CASE 4 : 'process the 4th parsed line
   CASE 5 : 'process the 5th parsed line
   CASE ELSE : Print "Comment is 
too long for the available DB2 fields"
  pbcount = pbcount + 1

Dig Deeper on LotusScript

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.




  • iSeries tutorials's tutorials provide in-depth information on the iSeries. Our iSeries tutorials address areas you need to know about...

  • V6R1 upgrade planning checklist

    When upgrading to V6R1, make sure your software will be supported, your programs will function and the correct PTFs have been ...

  • Connecting multiple iSeries systems through DDM

    Working with databases over multiple iSeries systems can be simple when remotely connecting logical partitions with distributed ...