Manage Learn to apply best practices and optimize your operations.

Smart Group Management Via Lclsx And Db\2

I use this agent to keep all my corporate management groups up to date. This
script accesses db2 via LCLSX and populates the members field of a group
document with the result of your sql query. you must use the Lotus connect LSX.
Option Public
Uselsx "*lsxlc"

Sub Initialize
Dim members () As String
Dim session As New LCSession
Dim srccon As New LCConnection ("db2")
Dim destcon As New LCConnection ("notes")
Dim namelst As String
Dim maplst As String
Dim fldLstRecord As New LCFieldList (1, LCFIELDF_TRUNC_DATA +
LCFIELDF_TRUNC_PREC)
Dim fldLstRecordMapped As New LCFieldList (1, LCFIELDF_TRUNC_DATA +
LCFIELDF_TRUNC_PREC)
Dim count As Long
Dim mb As Long
Dim wintitle As String
Dim selectionstatement As String

On Error Goto ErrorHandler
mb = MB_ICONINFORMATION + MB_OK
wintitle = "Manager Mailing list creator"

REM set the appropriate properties to connect to the data sources

srccon.Database = "db2"
srccon.Userid = "cmnb5"
srccon.Password = "1qt5x35g"

REM connect to the data source
srcCon.Connect

REM now connected, we can execute a selection sql statement
REM This sql selects user names that are current employees at a certain
level of mgmt
REM in a specific office location

selectionstatement = selectionstatement + " SELECT B.ACCESS_ID, C.LAST_NM
FROM SQLUSER.RRT105 A, SQLUSER.RRT107 B, SQLUSER.RRT101 C WHERE A.JOB_CD = '1'
AND A.CORP_CD = '010' "
selectionstatement = selectionstatement + " AND A.DEPT_CD <> '0998' and (
A.DEPT_CD LIKE '05%%' OR A.DEPT_CD LIKE '06%%' OR A.DEPT_CD LIKE '07%%'"
selectionstatement = selectionstatement + " OR A.DEPT_CD LIKE '08%%' OR
A.DEPT_CD LIKE '095%' ) AND A.PERSON_ID = B.PERSON_ID "
selectionstatement = selectionstatement + " AND A.CORP_CD = B.CORP_CD
AND A.DEPT_CD = B.DEPT_CD AND A.ACT_NO = B.ACT_NO AND A.FUNCT_NO =
B.FUNCT_NO"
selectionstatement = selectionstatement + " AND B.CLASS_CD = 'EMAIL' AND
B.TYPE_CD = 'CCMAIL' AND A.PERSON_ID = C.PERSON_ID AND C.MAINT_ID <> 'D'"
selectionstatement = selectionstatement + " ORDER BY C.LAST_NM,
B.ACCESS_ID"

count = srcCon. Execute (selectionstatement , fldLstRecord)

REM now see if we got anything in our result set
If count <> 0 Then
REM some data sources do not support an accurate record count so this
may be -1 which means unknown
Print ("The number of records selected is " & Cstr(count))
Else
Print "No records were fetched."
End If

REM fetch a record from the result set

count = srcCon.Fetch (fldLstRecord, 1, 1)

REM now insert the record into the target array and fetch the next,
looping until all noncons have been inserted

While Not (count = 0 )
Redim Preserve members(mem) As String
members(mem) = fldLstRecord.fields(0).value(0)
mem = mem +1
count = srcCon.Fetch (fldLstRecord, 1, 1)
Wend

REM open address book;Get groups view ;get doc by key;
REM set members field to array holding result returned from sql

Dim Db As New NotesDatabase( "", "" )
Call Db.Open( "Servername/org" , "names.nsf")
Set groupview=Db.getview("Groups")
Set groupdoc=groupview.getdocumentbykey("Supervisors" )
Call groupdoc.replaceitemvalue("Members", members)
Call groupdoc.computewithform (True, True)
Call groupdoc.save(False, False)
Print "Finished building Supervisors group."
End

ErrorHandler:
Dim msg As String
Dim result As String
Dim status As Integer
Dim msgcode As Long

If session.status <> LCSUCCESS Then
status = session.GetStatus (result, msgcode, msg)
Else
result = "Error " & Err() & ": " & Error()
End If
Print (result)
End
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