'LEI ODBC Example 'Julian Robichaux 'http://www.nsftools.com Option Public Option Declare Uselsx "*lsxlc" Sub Initialize '** This is an example of how to use LEI to get information from '** a database using ODBC DSNs (instead of using LEI connectors). '** It's better to use the native LEI connectors when possible, '** but this should show you a way to convert your LS:DO ODBC '** code over to LEI if you want to start using it. '** version 1.0 '** Julian Robichaux -- http://www.nsftools.com '** '** NOTE: a memory leak relating to the LEI ODBC2 connector was fixed '** in 6.5.4. Please make sure you're updated: '** http://www.ibm.com/support/docview.wss?rs=899&uid=swg21205655 On Error Goto processError Dim LCses As New LCSession '** just checking to see what connectors we have available; '** skip this if you don't care (or already know) Dim conName As String, conList As String Call LCses.ListConnector(LCLIST_FIRST, conName) conList = conName While LCses.ListConnector(LCLIST_NEXT, conName) conList = conList + ", " + conName Wend Print "Available connectors are: " & conList '** on to the good stuff Dim con As New LCConnection ("odbc2") '** note that it's "odbc2", not "odbc" Dim fieldList As New LCFieldList Dim tableName As String Dim sql As String Dim count As Integer '** obviously these need to be changed tableName = "MyTable" con.Server = "SomeSystemDSN" '** DSN Name, for ODBC con.Metadata = tableName '** not sure if Metadata is required for ODBC... con.Userid = "MyUserName" con.Password = "MyPassword" '** we should end up in the error block if the connection didn't work con.Connect '** execute the SQL and put the results in our FieldList (I think you can also '** pass "Nothing" for the FieldList if you're just doing an Update/Insert). '** If you do multiple Executes, you should keep using new FieldLists. sql = "SELECT * from " & tableName Call con.Execute(sql, fieldList) If (fieldList.FieldCount < 1) Then Print "Sorry, no results" Else '** it's more efficient to map all the fields upfront (note that GetField '** is a 1-based array, not 0-based) Dim fields List As LCField Dim stream As LCStream Dim i As Integer For i = 1 To fieldList.FieldCount Set fields(fieldList.GetName(i)) = fieldList.GetField(i) Next '** show the user what we got Print "The columns values that were returned were: " Do While (con.Fetch(fieldList) > 0) count = count + 1 Print " " Print "ROW #" & count Forall field In fields If (field.Datatype < 7) Then '** for most "normal" types of fields, we can just get the value directly Print Listtag(field) & " = " & field.Value(0) Else '** other types (like BLOB fields) may need to be converted, if possible Set stream = field.GetStream(1, LCSTREAMFMT_NATIVE) Print Listtag(field) & " (Datatype: " & field.Datatype & ") = " & stream.Text End If End Forall '** NOTE that you can also get the fields with "extended syntax", like '** NotesDocument fields, i.e.: '** Print fieldList.SomeFieldName(0) '** However, it's less efficient to do this on large resultsets. Using the '** method above, you could do the same thing with: '** Print fields("SomeFieldName").Value(0) Loop End If '** always disconnect when you're done... con.Disconnect Exit Sub processError: Dim errMsg As String If (LCses.Status <> LCSUCCESS) Then errMsg = "(LEI Error) " & LCses.GetStatusText LCses.ClearStatus Else errMsg = Error$ End If Print "Error on line " & Erl & ": " & errMsg If con.IsConnected Then con.Disconnect End If Exit Sub End Sub
This LotusScript was converted to HTML using the ls2html routine,
provided by Julian Robichaux at nsftools.com.