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$ ELSE 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 END IF END IF 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" END SELECT END IF pbcount = pbcount + 1 WEND END IF