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

RunDB2LotusScriptBean;

package com.dokoll.solutions.inc.db2.test;

/**

* Created from Copy: 2012.11.03.9.57.PM

* Run ConnectDB2LSClonePrint to read DB2 Data into Notes Documents

*

*/

import javax.faces.context.FacesContext;

import lotus.domino.NotesException;

import lotus.domino.local.Database;

/**

* @author Dököll Solutions, Inc.

* @version 2012.11.03.9.57.PM

*

*/

//...

public class RunDB2LotusScriptBean {

//...

//Xpages button code

public void doRunDB2ForNotesData() throws NotesException {

//get the current database being used

Database database= (Database) FacesContext.getCurrentInstance()

.getApplication().getVariableResolver()

.resolveVariable(FacesContext.getCurrentInstance(), "database");

System.out.println("Database Obtained..." + database);

//Run the LotusScript agent to Access DB2 Data

database.getAgent("ConnectDB2LSClonePrint").runOnServer();

System.out.println("ConnectDB2LSClonePrint process begins...");

}

}

Run LotusScript for DB2 Data with Xpages

You have a connector class and a JavaBean rigged to submit and retrieve data from DB2 database (DOMINODB), but you need to run code and perform tasks... Plug your JavaBean's method into the following Xpages form button, and check log.nsf database for the results; see previous screenshots to get an idea how this might look. Copy and paste to your environement, areas of interest have been highlighted for your convenience.

xprunlotusscriptfordb2.xsp;

<?xml version="1.0" encoding="UTF-8"?>

<xp:view xmlns:xp="http://www.ibm.com/xsp/core">

<xp:button value="Run DB2" id="button1">

<xp:eventHandler event="onclick" submit="true"

refreshMode="complete" immediate="false" save="true"

id="eventHandler1">

<xp:this.action><![CDATA[#{javascript:RunDB2LotusScriptBean.doRunDB2ForNotesData()}]]></xp:this.action>

</xp:eventHandler>

</xp:button>

</xp:view>

Conclusion:

You can now use an Xpages form button to connect to DB2 database via LotusScript to retrieve data from DOMINODB via SQL and submit to NSF back-end as Documents.

Questions, comments, please post a brief message on our Contact form on the main site.

Thank you for coming...