SQL Agent

HCL Domino Domino Forum
1

HiDoes anyone have some basic code for an agent to access fields from an SQL DB and display in a Notes DB?

Thanks.

C

2

Subject: RE: SQL Agent

There are several different ways to do this discussed in the LEI Redbook. Most of the techniques do not require that you have a copy of LEI.

3

Subject: SQL Agent: ODBC used for creating documents

This uses an ODBC source to create documents in Notes.

Sub Initialize

Dim LC_S As New LCSession

Dim LC_Conn As New LCConnection("odbc2")

Dim LC_FldLst As New LCFieldList(1)



Dim Count As Long

Dim SelectStatement As String

Dim msg As String

Dim ErrorTxt As String

Dim msgcode As Long

Dim i As Double



Dim s As New NotesSession

Dim db As NotesDatabase

Dim importedDoc As NotesDocument



Set db = s.CurrentDatabase



On Error Goto ErrorHandler

LC_Conn.Userid = "[ID]"

LC_Conn.Password = "[Password]"

LC_Conn.Server = "Name of ODBC source"

LC_S.ClearStatus

LC_Conn.Connect

'Lookup in external source

SelectStatement = | SELECT * FROM CustTable |



count = LC_Conn.Execute(SelectStatement, LC_FldLst)



Print "Importing"



i = 0



If count <> 0 Then ' 0 indicates no result from execution of selection.

	count = LC_Conn.Fetch(LC_FldLst, 1, 1)  ' Read row in resultset. If you expect several rows, call Fetch in a loop.

	

	While (count > 0) 'If row found in resultset.

		Set importedDoc = db.CreateDocument

		

		importedDoc.Form = "[FormName]"

		importedDoc.DeliveryName = Cstr(LC_FldLst.DeliveryName(0))

		importedDoc.CustName = Cstr(LC_FldLst.Name(0))

		importedDoc.CountryCode = Ucase(Cstr(LC_FldLst.Country(0)))

		

		Call importedDoc.Save(True,False)

		i = i + 1

		

		count = LC_Conn.Fetch(LC_FldLst, 1, 1)  ' Read row in resultset. If you expect several rows, call Fetch in a loop.

	Wend		

End If	



LC_Conn.Disconnect  'This is not necessary. If omitted, connection pooling is available.



Print "Imported " + Cstr(i) + " documents"



End 'Terminates execution

ErrorHandler:

If LC_S.status <> LCSuccess Then

	Call LC_S.GetStatus(errortxt, msgcode, msg)

Else

	errortext = "Error: " & Err() & ": " & Error()

End If



Print errortext

End Sub

4

Subject: SQL Agent: OLEDB connector returning row

Here is an example using oledb connector. It is often a better way of connecting than ODBC.

Options: Uselsx “*lsxlc”

Sub Initialize

On Error Goto Handler



Dim nSession As New NotesSession

Dim lcSession As New LCSession



Dim fldLst As New LCFieldList

Dim connection As New LCConnection("oledb")

Dim custName As LCField



Dim db As NotesDatabase

Dim doc As NotesDocument



lcSession.ConnectionPooling = True

lcSession.ClearStatus



connection.Provider = "sqloledb"  'Connecting to SQL Server

connection.Init_datasource = "nnn.nnn.nnn.nnn"  'IP-adress?

connection.Database = "[Database]"

connection.UserId = "[User]"

connection.Password = "[Password]"

connection.CommitFrequency = 0



connection.Connect

Print "Successfully connected to SQL Server."



'Excecute selection

If (connection.Execute (|SELECT * FROM OCUSMA|,  fldLst) = 0) Then

	Print "No record matching query."

Else

	'Lookup fields of interest

	Set custName = fldLst.Lookup ("OKCUNM")

	

	'Loop this to fetch more than one result row.

	Call connection.Fetch(fldLst)

	Print "Customer name: " + custName.Text(0)

	

End If



'Disconnect

connection.Disconnect

Print "Successfully disconnected from SQL Server."



End

Handler:

If (lcSession.Status <> LCSUCCESS) Then

	Messagebox lcSession.GetStatusText, 0, "The following Lotus Connector error has occurred"

Else

	Messagebox Error$, 0, "The following LotusScript error has occurred"

End If

End

End Sub