Using Lotus Connectors to execute stored procedures

Using Lotus Connectors to execute stored procedures

I had to use Lotus Connector classes in a scheduled agent to execute a sybase stored procedure. The stored procedure requires an input parameter(a record id) which I get from a Notes document and returns a multi line result set which I concatenate together to form a comments field on the same notes document. The only mods needed are to change the parameters for the source connection and the GetField(x) value to the applicable column in the result set that contains the info you need.
Sub Initialize
	Dim notessession As New NotesSession
	Dim db As NotesDatabase     
	Dim dc As NotesDocumentCollection     
	Dim view As NotesView     
	Dim doc As NotesDocument
	Dim docfld As NotesItem
	
	Dim session As New LCSession	
	Dim src As New LCConnection ("sybase")  
	Dim fldLst As LCFieldList  'result set provided by stored proc
	Dim keyLst As New LCFieldList  'input to stored proc
	Dim fld As LCField
	Dim notesServer As String
	Dim notesDb As String
	Dim viewName As String
	Dim inputFieldName As String
	
	'Set src attributes	
	src.Server = "SYBASESERVER"	
	src.Database = "SYBASEDBASE"
	src.Userid = "SYBASEUSER"
	src.Password = "SYBASEPW"
	src.Procedure = "STOREDPROC"
	
	'Connect to src
	src.Connect 
	
	'Identify location of Notes Documents to update
	'For this example, the doc resides within this db
	notesServer = notessession.CurrentDatabase.Server
	notesDb = notessession.CurrentDatabase.FilePath
	viewName= "VIEWNAME"
	inputFieldName="FIELDNAME

    Requires Free Membership to View

    Login

    Register today to access targeted resources from our editorial writers and independent industry experts focused on Lotus Domino, Notes, Workplace and other related technologies.

    By submitting your registration information to SearchDomino.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchDomino.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

 PROVIDING INPUT PARAMETER TO SYBASE DB"
	
	Set db = notessession.GetDatabase(notesServer,notesDb) 
	Set view = db.GetView( viewName)     
	Set doc=view.GetFirstDocument    
	Set fld = keyLst.Append(inputFieldName,LCTYPE_TEXT)
	
	While Not (doc Is Nothing)     
	    'docfld is the field used on the Notes Document 
	    'as an input to the stored procedure		
		Set docfld=doc.GetFirstItem(inputFieldName)
		
	    'Get the field value from the Notes doc
		fld.text=docfld.Text
		fld.value=docfld.Text
		
		Set fldLst = New LCFieldList
		
		'Call the stored procedure, provide it the input parameter, and get the result set 
		If (src.Call (keyLst, 1, fldLst) = 0) Then
			'If no result set, set the field to NULL
			doc.Comment = ""
		Else
			'Process the result set - Concatenate text together
			'The Getfield(3) returns the 3rd field in the result set
			While src.Fetch (fldLst, 1, 1) > 0
				message = message & fldLst.Getfield(X).text(0)
			Wend
			doc.Comment = Cstr(message)
		End If
		
		Call doc.Save(False, True ) 
		
		'Get ready for next document
		Delete fldLst
		message = Null
		Set doc=view.GetNextDocument(doc)
		
	Wend  
	src.Disconnect
End Sub

This was first published in January 2002

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

Back to top