WEBCAST SCHEDULE

1
WEBCAST SCHEDULE

• Todays event will run one-hour long. Here are
  the expected times for each segment of the
  Webcast
• 00 05 Moderator introduces the speaker and
  discusses the details of the Webcast.
• 05- 25 Speaker delivers a PowerPoint
  presentation on the Webcast topic.
• 25- 35 Moderator and speaker engage in a brief
  QA on the topic.
• 35- 60 The speaker responds to questions
  submitted by the audience.
• You can submit questions to the speaker at any
  time during the event. Just click on the Ask a
  Question button in the lower left corner of your
  screen.

2
TECHNICAL FAQs

• Here are answers to the most common technical
  problems users encounter during a Webcast
•  
• Q Why cant I hear the audio part of the
  webcast?
• A Try increasing the volume on your computer.
• Q I just entered the webcast and do not see the
  slide that the speaker is referring to. What
  should I do?
• A The slides are constantly being pushed to
  your screen. You should refresh (hit F5) to view
  the latest slide.
•  
• Q What time zone do the webcasts take place?
• A The TechTarget webcasts all occur on Eastern
  Daylight Saving Time (UTC/GMT - 4 hours). After
  Oct. 27, 2002, the Webcasts will occur on Eastern
  Standard Time (UTC/GMT 5 hours).
• If your question is still not answered, please
  click the Ask a Question button in the lower
  left corner of your screen and submit your
  problem. A technical support person will respond
  immediately.
• You can also visit the Broadcast Help page for
  more information or to test your browser
  compatibility. Click here http//help.yahoo.com/
  help/bcst/
•  

3
Enterprise Integration with LotusScript -- Using
the LC LSX with Precision and Performance
Andre Guirard, IBM Software Group Enterprise
Integration Team http//www.lotus.com/ei
4
Agenda

• Comparing LotusScript EI Tools
• Lotus Connector Architecture
• Using the LC LSX
• High Performance Coding
• Metaconnectors
• Character Set Conversion
• Advanced Code Example
• Demonstrating the above.
• Q A

5
Comparing LotusScript Database Tools

• LSDO
• ODBC / SQL queries
• Limited Capabilities
• Comes free with Notes/Domino
• LSX for Lotus Connectors
• Strategic - used in Domino, DECS, LEI, ESB
• Better performance using native database APIs
• 10 Connectors by Lotus
• 3rd-party connectors...
• Comes free with Notes 6/Domino
• LSXLEI - no longer used
• Use LSX LC for Scripted Activities
• Migration tool available

6
LC LSX - Acronyms Explained

• LC Lotus Connectors
• C API for accessing relational or
  relational-like data from a multitude of sources.
• LSX LotusScript eXtension
• A library to let LotusScript call lower-level
  functions.

7
Enterprise Integration Architecture
Domino Clients
Domino Applications Agents Scripts,
Servlets... Forms, Views, Pages, Frames
LC LSX
LEI
DECS
DCRs
ESB
Base
Premium
Lotus Connectors
Unstructured Data
Relational Data
ERP Systems
Domino Data
8
LSX Classes

• LCSession - Connector environment control
• LCConnection - represents an instance of LC
  Connector
• Connect method activates connections
• Execute, Select, Call, Fetch, Insert, Update,
  Remove, ... methods perform standard database
  operations.
• LCFieldlist - represents one or more "rows" of
  data.
• Use to write and read tables and result sets.
• Read and create metadata, indexes.
• LCField - represents a single field in an
  LCFieldList.
• An LCField can be a member of more than one field
  list.
• LCStream
• text and binary data, special data types,
  character set conversions.
• LCCurrency, LCNumeric, LCDatetime
• Higher precision than the built-in LotusScript
  types.

1.00 US 1,613,500.00 Turkey Liras(as of
2/26/03)
9
Earlier attempt to publish on this subject
Field Stream Magazine Editorial Office 108 10th
Ave. New York NY 10036
Dear Sir, We regret that the enclosed material,
Using Fields and Streams for High Performance in
the LC LSX does not meet our current needs.
P.S. What planet are you from?
10
Establishing a Connection

• Uselsx "lsxlc" ' lsxlei is no longer used --
  migration tool available.
• Dim lcSes As New LCSession("sessionNameForLog") '
  for scripted activities.
• Dim con As New LCConnection("connectionDocName")
• ' name of LEI Admin connection document.or...

• Dim lcSes As New LCSession ' (optional) all
  other cases.
• Dim con As New LCConnection("connectorName")
• ' e.g. db2 or oledb
• con.Database ...
• con.Username ...
• con.Password ...
• con.Metadata ... ' table name goes here.
• con.Connect

11
Selecting and Fetching All Records in a Table

• Dim flds As New LCFieldList
• con.Fieldnames "Empno,Name" ' which fields we
  want (default)
• Call con.Select(Nothing, 1, flds) ' selects all
  records in table/view
• ' select Empno,Name from Employees
• ' plus (bonus!) fills in the fieldlist with
  descs. of fields in result set.
• Print flds.Fieldcount " fields."
• ' read one record, loop until there's no next
  record
• Do While con.Fetch(flds) gt 0
• ' Process the record. Print values
• ' of Empno and Name fields.
• Print flds.Empno(0) " " flds.Name(0)
• Loop

7 fields. 21234 Maharishi Mahesh Yogi 90922
Shirley MacLaine 50446 Richard Bach ...
12
Locating and Updating a Single Record

• con.Writeback True ' when we update, update the
  row just read.
• Dim flds As New LCFieldList
• Dim keys As New LCFieldList
• Dim empNo As LCField
• Dim count As Long
• Set empNo keys.Append("Empno", LCTYPE_TEXT)
• empNo.Values "90922"
• empNo.Flags LCFIELDF_KEY ' this field is now a
  key for SELECT
• count con.Select(keys, 1, flds)
• Print count " matches found." ' prints -1 or 0
  with some connectors.
• ' select from Employees where Empno "90922"
• If count ltgt 0 Then
• flds.Name "Ms. Shirley MacLaine"
• con.Update flds
• ' if not using Writeback, one of the flds
  list must have key flags.
• End If

13
Good Stuff to Know

• Different connectors have different properties.
• Select uses a single table or view.
• To write your own native command, use Execute.
• Fetch reads the result set of Select, Call, or
  Execute.
• To control which fields Select returns, use
  LCConnection.FieldNames (default all fields).
• To control which records Select finds, use "key
  field" flags in LCField objects, and/or
  LCConnection.Condition.
• MapByName vs. by position.
• Use Writeback for easy updates and record
  locking.
• Update or Remove in writeback affects record just
  read.
• In non-writeback, uses keys and Condition, like
  Select.
• Use Order metaconnector to sort data, or native
  sorting.
• NotesAgent.RunOnServer to centralize DB
  software/credentials.

14
High Performance Tips

• Use shared LCField objects to avoid manual
  copying.
• Read/write multiple rows of data at a time.
• Perform field lookups before looping.
• Enable connection pooling.

15
Compare Code - Field Sharing

• ' Copy all records from A to B.
• ' code omitted here to set up fieldlist BFlds.
• A.Select(Nothing, 1, AFlds)
• Do Until A.Fetch(AFlds) 0
• BFlds.AcctNo AFlds.KeyCode(0)
• BFlds.AcctName UCase(AFlds.AcctID(0))
• BFlds.LastPayment AFlds.PayDate(0)
• Call B.Insert(BFlds)
• Loop

AFlds
KeyCode
AcctID
PayDate
text
text
date
BFlds
AcctNo
AcctName
LastPayment
text
text
date
LCFieldList
LCField

• A.Select(Nothing, 1, AFlds)
• BFlds.MapName(AFlds, "KeyCode,PayDate",
  "AcctNo,LastPayment")
• BFlds.Append("AcctName", LCTYPE_TEXT)
• Do Until A.Fetch(AFlds) 0
• BFlds.AcctName UCase(AFlds.AcctID(0))
• Call B.Insert(BFlds)
• Loop

KeyCode
AcctID
PayDate
AFlds
text
text
date
AcctNo
LastPayment
AcctName
BFlds
text
16
Read/Write multiple rows

• Goal Reduce overhead by sending fewer, bigger
  packets of information.
• Some connector back-ends support multiple-row
  operations.
• If not, the LSX still allows a multiple-row call
  -- it's just not faster.
• Not supported when using stored procedures.
• Doesn't work in conjunction with Order
  metaconnector.

17
Compare Code - Multiple row fetch/update

• Dim AFlds As New LCFieldList, BFlds As New
  LCFieldList
• A.Select(Nothing, 1, AFlds)
• BFlds.MapName(AFlds, "KeyCode,PayDate",
  "AcctNo,LastPayment")
• BFlds.Append("AcctName", LCTYPE_TEXT)
• Do Until A.Fetch(AFlds) 0
• BFlds.AcctName UCase(AFlds.AcctID(0))
• Call B.Insert(BFlds)
• Loop

• Dim AFlds As New LCFieldList(50), BFlds As New
  LCFieldList(50)
• A.Select(Nothing, 1, AFlds)
• BFlds.MapName(AFlds, "KeyCode,PayDate",
  "AcctNo,LastPayment")
• BFlds.Append("AcctName", LCTYPE_TEXT)
• Do
• count A.Fetch(AFlds, 1, 50)
• If count gt 0 Then
• BFlds.AcctName ArrayUCase(AFlds.AcctID)
• Call B.Insert(BFlds, 1, count)
• End If
• Loop Until count 0

Function ArrayUCase(x) As Variant Redim
result(Lbound(x) To Ubound(x)) As String Dim
i For i Lbound(x) To Ubound(x)
result(i) UCase(x(i)) Next ArrayUCase
result End Function
18
Look Before You Loop

• Use LCFieldList.Lookup to get handles to LCField
  objects before entering a Fetch loop.
• Saves overhead of repeatedly searching the
  fieldlist for a name.
• The same LCField objects are used for each call
  to Fetch -- only the data changes.

19
Compare Code - Look Before You Loop

• A.Select(Nothing, 1, AFlds)
• BFlds.MapName(AFlds, "KeyCode,PayDate",
  "AcctNo,LastPayment")
• BFlds.Append("AcctName", LCTYPE_TEXT)
• Do
• count A.Fetch(AFlds, 1, 50)
• If count gt 0 Then
• BFlds.AcctName ArrayUCase(AFlds.AcctID)
• Call B.Insert(BFlds, 1, count)
• End If
• Loop Until count 0

• A.Select(Nothing, 1, AFlds)
• BFlds.MapName(AFlds, "KeyCode,PayDate",
  "AcctNo,LastPayment")
• Dim BAcctName As LCField, AAcctID As LCField
• BAcctName BFlds.Append("AcctName", LCTYPE_TEXT)
• AAcctID AFlds.Lookup("AcctID")
• Do
• count A.Fetch(AFlds, 1, 50)
• If count gt 0 Then
• BAcctName.Value ArrayUCase(AAcctID.Value)
• Call B.Insert(BFlds, 1, count)
• End If
• Loop Until count 0

AAcctID
text
BAcctName
AFlds
KeyCode
AcctID
PayDate
date
text
text
BFlds
AcctNo
LastPayment
AcctName
20
Connection Pooling
This goes before any LCConnection objects are
created.
Dim session As New LCSession session.ConnectionPoo
ling True
Generally stays on until a Notes/Domino restart
happens. Setting to False doesn't clear existing
connections in the pool. It just keeps new ones
from joining it.
Note manually commit to avoid dangling
transactions.
21
Emptying a Connection Pool
Dim session As New LCSession Call
session.FlushPool
This method is not doc'ed, but is very useful.
22
Metaconnectors

• Provide a layer of functionality between your
  program and the connector.
• trace log all interactions with the database
• meter record statistics of data transfer
• order sort data as it's read
• ...

Set con New LCConnection("order") con.ConnectorN
ame "db2" con.OrderNames "fieldname"
vs
Set con New LCConnection("db2")
23
Using the "order" Metaconnector
LCConnection ("order")
Dim con As New LCConnection("order")
LCConnection ("db2")
ConnectorName
con.ConnectorName "db2"
con.OrderNames "EMPNO"
OrderNames
Database
con.Database "SAMPLE"
Metadata
con.Metadata "EMPDATA"
...
Userid
Password
...
Many connectors have their own sorting capability
which gives better performance than the
metaconnector -- however, metaconnector offers
more options.
24
Character Set Conversion

• Conversion of LCStreams based on "code pages".
• May not be possible, e.g. there is no ASCII code
  for
• LC detects character set in use and converts
  automatically during read and write.
• If LC guesses wrong, look at database and
  notes.ini settings (see technotes). If that
  doesn't work...
• Use binary LCStream object (LCSTREAMFMT_BLOB) to
  convert manually in your code.
• Methods to do "manual" conversion
• LCField.GetStream (from a field to a LCStream)
• LCField.Convert (from a field to another field)
• LCField.SetStream (from a stream to a field)
• LCStream.Convert (from stream to another stream)

25
Intermediate binary buffer for character
conversion

• Suppose LC believes a text field to be in Windows
  Cyrillic (CP 1251), a.k.a. LCSTREAMFMT_IBMCP1251
• You know the data are actually in Windows Greek
  (CP 1253, LCSTREAMFMT_IBMCP1253 ) and want to
  convert to Unicode to store in MS Access.

Dim buffer As LCStream Dim srcFld As LCField Dim
destFld As New LCField(LCTYPE_TEXT) Call
destFld.SetStreamFormat( , , LCSTREAMFMT_UNICODE)
... Set buffer srcFld.GetStream(1,
LCSTREAMFMT_BLOB) ' copy raw binary
data. buffer.Format LCSTREAMFMT_IBMCP1253 '
change interpretation without replacing
data. Call destFld.SetStream(1, buffer) ' data
are automatically converted to type of destFld.
26
DEMO

• A simple application to copy data from Notes to
  DB2 and back.
• Demonstrates
• field sharing
• multiple row fetching
• look before you loop
• connection pooling
• character set conversion
• metaconnector for sorting
• creating a table
• Download demo database from LDD Sandbox
• http//www.lotus.com/ldd
• click "Sandbox"
• search for "LC LSX"

27
"Create Metadata" Agent
' establish the parameters of the
connection. Dim con As New LCConnection("db2") c
on.database "SAMPLE" ... con.Metadata
"EMPDATA" con.Connect ' create field list
with the columns we want to have in a new DB2
table. Dim flds As New LCFieldList, aFld As
LCField Set aFld flds.Append("EMPNO",
LCTYPE_TEXT) Call aFld.SetFormatStream(0, 10) '
ten chars max Set aFld flds.Append("LANGUAGE",
LCTYPE_INT) Set aFld flds.Append("SURNAME",
LCTYPE_BINARY) Call aFld.SetFormatStream(, 250,
LCSTREAMFMT_BLOB) ... Set aFld
flds.Append("HOMEPHONE", LCTYPE_TEXT) Call
aFld.SetFormatStream(0, 20) ' delete the table
if it exists -- if not, ignore resulting
error. On Error Resume Next con.Drop
LCOBJECT_METADATA ' create the table using the
fieldlist defined above. Call con.Create(LCOBJECT
_METADATA, flds)
28
Export Agent - Create Connection to Notes
' Establish connection to current Notes database
thru the LC. Dim ses As New NotesSession Dim db
As NotesDatabase Set db ses.CurrentDatabase Di
m notesCon As New LCConnection("notes") notesCon.
Server db.Server ' create LC connection to
current database. notesCon.Database
db.FilePath notesCon.Metadata "Employee" '
name/alias of Notes form notesCon.MapByName
True notesCon.Connect ' select all Notes
documents that use this form. Dim notesFlds As
New LCFieldList Call notesCon.Select(Nothing, 1,
notesFlds)
Use the LC to access Notes so that the
information is read in a form that can be written
directly to the external database.
29
Notes Input Field List
EmpNum
EmpLang
FirstName
LastName
FullName
Phone
notesFlds
type text
type text
type text
type text
type text
type text
30
Export Agent - Look before you loop
Dim srcFName As LCField, srcLName As LCField Dim
srcFullName As LCField, srcLang As LCField Dim
empNo As LCField Set srcLang
notesFlds.Lookup("EmpLang") Set srcFName
notesFlds.Lookup("FirstName") Set srcLName
notesFlds.Lookup("LastName") Set srcFullName
notesFlds.Lookup("FullName")
31
Export Agent - Create Output Fieldlist
' make EMPNO and EmpNum point to the same field
object. Likewise HOMEPHONE and Phone. ' This
makes it unnecessary to copy them from one field
list to the other. Dim extFlds As New
LCFieldList Call extFlds.MapName(notesFlds,
"EmpNum,Phone", "EMPNO,HOMEPHONE") ' for other
fields, create independent LCField objects and
add them to the list. ' we don't care what order
they go in because extCon.MapByName is True. '
otherwise we'd use Insert to put them in the
right order. Dim destFName As LCField, destLName
As LCField Dim destFullName As LCField, destLang
As LCField Set destLang extFlds.Append("LANGUAG
E", LCTYPE_INT) Set destFName
extFlds.Append("GIVENNAME", LCTYPE_BINARY) Call
destFName.SetFormatStream(, , LCSTREAMFMT_BLOB)
Set destLName extFlds.Append("SURNAME",
LCTYPE_BINARY) Call destLName.SetFormatStream(,
, LCSTREAMFMT_BLOB) Set destFullName
extFlds.Append("FULLNAME", LCTYPE_BINARY) Call
destFullName.SetFormatStream(, , LCSTREAMFMT_BLOB)
32
Field Lists in Export Agent
EmpNum
EmpLang
FirstName
LastName
FullName
Phone
notesFlds
type text
type text
type text
type text
type text
type text
extFlds
EMPNO
HOMEPHONE
LANGUAGE
FULLNAME
GIVENNAME
SURNAME
type integer
type BLOB
type BLOB
type BLOB
33
Export Agent - Create Output Fieldlist
' Designate empNo as a key field (for the Update
command). empNo.Flags empNo.Flags Or
LCFIELDF_KEY
Default key match is "". Though this field is
shared with the Notes field list, it doesn't
affect reading from Notes because notesFlds is
never used as the key list in Select.
34
Export Agent - Read and Write Data
Do While notesCon.Fetch(notesFlds, 1, 1) ' In
Notes form English is string "EN" and Japanse is
"JP", but DB2 table uses integer 0 and 1. Select
Case srcLang.Value(0) Case "JP" charCodeSet
LCSTREAMFMT_IBMCP932 ' MS-DOS PC Japanese
Microsoft Shift-JIS destLang.Value 1 Case
Else charCodeSet LCSTREAMFMT_ASCII ' regular
ASCII characters destLang.Value 0 End
Select ' Convert the Notes characters to the
desired character code set for English or
Japanese, ' and copy that data into the BLOB
field (which does not convert them further
because ' BLOBS don't have character sets
associated with them). Call destFName.SetStream(1
, srcFName.GetStream(1, charCodeSet)) ... '
Update the DB2 database. If the update fails, the
record must not yet exist, so insert instead. If
extCon.Update(extFlds, 1, 1) 0 Then Call
extCon.Insert(extFlds, 1, 1) ' it would be
smart to do some error checking here in case the
insert failed. ... End If Loop
35
Scan Agent - Create Connection
Dim extCon As LCConnection Dim reply As
Integer Dim readCount As Long reply
Messagebox("Would you like sorting with that?",
MB_YESNO, "Scan DB2 Data") If reply IDYES
Then Set extCon New LCConnection("order") ex
tCon.ConnectorName "db2" extCon.OrderNames
"EMPNO" readCount 1 ' only read one record at
a time Else Set extCon New
LCConnection("db2") readCount 20 ' read up to
20 records simultaneously. End
If extCon.database "SAMPLE"
Order Metaconnector can't handle multiple row
reads, so only use multiple row when not using
sorting.
36
Scan Agent - Select Data Get LCField Handles
' dimension field list to contain the max number
of entries read simultaneously. Dim extFlds As
New LCFieldList(readCount) Call
extcon.Select(Nothing, 1, extFlds) ' select all
records in the EMPLOYEE table Dim i As Long,
count As Long, charCodeSet As Long ' "Look
before you loop" Dim givenName As LCField,
fullName As LCField, empNo As LCField, lang As
LCField Set givenName extFlds.Lookup("GIVENNAME
") Set fullName extFlds.Lookup("FULLNAME") Set
lang extFlds.Lookup("LANGUAGE") Set empNo
extFlds.Lookup("EMPNO")
37
Scan Agent - Loop to Read Data
Do count extcon.Fetch(extFlds, 1, readCount) '
fetch readCount records at a time. For i 1 To
count Select Case lang.Value(i-1) Case 1 '
japanese character set charCodeSet
LCSTREAMFMT_IBMCP932 ' MS-DOS PC Japanese
Microsoft Shift-JIS Case Else ' ascii
character set charCodeSet LCSTREAMFMT_ASCII
End Select Dim buffer As New LCStream Dim
rawBuffer As LCStream Set rawBuffer
givenName.GetStream(i, LCSTREAMFMT_BLOB) Call
buffer.Convert(rawBuffer, 0, charCodeSet) Print
givenName " buffer.Text "
rawBuffer.Length bytes Set buffer
fullName.GetStream (i, charCodeSet) Set
rawBuffer fullName.GetStream(i,
LCSTREAMFMT_BLOB) ' just to get the length in
bytes Print fullName " buffer.Text "
rawBuffer.Length bytes Next Loop
Until count 0
38
Additional Info

• www.lotus.com/ei
• Lotus Developer Domain - Enterprise Integration
  Forum www.lotus.com/ldd/eiforum.nsf
• SearchDomino.com Ask the Experts, Andre
  Guirard
• http//searchdomino.techtarget.com/ateQuestion/0,2
  89624,sid4_tax293289,00.html

39
Q A Submit your questions now by clicking on
the Ask a Question button in the bottom left
corner of your presentation screen.

• \

Thank you! You can send additional questions to
Andre Guirard via editor_at_searchdomino.com.
40
Feedback

• Thank you for your participation
• Did you like this Webcast topic? Would you like
  us to host other events similar to this one? Send
  us your feedback on this event and ideas for
  other topics at editor_at_searchWin2000.com.