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