Transcript Look before you loop

WEBCAST SCHEDULE
Today’s 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 Q&A 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.
TECHNICAL FAQs
Here are answers to the most common technical problems users encounter during a Webcast:
Q: Why can’t 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/
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
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
Comparing LotusScript Database Tools
LS:DO
–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
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.
Enterprise Integration Architecture
Domino Clients
Domino Applications
Agents Scripts, Servlets...
Forms, Views, Pages, Frames
LEI
Base
Relational Data
DECS
LC LSX
DCRs
Lotus Connectors
Domino
Data
ERP
Systems
ESB
Premium
Unstructured
Data
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)
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?
Establishing a Connection
1. Uselsx "*lsxlc"
' lsxlei is no longer used -- migration tool available.
2. Dim lcSes As New LCSession("sessionNameForLog") ' for scripted activities.
3. Dim con As New LCConnection("connectionDocName")
4.
' name of LEI Admin connection document.
or...
2.
3.
4.
5.
6.
7.
8.
9.
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
Selecting and Fetching All Records in a Table
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
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) > 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
...
Locating and Updating a Single Record
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
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 <> 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
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.
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.
Compare Code - Field Sharing
1.
2.
3.
4.
5.
6.
7.
' Copy all records from A to B.
AFlds KeyCode
AcctID
PayDate
' code omitted here to set up fieldlist BFlds.
A.Select(Nothing, 1, AFlds)
text
text
date
Do Until A.Fetch(AFlds) = 0
BFlds AcctNo
AcctName
LastPayment
BFlds.AcctNo = AFlds.KeyCode(0)
BFlds.AcctName = UCase(AFlds.AcctID(0))
text
text
date
BFlds.LastPayment = AFlds.PayDate(0) LCFieldList
Call B.Insert(BFlds)
LCField
Loop
1.
2.
3.
4.
5.
6.
7.
A.Select(Nothing, 1, AFlds)
BFlds.MapName(AFlds, "KeyCode,PayDate", "AcctNo,LastPayment")
BFlds.Append("AcctName", LCTYPE_TEXT)
AcctID
PayDate
AFlds KeyCode
Do Until A.Fetch(AFlds) = 0
text
text
date
BFlds.AcctName = UCase(AFlds.AcctID(0))
Call B.Insert(BFlds)
LastPayment
AcctName
BFlds AcctNo
Loop
text
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.
Compare Code - Multiple row fetch/update
1.
2.
3.
4.
5.
6.
7.
8.
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
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
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)
Function ArrayUCase(x) As Variant
If count > 0 Then
Redim result(Lbound(x) To Ubound(x)) As String
BFlds.AcctName = ArrayUCase(AFlds.AcctID)
Dim i%
Call B.Insert(BFlds, 1, count)
For i = Lbound(x) To Ubound(x)
End If
result(i) = UCase(x(i))
Loop Until count = 0
Next
ArrayUCase = result
End Function
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.
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 > 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")
AAcctID
•Dim BAcctName As LCField, AAcctID As LCField
•BAcctName = BFlds.Append("AcctName", LCTYPE_TEXT)
text
•AAcctID = AFlds.Lookup("AcctID")
BAcctName
•Do
AFlds KeyCode AcctID PayDate
• count = A.Fetch(AFlds, 1, 50)
• If count > 0 Then
date
text
text
•
BAcctName.Value = ArrayUCase(AAcctID.Value)
• Call B.Insert(BFlds, 1, count)
BFlds AcctNo LastPayment AcctName
• End If
•Loop Until count = 0
Connection Pooling
Dim session As New LCSession
session.ConnectionPooling = 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.
This goes before
any LCConnection
objects are
created.
Note: manually
commit to avoid
dangling
transactions.
Emptying a Connection Pool
Dim session As New LCSession
Call session.FlushPool
This method is not
doc'ed, but is very
useful.
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("db2")
vs
Set con = New
LCConnection("order")
con.ConnectorName = "db2"
con.OrderNames = "fieldname"
Using the "order" Metaconnector
Dim con As New LCConnection("order")
LCConnection
("order")
con.ConnectorName = "db2"
ConnectorName
con.OrderNames = "EMPNO"
OrderNames
LCConnection
("db2")
con.Database = "SAMPLE"
Database
con.Metadata = "EMPDATA"
Metadata
...
Userid
Password
...
Many connectors have their own sorting capability which
gives better performance than the metaconnector -however, metaconnector offers more options.
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)
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.
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"
"Create Metadata" Agent
' establish the parameters of the connection.
Dim con As New LCConnection("db2")
con.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)
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
Dim 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.
Notes Input Field List
notesFlds EmpNum
type text
EmpLang
type text
FirstName
type text
LastName
FullName
Phone
type text
type text
type text
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")
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("LANGUAGE", 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)
Field Lists in Export Agent
notesFlds EmpNum
type text
EmpLang
FirstName
type text
LastName
FullName
Phone
type text
type text
type text
type text
extFlds EMPNO HOMEPHONE LANGUAGE FULLNAME
type
integer
type
BLOB
GIVENNAME
type
BLOB
SURNAME
type
BLOB
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.
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
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")
extCon.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.
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")
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
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,289624,sid4_tax2
93289,00.html
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
[email protected].
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 [email protected].