Avoid same record get generate again.

I'm able to pull a set of records from Oracle DB by using script in a button.
I let user to press the button when ever there is any new record in Oracle DB.
How can I make sure those previous records already exist in Notes will not generate again in Notes;
ie, how to avoid same record get generate again after pulling from Oracle DB by using script.
As I do not have control over Oracle DB site, I only can put the control in Notes.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

how about keeping track of the number of records in the oracle db (say in an environment variable), and if the number increased get the latest records.
jiayilooAuthor Commented:
How to get the latest record ??
This function might be done by a few person at different time.
like I said, if u have this DB on the server, and u created an environment variable it will be stored in the server's notes.ini file.  Lets say its called "Latest=40" then the next time u will get records starting from the 40th record up till the last record which for example could be 52nd record, then u will change the environment variable to "Latest=52", and so on.  Get it?  This i control is only in notes.
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

jiayilooAuthor Commented:
What if the new record is in between 1 to 40 ?
The new list from 41 to 52 may contain some old/existing records. Pls advise, thks in advance.
Understanding the logic is important:
You should have an unique key in that database of oracle. go to the last record which is available in oracle db, check for it in LN, it will not be available, add the record, go to second last record, do the same thing unless you find the record in LN. If you are performing from first record, lotus will take long time to finish its task.
Another way. Once a new record is added in Oracle, Keep a track of the last record (any value within a field ) entered in lotus notes into Notes.ini.( As an environmental variable) Open oracle db and get all the records after that particular value. This will be working very fast, because the search is based on oracle not LN.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thats why u should have a table in oracle that only has all values and it should be sorted ascendingly so latest will be last...
Please reject his answer as he only copied my comments, thanx
jiayilooAuthor Commented:
Sathya & ghassan:
Any sample code for the approach U guys shared??

The approach U guys share can only take care of inserted records in Oracle how about updated records??

Will increase the point when I accumulate more points & got my problem solved.
Thks in advance...
Well I was in the process of doing the same thing with two notes databases.  Two NABS.

One is primary NAB and the other is the secondary.  I cannot touch primary and I have to do all my work with the secondary.  The secondary will find only people of certain category and add a few details to the document and update in the secondary.  This has to be done everyday since the primary will be changed everyday.

You can take this code replace the primary with your Oracle and go ahead.  But this will be time consuming but no other way,

Agent Script triggered by NotesPump.

Dim session As NotesSession

Dim Refdb As NotesDatabase
Dim Tardb As NotesDatabase

Dim Refview As NotesView
Dim Tarview As NotesView
Dim peopleview As NotesView

Dim Refdoc As NotesDocument
Dim Tardoc As NotesDocument
Dim Newdoc As NotesDocument
Dim deldoc As NotesDocument
Dim serdoc As Notesdocument

Dim dcnew As NotesDocumentCollection
Dim dcold As NotesDocumentCollection

Dim deletecount As Double
Dim addcount As Double
Dim modcount As Double    
Dim delta As Double

Dim rndval As String
Dim Errorflag As String
Dim Refname As String
Dim RACF As String
Dim AgentName As String
Dim g As String
Dim ShortName As String
Dim key As String

'To take the first document creation time.
Dim DateTimeFlag As Variant
Dim DTFlg As String

Dim k As Variant
Dim success As Variant

Dim Pwditem  As NotesItem
Dim Fnitem As NotesItem
Dim rnditem As NotesItem

' For the use of Function
Dim vpass As String
Dim lpos As Integer
Dim totl As Integer
Dim rpos As Integer

'NotesPump Declaration
Dim Activity As NPActivity

'Maildoc Sub
Dim maildoc As notesdocument
Dim richstyle As NotesRichTextStyle
Dim rtitem As NotesRichTextItem    

'Old Deletion
Dim dateTime1 As NotesDateTime

Initialize event

 AgentName = "Update NAB Agent"
     Set Activity = New NPActivity ( 0, AgentName )
     LogMessage ( "Starting - " + AgentName  & " at  -- " & Now())
     Set Refdb = New NotesDatabase("","names.nsf") ' Reference Database
     Set Tardb = New NotesDatabase("","fahpnames.nsf") ' Target Database
     Set Refview = Refdb.GetView("($People)") ' Reference view to walk-through -- Adding to TarDb
     Set peopleview = Tardb.GetView("($People)")
     Set Tarview = Tardb.GetView("($ByKey)") ' Target view to -- Delete from TarDb
     Set Refdoc = Refview.GetFirstDocument          
     ErrorFlag = ""         
     On Error Goto ErrorHandler
     g = Cstr(Now)
     addcount = 0
     modcount = 0
     DTFlg = ""    ' Flag to check the First document Modification / Created Date
     ' This LOOP is to traverse through the Reference view to pickout documents that are pertaining
     ' to the Prudential Securities and create similar documents in the Target database.          
     Do While (Not Refdoc Is Nothing)                                                                                
          If Refdoc.HasItem("$Conflict") Or Trim(Refdoc.mailfile(0)) = "" Then Goto jumper    ' Skip the Documents that are Conflicts as well as Documents with no RACF
          If Ucase(Right(RefDoc.fullname(0),20)) = Ucase("/OU=PSG/O=Prudential") Then ' Found a document for Prudential Securities.
               RACF = GetRACF(Refdoc.mailfile(0))                                                                   ' Function to get the RACF id number
               ShortName = Refdoc.ShortName(0)                                                                    ' To get the Short Name value and form the key to get the Target Document
               key = RACF & ShortName
               Set Tardoc = Tarview.GetDocumentByKey(key)  
               If Tardoc Is Nothing Then
                    Set Newdoc = Tardb.CreateDocument      
                    Call Refdoc.CopyAllItems( Newdoc , True )                    
                    Set Pwditem = NewDoc.ReplaceItemValue( "HTTPPassword", RACF & "NOTES")
                    Set Fnitem = NewDoc.GetFirstItem( "Fullname" )
                    Call Fnitem.AppendToTextList( RACF )
                    success = Newdoc.ComputeWithForm( False, False )                                         ' Reset the HTTP password
                    Call NewDoc.Save(True, True)
     ' This statement is to have the datetime in a variable so that the documents that are created prior to this dateTime can be deleted
     ' if the script is completely successfull
                    If Trim(DTFlg) = "" Then
                         DateTimeFlag = Newdoc.Created
                         DTFlg = "Affected"                  
                    End If
                    addcount = addcount + 1                                                                                     ' Counter to have Addition count    
                    Set Pwditem = TarDoc.ReplaceItemValue( "HTTPPassword", RACF & "NOTES")                    
                    Set Fnitem = TarDoc.ReplaceItemValue( "Fullname", Refdoc.FullName )
                    Call Fnitem.AppendToTextList( RACF )
                    success = Tardoc.ComputeWithForm( False, False )                                         ' Refresh the HTTP password so that it can ve encrypted
                    Call TarDoc.Save(True, True)                      
     ' This statement is to have the time in a variable so that the documents that are modified prior to this dateTime can be deleted
     ' if the script is completely successfull
                    If Trim(DTFlg) = "" Then
                         DateTimeFlag = Tardoc.LastModified
                         DTFlg = "Affected"                  
                    End If
                    modcount = modcount + 1                                                                                    ' Counter to have Modification count    
               End If                    
          End If                                        
jumper:                                                                                                                                      'To skip the conflict documents and documents that dont have mailfile
          Set Refdoc = Refview.GetNextDocument(Refdoc)                                                  
     LogMessage ( "Documents Added --  " & addcount &" at  " & Now)    
     LogMessage ("Documents Modified --  " & modcount &"  at  " & Now)  
     ErrorFlag = "Nil"
     Call Tarview.Refresh    
     Call peopleview.Refresh
     Call Tardb.UpdateFTIndex(False)                                                                                       ' To search the db for documents modified before the specified dateTime
     Call OldDeletor
     LogMessage ("Job Completed at -- " & Now)
     Exit Sub
     If ErrorFlag <> "Nil" Then
          LogMessage ( "Error Occurred in the NAB Agent" )
          LogMessage ( "Documents Added (Till Error Occurred) --  " & addcount )    
          LogMessage ("Documents Modified (Till Error Occurred) -- " & modcount )
          LogMessage ( "Agent......Exiting............." )
          Exit Sub        
     End If          
End Sub

Sob olddeletor
     Dim formula As String
     Set dateTime1 = New NotesDateTime ("01/01/1999")
     formula = | Form = "Person"  & @Modified < [| & DateTimeFlag & |] |
     Set dcold = Tardb.Search( formula , dateTime1 , 0 )
     deletecount = dcold.count
     Call dcold.RemoveAll( True )        
     LogMessage ( "Successfull Deletion in Target DB --  " & deletecount & "  at  " & Now)                        
     If Tardb.PercentUsed < 85 Then
          LogMessage ( "Started compacting Secondary NAB at  " & Now)                            
          delta = Tardb.Compact  
          LogMessage ( "Completed compacting Secondary NAB at  " & Now)                            
          LogMessage ( "Compaction process have freed " & delta & " bytes." )
     End If

GetRACF Function
Function GetRACF(mailfile) As String
     On Error Goto errorbreak
     vpass = mailfile
     lpos = Instr(vpass, ".")
     If lpos = 0 Then
          totl = Len(vpass)
          rpos = Instr(vpass, "\")
          vpass = Right( vpass , totl  - rpos )
          lpos = lpos - 1
          vpass = Left( vpass , lpos )
          rpos = Instr(vpass, "\")
          vpass = Right( vpass , lpos-rpos )                    
     End If
     GetRACF = vpass
     Exit Function
'     Print Error$
     GetRACF = ""
     Exit Function
End Function


I dont know How far this will help you.

Keep :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.