DB2 Run from LotusScript into Notes Form

'@AppName Döcu
'@author: Dököll Solutions, Inc.
'@version 2012.11.03.8.56.PM
'Program: ConnectDB2LSClonePrint
'Code to connect to DB2, grab data and insert into NSF back-end as documents
'Item being prepared to load from Xpages
Option Public
Option Declare
UseLSX "*lsxlc"
Sub Initialize
	'TO DO: Load connection variable into a Script Library	
	'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	'DB2 Connection variables Begin
	'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	'Dimension lotusConnectorSession variable to Connect to DB2
	Dim lConnSession As New LCSession
	'enforce ConnectionPooling
	lConnSession.ConnectionPooling = True	
	'Dimension lotusConnection variable to Connect to DB2
	Dim lotusConnector As New LCConnection ("DB2")
	lotusConnector.Database = "DOMINODB" 'Grab DataSource created on Windows 7 OS System
	lotusConnector.UserID = "db2admin" 'Connect to DB2 using Windows 7 System User created during installation
	lotusConnector.Password = "Win7SystemUser" 'Connect to DB2 using Windows 7 System Password created during installation
	lotusConnector.Disconnect '...
	lConnSession.Clearstatus'...
	lotusConnector.Connect'...	
	'Dimension placeholder variable to query LCFieldList
	Dim fieldList As New LCFieldList
	'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	'DB2 Connection variables End
	'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	'Dimension notesDoc variables
	Dim SUBJECTSID As Variant
	Dim USERNAME As Variant
	Dim ALLEGATIONCODE As Variant
	Dim OFFICE As Variant
	Dim INVESTIGATOR As Variant
	Dim DATEOPEN As Variant
	Dim AGE As Variant
	Dim SUBJECT As Variant
	Dim LICENSEE As Variant
	Dim ALLEGATIONNUMBER As Variant
	Dim REGION As Variant
	'Query DB2 back-end and load results to fieldList variable
	lotusConnector.Execute "SELECT * from DB2ADMIN.USER_DETAILS", fieldList 
	'set and initialize notesDoc variables, attach items found in fieldList
	Set SUBJECTSID = fieldList.Lookup ("ID")
	Set USERNAME = fieldList.Lookup ("USERNAME")
	Set ALLEGATIONCODE = fieldList.Lookup ("ALLEGATIONCODE")
	Set OFFICE = fieldList.Lookup ("OFFICE")
	Set INVESTIGATOR = fieldList.Lookup ("INVESTIGATOR")
	Set DATEOPEN = fieldList.Lookup ("DATEOPEN")
	Set AGE = fieldList.Lookup ("AGE")
	Set SUBJECT = fieldList.Lookup ("SUBJECT")
	Set LICENSEE = fieldList.Lookup ("LICENSEE")
	Set ALLEGATIONNUMBER = fieldList.Lookup ("ALLEGATIONNUMBER")
	Set REGION = fieldList.Lookup ("REGION")
	'run through and submit if fieldList actually collectd values from DB2
	While (lotusConnector.Fetch(fieldList) > 0)
	'Dimension placeholder variables to load column values from fieldList
		Dim thisSUBJECTSID As String
		Dim thisUSERNAME As String
		Dim thisALLEGATIONCODE As String
		Dim thisOFFICE As String
		Dim thisINVESTIGATOR As String
		Dim thisDATEOPEN As String
		Dim thisAGE As String
		Dim thisSUBJECT As String
		Dim thisLICENSEE As String
		Dim thisALLEGATIONNUMBER As String
		Dim thisREGION As String
		'load column values to into thisVariable, attach to notesDoc
		thisSUBJECTSID = SUBJECTSID.text(0) '...
		thisUSERNAME = USERNAME.text(0) ''
		thisALLEGATIONCODE = ALLEGATIONCODE.text(0) ''
		thisOFFICE = OFFICE.text(0) ''
		thisINVESTIGATOR = INVESTIGATOR.text(0) ''
		thisDATEOPEN = DATEOPEN.text(0) ''
		thisAGE = AGE.text(0) ''
		thisSUBJECT = SUBJECT.text(0) ''
		thisLICENSEE = LICENSEE.text(0) ''
		thisALLEGATIONNUMBER = ALLEGATIONNUMBER.text(0) ''
		thisREGION = REGION.text(0) ''
	'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	'Current Database Connection variables Begin
	'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
		'Dimension variable for currentDBSession
		Dim thisSession As New NotesSession		
		'Dimension variable for currentDB
		Dim thisDB As NotesDatabase
		'Dimension variable for the documents being loaded to NSF back-end from DB2 data
		Dim notesDoc As NotesDocument
		'find the currentDB based on thisSession
		Set thisDB = thisSession.GetDatabase("", "docu.nsf")
	'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	'Current Database Connection variables Begin
	'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
		'arrange for notesDocs to be sumitted to currentDB
		Set notesDoc = New NotesDocument(thisDB)	
		'add notesDocs to the form in question
		notesDoc.Form ="DB2Report"
		'load column results of DB2 into NSF back-end fields
		notesDoc.ID =thisSUBJECTSID
		notesDoc.USERNAME =thisUSERNAME
		notesDoc.ALLEGATIONCODE=thisALLEGATIONCODE
		notesDoc.OFFICE=thisOFFICE
		notesDoc.INVESTIGATOR=thisINVESTIGATOR
		notesDoc.DATEOPEN=thisDATEOPEN
		notesDoc.AGE=thisAGE
		notesDoc.SUBJECT=thisSUBJECT
		notesDoc.LICENSEE=thisLICENSEE
		notesDoc.ALLEGATIONNUMBER=thisALLEGATIONNUMBER
		notesDoc.REGION=thisREGION
		'Save the notesDoc iems
		Call notesDoc.Save(True,True)
		'release the currentDBSession
		Set thisSession = Nothing
	Wend	
End Sub





Use Lotus Connector, Lotus Connector Session, Lotus Connector FieldList to grab data from DB2 and submit to NSF back-end as Notes Documents.  Run the LotusScript code from Xpages button and obtain results.  Information that can be used to later search the current DOMINODB database dynamically via links on Xpages...

LotusScript
Köll S Cherizard
November 4, 2012 2:59 AM
Rating
176

All code submitted to OpenNTF XSnippets, whether submitted as a "Snippet" or in the body of a Comment, is provided under the Apache License Version 2.0. See Terms of Use for full details.



No comments yetLogin first to comment...