Tip

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.