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_TRUNCATION_FLAGS = LCFIELDF_TRUNC_PREC
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
src_connect.Connect
targ_connect.Connect

' 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 (XFER_OVERWRITE) Then
' 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 (XFER_NAME_MAPPING <> 2 ) Then
' 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
If (XFER_NAME_MAPPING =1 ) Then
' Set the property map by name for the target to 1
targ_connect.mapbyname="1"
End If

Else
' this section is for creating a mapping of fields by name
' Set the property map by name for the source and target to 1
src_connect.mapbyname="1"
targ_connect.mapbyname="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
targ_namelist = "NOTES_FIRST_NAME, NOTES_LAST_NAME, NOTES_MIDDLE_INITIAL, "+_ "NOTES_SHORT_NAME, NOTES_FULL_NAME, NOTES_EMPLOYEE_ID"

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))
Else
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))
Then
' 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)
Wend

targ_connect.Disconnect
src_connect.Disconnect
Goto ResumeLabel
' ---------------------------------------------------------------------------------------------
' here we handle any LotusScript errors
LSErrorLabel:
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."
Else
Dim Msg As String
Dim MsgCode As Long
session.GetStatus MessageString, MsgCode, Msg
If MessageString="" Then
Print Error$
Else
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

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

This was first published in November 2000

Dig deeper on Domino Resources - Part 5

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchWinIT

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 ...

SearchEnterpriseLinux

SearchVirtualDataCentre.co.uk

Close