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 FAQsHere 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 GroupEnterprise Integration Team

http://www.lotus.com/ei

Agenda

Comparing LotusScript EI ToolsLotus Connector ArchitectureUsing 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.

Unstructured Unstructured DataData

Domino Clients

Relational DataRelational Data

Lotus Connectors

Domino Data

ERP Systems

LEI DECS DCRs LC LSX ESB

Domino ApplicationsAgents Scripts, Servlets...

Forms, Views, Pages, Frames

Base Premium

Enterprise Integration Architecture

LSX Classes

LCSession - Connector environment controlLCConnection - 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 MagazineEditorial Office108 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?

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...

Establishing a Connection

2. Dim lcSes As New LCSession ' (optional) all other cases.3. Dim con As New LCConnection("connectorName")4. ' e.g. db2 or oledb5. con.Database = ...6. con.Username = ...7. con.Password = ...8. con.Metadata = ... ' table name goes here.9. con.Connect

Selecting and Fetching All Records in a Table

10. Dim flds As New LCFieldList11. con.Fieldnames = "Empno,Name" ' which fields we want (default=*)12. Call con.Select(Nothing, 1, flds) ' selects all records in table/view13. ' select Empno,Name from Employees14. ' plus (bonus!) fills in the fieldlist with descs. of fields in result set.15. Print flds.Fieldcount & " fields."16. ' read one record, loop until there's no next record17. Do While con.Fetch(flds) > 018. ' Process the record. Print values19. ' of Empno and Name fields.20. Print flds.Empno(0) & ": " & flds.Name(0)21. Loop

7 fields.21234: Maharishi Mahesh Yogi90922: Shirley MacLaine50446: Richard Bach...

Locating and Updating a Single Record

10. con.Writeback = True ' when we update, update the row just read.11. Dim flds As New LCFieldList12. Dim keys As New LCFieldList13. Dim empNo As LCField14. Dim count As Long15. Set empNo = keys.Append("Empno", LCTYPE_TEXT)16. empNo.Values = "90922"17. empNo.Flags = LCFIELDF_KEY ' this field is now a key for SELECT18. count = con.Select(keys, 1, flds)19. Print count & " matches found." ' prints -1 or 0 with some connectors.20. ' select * from Employees where Empno = "90922"21. If count <> 0 Then22. flds.Name = "Ms. Shirley MacLaine"23. con.Update flds24. ' if not using Writeback, one of the flds list must have key flags.25. 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.

' Copy all records from A to B.' code omitted here to set up fieldlist BFlds.

1. A.Select(Nothing, 1, AFlds)2. Do Until A.Fetch(AFlds) = 03. BFlds.AcctNo = AFlds.KeyCode(0)4. BFlds.AcctName = UCase(AFlds.AcctID(0))5. BFlds.LastPayment = AFlds.PayDate(0)6. Call B.Insert(BFlds)7. Loop

1. A.Select(Nothing, 1, AFlds)2. BFlds.MapName(AFlds, "KeyCode,PayDate", "AcctNo,LastPayment")3. BFlds.Append("AcctName", LCTYPE_TEXT)4. Do Until A.Fetch(AFlds) = 05. BFlds.AcctName = UCase(AFlds.AcctID(0))6. Call B.Insert(BFlds)7. Loop

Compare Code - Field Sharing

text text date

AcctNo AcctName LastPayment

KeyCode AcctID PayDate

text text date

AFlds

BFlds

LCFieldList

LCField

text

text text date

AcctNo LastPayment AcctName

KeyCode AcctID PayDateAFlds

BFlds

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.

1. Dim AFlds As New LCFieldList(50), BFlds As New LCFieldList(50)2. A.Select(Nothing, 1, AFlds)3. BFlds.MapName(AFlds, "KeyCode,PayDate", "AcctNo,LastPayment")4. BFlds.Append("AcctName", LCTYPE_TEXT)5. Do6. count = A.Fetch(AFlds, 1, 50)7. If count > 0 Then8. BFlds.AcctName = ArrayUCase(AFlds.AcctID)9. Call B.Insert(BFlds, 1, count)10. End If11. Loop Until count = 0

1. Dim AFlds As New LCFieldList, BFlds As New LCFieldList2. A.Select(Nothing, 1, AFlds)3. BFlds.MapName(AFlds, "KeyCode,PayDate", "AcctNo,LastPayment")4. BFlds.Append("AcctName", LCTYPE_TEXT)5. Do Until A.Fetch(AFlds) = 06. BFlds.AcctName = UCase(AFlds.AcctID(0))7. Call B.Insert(BFlds)8. Loop

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 = resultEnd Function

Compare Code - Multiple row fetch/update

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.

•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")•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 > 0 Then• BAcctName.Value = ArrayUCase(AAcctID.Value)• Call B.Insert(BFlds, 1, count)• End If•Loop Until count = 0

Compare Code - Look Before You Loop

AAcctID

BAcctName

text

text

date

AcctNo LastPayment AcctName

KeyCode AcctID PayDate

text

AFlds

BFlds

This goes before any LCConnection

objects are created.

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.

Connection Pooling

Dim session As New LCSessionsession.ConnectionPooling = True

This method is not doc'ed, but is very

useful.

Emptying a Connection Pool

Dim session As New LCSessionCall session.FlushPool

Metaconnectors

Set con = New LCConnection("db2")Set con = New LCConnection("db2")

Set con = New Set con = New LCConnection("order")LCConnection("order")

con.ConnectorName = "db2"con.ConnectorName = "db2"

con.OrderNames = "con.OrderNames = "fieldnamefieldname""

vsvs

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–...

LCConnection("order")

OrderNames

ConnectorName

LCConnection("db2")

Metadata

Database

Userid

Password

...

Dim con As New LCConnection("order")

con.ConnectorName = "db2"

con.OrderNames = "EMPNO"

con.Database = "SAMPLE"

con.Metadata = "EMPDATA"

...

Using the "order" Metaconnector

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 forLC 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

Dim buffer As LCStreamDim srcFld As LCFieldDim 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.

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.

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 LCFieldSet aFld = flds.Append("EMPNO", LCTYPE_TEXT)Call aFld.SetFormatStream(0, 10) ' ten chars maxSet 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 Nextcon.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 NotesSessionDim db As NotesDatabaseSet db = ses.CurrentDatabaseDim notesCon As New LCConnection("notes")notesCon.Server = db.Server ' create LC connection to current database.notesCon.Database = db.FilePathnotesCon.Metadata = "Employee" ' name/alias of Notes formnotesCon.MapByName = TruenotesCon.Connect

' select all Notes documents that use this form.Dim notesFlds As New LCFieldListCall 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.

type text type text

EmpNum EmpLang FirstName LastName FullName PhonenotesFlds

Notes Input Field List

type text type text type texttype text

Export Agent - Look before you loop

Dim srcFName As LCField, srcLName As LCFieldDim srcFullName As LCField, srcLang As LCFieldDim 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 LCFieldListCall 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 LCFieldDim destFullName As LCField, destLang As LCFieldSet 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)

type integer

type text type text

EmpNum EmpLang FirstName LastName FullName PhonenotesFlds

extFlds

Field Lists in Export Agent

type text type text type texttype text

type BLOB

type BLOB

type BLOB

EMPNO HOMEPHONE LANGUAGE FULLNAME GIVENNAME SURNAME

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 "=".Default key match is "=".

Though this field is shared with the Notes field list, it doesn't Though this field is shared with the Notes field list, it doesn't affect reading from Notes because notesFlds is never used affect reading from Notes because notesFlds is never used as the key list in Select.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 = 1Case Else

charCodeSet = LCSTREAMFMT_ASCII ' regular ASCII charactersdestLang.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 LCConnectionDim reply As IntegerDim readCount As Longreply = 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

ElseSet extCon = New LCConnection("db2")readCount = 20 ' read up to 20 records simultaneously.

End IfextCon.database = "SAMPLE"

Order Metaconnector can't handle multiple row reads, so only Order Metaconnector can't handle multiple row reads, so only use multiple row when not using sorting. 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 tableDim 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 LCFieldSet 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

Docount = 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 setcharCodeSet = LCSTREAMFMT_ASCII

End Select

Dim buffer As New LCStreamDim rawBuffer As LCStreamSet 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

bytesPrint { fullName = "} & buffer.Text & {" [} & rawBuffer.Length & { bytes]}

NextLoop 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_tax293289,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 editor@searchdomino.com.

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@searchWin2000.com.