Manage Learn to apply best practices and optimize your operations.

Using Lotus Connectors to execute stored procedures

Use Lotus Connector classes in a scheduled agent to execute a sybase stored procedure

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

Dig Deeper on Domino Resources - Part 6

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchWindowsServer

Search400

  • iSeries tutorials

    Search400.com's tutorials provide in-depth information on the iSeries. Our iSeries tutorials address areas you need to know about...

  • V6R1 upgrade planning checklist

    When upgrading to V6R1, make sure your software will be supported, your programs will function and the correct PTFs have been ...

  • Connecting multiple iSeries systems through DDM

    Working with databases over multiple iSeries systems can be simple when remotely connecting logical partitions with distributed ...

SearchDataCenter

SearchContentManagement

Close