1 / 40

WEBCAST SCHEDULE

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.

chill
Download Presentation

WEBCAST SCHEDULE

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

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

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

  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 ESB LEI DCRs DECS Base Premium Lotus Connectors Unstructured Data Relational Data ERPSystems DominoData

  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) > 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 <> 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 BFlds AcctNo LastPayment AcctName 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 > 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 > 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 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.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. 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.ConnectorName = "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") 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)

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

  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("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)

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

  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,289624,sid4_tax293289,00.html

  39. Q & ASubmit 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.

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

More Related