Skip to main content
 
developerWorks
AIX and UNIX
Information Mgmt
Lotus
New to Lotus
Products
How to buy
Downloads
Live demos
Technical library
Training
Support
Forums & community
Events
Rational
Tivoli
WebSphere
Java™ technology
Linux
Open source
SOA and Web services
Web development
XML
My developerWorks
About dW
Submit content
Feedback



developerWorks  >  Lotus  >  Forums & community  >  Enterprise Integration Forum

Enterprise Integration Forum

developerWorks

  

Sign in to participate PreviousPrevious NextNext


Steve Sherwood 21.Aug.16 11:37 PM a Web browser
Connector for Text LEI - 8.0 Windows


We need to export Sametime Log data from Domino 8.5.3 to MS SQL Server version 2012 using LEI 8.5.3. Each document has a rich text field which contains formatted text and often inline images.
It is essential that the format is retained and images remain in their original position. I have used a third party product to export the Notes rich text data to html,
then replaced the <img src> in the html ouptut with the file contents encoded as a Base64 string.

LEI Connections are defined for Text and OLE DB. The LEI Direct Transfer Activity works fine for documents containing no or very small images, but with large images
(which can be several hundred Kb in size) an error is reported:
22/08/2016 08:46:24 AM WARNING: Unable to set ChatStart for [Input Record 78], field exceeds record length (32766), Connector 'Sametime Text', Method -Fetch-

This error occurs for every column in every document After the one with the large image(s), suggesting that the very long Base64 string has been 'wrapped' to a new line within the Activity.
The source data is in the format below, using "^" as the field separator, with the Body html exported as the last column. The Base64-encoded string can run for pages if the image is large...
There are Definitely no line feeds/carriage returns in the source data, although I've added some below so that it doesn't all disappear off the right of the screen.

0003d552-fd5e-4dc7-9504-33814898354e^23/04/2014 14:35:26^23/04/2014 14:42:53^Ann Other;John Person^2^2^18^<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html lang="en">
<!-- Midas Rich Text LSX Version 4.31, 08/22/2016 --><head><title></title><meta http-equiv="Content-Style-Type" content="text/css" /><style type="text/css">div.pabid2 {margin-left: 2.40in; text-indent: -2.40in; }
</style></head><body text='#000000' bgcolor='#ffffff'><div class="pabid2"><span style="font-size: 9pt; color: rgb(128,128,128); ">02:35:26 pm:&nbsp;</span><span style="font-size: 9pt; ">
&nbsp;&nbsp;&nbsp;Ann Other:</span><span style="font-size: 9pt; color: rgb(128,128,128); "><i>&nbsp;&nbsp;&nbsp;(Joined chat)</i></span></div><div class="pabid2">
<span style="font-size: 9pt; color: rgb(128,128,128); ">02:35:26 pm:&nbsp;</span><span style="font-size: 9pt; color: rgb(255,0,0); ">&nbsp;&nbsp;&nbsp;John Person:
</span><span style="font-size: 9pt; color: rgb(128,128,128); "><i>&nbsp;&nbsp;&nbsp;(Joined chat) </i></span></div><div class="pabid2"><span style="font-size: 9pt; color: rgb(128,128,128); ">
02:35:26 pm:&nbsp;</span><span style="font-size: 9pt; ">&nbsp;&nbsp;&nbsp;Ann Other:&nbsp;&nbsp;&nbsp; </span><span style="font-size: 9pt; font-family: Segoe UI;
">yep we can do that, I'll just confirm with Sue tomorrow - we gave them a week&nbsp;</span><img src="data:image/gif;base64,
R0lGODlhEgASAOZ3AP/mI//mIv/jH//kIPC2AP/kH/fLAMrKyoB+efzZEffMAPvXDf/iHWhfTPvYD//kIeyrAH99ePC3AP7gG/bJAO+xAKF+JuecAPrTCP7hHemiAIlsMPvXDvK9APjMAPfKAO+yAP3dF/3fGX9+eIlpL9iUBvrUCu3HC4VzMLd
/F/bIAOe1Bf7iHvbKAPTDAGhgTNqRBcSeF4BkMN2TBd6WBvK/AINrMI53L/HKC9+ZBuSmBYhpMOy+Buq4BfvZEGlhTPjOAv7hHP3eGI97L7R9GGpiTL6eGPzbE/rSB7iEF/zbFO3BBpxxJvPCAP7jH//iHuyqAO2uAI97MPPBAOG
jBbaDGOq+BmhhTP7iHdiSBsSjF+vABvO/AIpvMIFpMI1zMIloMI11MLyYGI56MP/nI/C4APTCAKeOJvvaEf7gGv3cFv7fGf7fGv/lIaSkpISEhMvLy25ubpiYmNbW1v/lIv//mTMzmf///
wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACH/C05FVFNDQVBFMi4wAwEAAAAh+QQFZAB3ACwAAAAAEgASAAAH/4B3goJwbm9xcW9ucIONd3NyV2NGSzxiYS9yc44HCCgnCwkJHEgfKzYIB4NzEWcLamwMDBMhDh4dF
iObd3JSC2t2dgIDA8EOBhJdcndwRTghdnR0Aw9tD3R2GGY6DYVDCxMCdXZtbdh1GT4UFRuGWgkM4tEB5wVHBgRJh1vwA+MBAOopUUCgBCIrHIIMkAaQTEAnaFqAyJIoBgYhdqoBBACgjR0TLiCkUHTjgwNh5gIEaIPFjoIyGkgstHDw8kMB
QqwEGEHCBcIM7rxtLBwMlgwRTUiHIBzLJHESwQmKLCgAIKTSRAucAkwq47nbxQqUCAAAgIGmDISOUIUoMNVRBy0CCyo4EmR4MKHUq0yFEgACH5BAUPAHcALAQAAwAJAAMAAAcUgHd2dneFg4JPAoV3Aml2T4uRIoEAIfkEBQ8AdwAsBAADAAkAA
wAABxWAd2psd4UhDndrdnaFd4sOjI2NdoEAIfkEBQ8AdwAsBAADAAkAAwAABxSAd3Z2d4WDgk8ChXcCaXZPi5EigQA7">
</div><div class="pabid2"><span style="font-size: 9pt; color: rgb(128,128,128); ">02:35:38 pm:&nbsp;</span><span style="font-size: 9pt; color: rgb(255,0,0); ">
&nbsp;&nbsp;&nbsp;John Person:&nbsp;&nbsp;&nbsp;</span><span style="font-size: 9pt; font-family: Segoe UI; ">ok</span></div><div class="pabid2"><span style="font-size: 9pt; color: rgb(128,128,128); ">
02:42:53 pm:&nbsp;</span><span style="font-size: 9pt; color: rgb(255,0,0); ">&nbsp;&nbsp;&nbsp;John Person:</span><span style="font-size: 9pt; color: rgb(128,128,128); "><i>&nbsp;&nbsp;&nbsp;(Left chat)
</i></span></div><div class="pabid2"><span style="font-size: 9pt; color: rgb(128,128,128); ">02:42:53 pm:&nbsp;</span><span style="font-size: 9pt; ">&nbsp;&nbsp;&nbsp;Ann Other:</span>
<span style="font-size: 9pt; color: rgb(128,128,128); "><i>&nbsp;&nbsp;&nbsp;(Left chat)</i></span></div><div class="pabid2"><span style="font-size: 9pt; color: rgb(128,128,128); "><i></i></span></div></body></html>^

The Field Specifications in the Text Connection document are defined as follows:
ChatId: TYPE TEXT UNTIL "^"
ChatStart: TYPE DATETIME FORMAT "DD/MM/YYYY hh:mm:ss" UNTIL "^"
ChatEnd: TYPE DATETIME FORMAT "DD/MM/YYYY hh:mm:ss" UNTIL "^"
ListParticipants: TYPE TEXT UNTIL "^"
PartyCount: TYPE NUMBER UNTIL "^"
MessageCount: TYPE NUMBER UNTIL "^"
WordCount: TYPE NUMBER UNTIL "^"
BodyHTMLString: TYPE TEXT UNTIL "^"

The corresponding columns in the SQL Server table are:
ChatId: nvarchar(128), not null
ChatStart: datetime, not null
ChatEnd: datetime, not null
ListParticipants: nvarchar(max), null
PartyCount: int, not null
MessageCount: int, not null
WordCount: int, not null
BodyHTMLString: nvarchar(max), null

Questions to the forum are:
1. Can the BodyHTMLString value be defined as something other than Text in the source data in order to overcome what seems to be a character limit?
2. Is nvarchar(max) a data type that LEI recognises? In the Lotus Connectors and Connectivity Guide, it mentions NVARCHAR Text (variable length, bound <= 8000) as a SQL Server data type. What does the 8000 refer to?
3. How has anybody else exported attachments to SQL Server using LEI? Maybe there is a better way of storing the attachments?

Your suggestions are appreciated.
Thanks,
Steve






field exceeds record length (32766)... (Steve Sherwood 21.Aug.16)
. . RE: field exceeds record length (32... (Steve Sherwood 21.Sep.16)





  Document options
Print this pagePrint this page

 Search this forum

  Forum views and search
Date (threaded)
Date (flat)
With excerpt
Author
Category
Platform
Release
Advanced search

 Sign In or Register
Sign in
Forgot your password?
Forgot your user name?
Create new registration

 RSS feedsRSS
All forum posts RSS
All main topics RSS
More Lotus RSS feeds

Resources

 Resources
Forum use and etiquette
Native Notes Access
Web site Feedback

Lotus Support

 Lotus Support
IBM Support Portal - Lotus software
Lotus Support documents
Lotus support by product
Lotus support downloads
Lotus support RSS feeds

Wikis

 Wikis
IBM Composite Applications
IBM Mashup Center
IBM Connections
IBM Connections Cloud Developers
IBM Docs
IBM Forms
IBM Mobile Connect
IBM Sametime
IBM SmartCloud for Social Business
IBM Web Experience Factory
Lotus Domino
Lotus Domino Designer
Lotus Expeditor
Lotus Foundations
Lotus iNotes
Lotus Instructor Community Courseware
Lotus Notes
Lotus Notes & Domino Application Development
Lotus Notes Traveler
Lotus Protector
Lotus Quickr
Lotus Symphony
IBM Web Content Manager
WebSphere Portal

Lotus Forums


 Lotus Forums
Notes/Domino 9.0
Notes/Domino 8.5 + Traveler
Notes/Domino XPages development forum
Notes/Domino 8
Notes/Domino 6 and 7
IBM Connections
IBM Mobile Connect
IBM Sametime
IBM SmartCloud Notes
Lotus Enterprise Integration
Lotus Protector
Lotus Quickr
Lotus SmartSuite