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.
jiayilooAsked:
Who is Participating?
 
SathyaCSSNarayanaConnect With a Mentor Commented:
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.
0
 
ghassan99Commented:
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.
0
 
jiayilooAuthor Commented:
How to get the latest record ??
This function might be done by a few person at different time.
0
Get your problem seen by more experts

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

 
ghassan99Commented:
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.
0
 
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.
0
 
ghassan99Commented:
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
0
 
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...
0
 
ArunkumarCommented:
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.

=====
Declaration
=====
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    
                   
               Else                    
                   
                    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)                                                  
         
     Loop                        
     
     
     
     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
     
ErrorHandler:
     
     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 )
     Else
          lpos = lpos - 1
          vpass = Left( vpass , lpos )
          rpos = Instr(vpass, "\")
          vpass = Right( vpass , lpos-rpos )                    
     End If
     
     GetRACF = vpass
     
     Exit Function
     
errorbreak:
'     Print Error$
     GetRACF = ""
     Exit Function
     
End Function


======================================

I dont know How far this will help you.
Anyway,

Keep :)
Arun
0
All Courses

From novice to tech pro — start learning today.