Manage Learn to apply best practices and optimize your operations.

Sychronizing Notes Names to External RDBMS

Many times when we need to integrate a Notes application with an RDBMS, we need to maintain a match between the notes users and user table in the external database. To accomplish this for my project, I used LCSLX connector classes to synchronize Notes users table with a user info table in an Oracle database. The following script has been put in a scheduled to run daily agent and has been configured to connect to an Oracle database. The database name, user login and password are being picked up from a profile document being maintained in the database.

'Export NOTESID Using Connectors
Option Public
Option Explicit
Uselsx "*lsxlc"

Public Const XFER_NAME_MAPPING = 2
Public Const XFER_SESSION_NAME = "Testing Script"
Public Const XFER_SOURCE_CONNECTION = "notes"
Public Const XFER_TARGET_CONNECTION = "oracle"
Public Const XFER_SOURCE_METADATA = "person"
Public Const XFER_RECORD_COUNT = 1
'The transfer statement is being used to filter the data that we have to put in Oracle and I am using it to validate the employee ID which is being stored in the ShortName field
Public Const XFER_STATEMENT = "SELECT Form='Person' & @Length(ShortName)=8 & @IsAvailable(EmployeeId)"
'Each time I am moving data to Oracle I am overwriting it
Public Const XFER_OVERWRITE = 1
Public Const XFER_CREATE = 0

Sub Initialize
On Error Goto LSErrorLabel
Dim session As New LCSession( )

Dim record_count As Long
Dim count As Long
Dim src_namelist As String
Dim targ_namelist As String
Dim stream As New LCStream
Dim fieldlist As LCFieldlist
Dim mapped_fieldlist As LCFieldList
Dim empid As New LCField(LCTYPE_TEXT, 1)
Dim fullname As New LCField(LCTYPE_TEXT,1)
Dim pdoc As notesdocument
Dim db As notesdatabase
Dim sess As New notessession
' Load the connections
Set db=sess.currentdatabase
'I am ensuring that this agent does not run in my template database
If Right(db.Filename, 3)="ntf" Then
Exit Sub
End If
Set pdoc=db.getprofiledocument("Profile") Dim src_connect As New LCConnection (XFER_SOURCE_CONNECTION) Dim targ_connect As New LCConnection (XFER_TARGET_CONNECTION) targ_connect.server = pdoc.Host_String_xt(0) targ_connect.UserID = pdoc.User_Name_xt(0) targ_connect.Password=pdoc.Password_xt(0) 'Getting the Domino Directory for export src_connect.server= db.server src_connect.database="names.nsf" src_connect.metadata = XFER_SOURCE_METADATA targ_connect.metadata = pdoc.User_Name_xt(0)+".DOMINO_USERS_TABL"

' Connect to both database components

' create fieldlist and Execute statement and result set

record_count = XFER_RECORD_COUNT
If (record_count = 0) Then
record_count = 1
End If
Set fieldlist = New LCFieldlist (record_count, XFER_TRUNCATION_FLAGS)

count = src_connect.Execute (XFER_STATEMENT, fieldlist)

' If the overwrite option is selected, truncate the target data
' if there is no data to be truncated this command will do nothing
targ_connect.Action LCACTION_TRUNCATE
End If

' This was a successful execution that produced no data
' consider it completed successfully
If ((count = 0) Or ((count = LCCOUNT_UNKNOWN) And (fieldlist.FieldCount = 0))) _ Goto ResumeLabel

' If there is no need for field name mapping, then the result set
' is used for both the source list and the target list
' however, If you require field name mapping see the section that follows
Set mapped_fieldlist = fieldlist
' Set the property map by name for the target to 1
End If

' this section is for creating a mapping of fields by name
' Set the property map by name for the source and target to 1

' These are the field names in the Person form in names.nsf
src_namelist = "FirstName, LastName, MiddleInitial, ShortName, FullName, dspEmployeeID"

'set the property in the source connection now using the list just built src_connect.FieldNames = src_namelist

'now do the target fields which are in the Oracle table

Set mapped_fieldlist = New LCFieldList(0,0)
Call mapped_fieldlist.MapName (fieldlist, src_namelist, targ_namelist)
End If ' end of name mapping section

' Loop through load-store sequence until an error occurs

' Load first N records from the source
count = src_connect.Fetch (fieldlist, 1, record_count)
While (count > 0)
' Store N records in destination
Set empid=fieldlist.Lookup("dspEmployeeId", 1)
' Setting the employee id by extracting it from ShortName field empid.text=Right(fieldlist.Lookup("ShortName").text(0), 6)
Set fullname=fieldlist.Lookup("FullName", 1)
' Setting the full notes name using the first name, middle intial and last name If fieldlist.Lookup("MiddleInitial").text(0)="" Then fullname.text=Trim(fieldlist.Lookup("FirstName").text(0)+" "+fieldlist.Lookup("LastName").text(0))
fullname.text=Trim(fieldlist.Lookup("FirstName").text(0)+" "+fieldlist.Lookup("MiddleInitial").text(0)+_ " "+fieldlist.Lookup("LastName").text(0))
End If
If Len(fieldlist.Lookup("FullName").text(0))<=75 And Isnumeric(empid.text(0))
' If full name length is less than 75 then inserting it into the Oracle table targ_connect.Insert mapped_fieldlist, 1, count
End If
' Load next N records from the source
count = src_connect.Fetch (fieldlist, 1, record_count)

Goto ResumeLabel
' ---------------------------------------------------------------------------------------------
' here we handle any LotusScript errors
Dim MessageString As String
MessageString = Error$ & " at line " & Cstr(Erl())

If (Session Is Nothing) Then
Print MessageString
' if we never created a lcsession context it is likely
' that LEI has not been correctly installed
Print "LEI may not be correctly installed or configured."
Dim Msg As String
Dim MsgCode As Long
session.GetStatus MessageString, MsgCode, Msg
If MessageString="" Then
Print Error$
Print MessageString
End If
End If
If Instr(MessageString, "unique constraint")>0 Then
Print "User Name: "+fullname.text(0)+" Employee ID: "+empid.text(0)
Resume Next
End If
Resume ResumeLabel

' -----------------------------------------------------------------------------------------------
' done
' all object cleanup occurs automatically
End Sub

Dig Deeper on Domino Resources - Part 5

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.




  • iSeries tutorials'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 ...