'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