varvoura
asked on
LS/ODBC agent
Hi all,
I have a lotusscript agent imports almost 20000 records from an external data source into a lotus notes database and that's working very well.
However, the data in any of those imported records changes frequently in the external database, so I am having to delete all those records from my lotus database and import them again every night which is cumbersome and I am sure inefficient for the database.
Now I have gone through similar path with Marylin to whom I am very greatful because she has tried to help with one of those tough agents at one stage, but that agent we worked on had static old records, nothing changed expect the daily data which was imported.
In this agent most fields in the records may need to change, product id, supplier, etc, and the change may need to happen on old and new records so doing a checking while import on every field on 20,000 record I believe maybe another nightmare.
It would really appreciate some ideas/ direction on the best way to change this agent so that I don't have to delete and reimport ever record into the database every night.
Thank you all in advance.
Varvoura
I have a lotusscript agent imports almost 20000 records from an external data source into a lotus notes database and that's working very well.
However, the data in any of those imported records changes frequently in the external database, so I am having to delete all those records from my lotus database and import them again every night which is cumbersome and I am sure inefficient for the database.
Now I have gone through similar path with Marylin to whom I am very greatful because she has tried to help with one of those tough agents at one stage, but that agent we worked on had static old records, nothing changed expect the daily data which was imported.
In this agent most fields in the records may need to change, product id, supplier, etc, and the change may need to happen on old and new records so doing a checking while import on every field on 20,000 record I believe maybe another nightmare.
It would really appreciate some ideas/ direction on the best way to change this agent so that I don't have to delete and reimport ever record into the database every night.
Thank you all in advance.
Varvoura
One more word for the history: the history is the field change history. So when my upper composite field record is not matching the RACF side compare record, then I go field by field and store the old and new value for every changed field, but only for changed fields. That change hostory log is a log that I cannot get from outside. Therefore I have to keep my user documents or at least store an archived copy for the change history. Of course that log could be kept also in log documents or event in external log database, but by that way I have al user relavant informations in one place without aditional aganets or views to collect the history.
ASKER
OK, since I am not an expert, I'll have to go through this with you step by step to really understand the process.
1)Where do you set a time stamp, and how? in the agent? on those documents that you want to import again?
2)Can I do something similar to this in lotus notes? maybe a lookup view which store all the productId or whatever field that's suppose to be unique in the external database. and if a productid does not exist then create the entire record in notes, otherwise wait?
3)OK, a bit confused here, so sorry.
Are you storing beside every field (which was imported from external source) a composite key to identify whether on not that specific field has been changed?
I don't understand this too well
4) I don't need to archive or delete any, do I still need this timestamp on documents
Yup it is an overkill and especially when this database has to replicate to other servers too and that's why we're not forced to find a better way to do this.
I think if you explain a bit more about your composite key, i'll be able to understand better your last comment.
Thanks heaps
1)Where do you set a time stamp, and how? in the agent? on those documents that you want to import again?
2)Can I do something similar to this in lotus notes? maybe a lookup view which store all the productId or whatever field that's suppose to be unique in the external database. and if a productid does not exist then create the entire record in notes, otherwise wait?
3)OK, a bit confused here, so sorry.
Are you storing beside every field (which was imported from external source) a composite key to identify whether on not that specific field has been changed?
I don't understand this too well
4) I don't need to archive or delete any, do I still need this timestamp on documents
Yup it is an overkill and especially when this database has to replicate to other servers too and that's why we're not forced to find a better way to do this.
I think if you explain a bit more about your composite key, i'll be able to understand better your last comment.
Thanks heaps
1.) The initial timestamp is the time value in a local var in the replication Agent. The lookup timestamp is set in every looked up or new created document as document field value. Deleted are all document with that lookup timestamp field value older the Agent lookup run starting initial time.
2.) I described that I search for the primary key in lotus notes view. If not found, then I create new document. I do not understand the wait you stated at end. Wait for what?
3.) No. I do not store for a field a comapre value. I store a value mask for all fields. Here an example:
Field Name: RACFDATA
Data Type: Text List
Data Length: 518 bytes
Seq Num: 215
Dup Item ID: 0
Field Flags: SUMMARY
"UID=NA2PAN;Zvonko;FULL;XI
The composite key is not realy neccessary if you not want to observe chnage history log. Then you can store all field values at every store. But...
4.) You definitely need that timestamp. Otherwise you do not know which dataset to delete because they was existant in external database but now are deleted in external database. You can delete all local documents which are NOT updated at this run.
So simplified for your scenario is your agent someting like this:
Sub Initialize
Dim StartTime As New NotesDateTime( "" )
Call StartTime.SetNow
Set session = New NotesSession
Set db = session.CurrentDatabase
Set UserView = db.GetView("($User)")
'// get every record from your external source
'// and compare the primary key to you notes view
Do While " new Records comming "
UserID = "your primary key"
Set doc = UserView.GetDocumentByKey(
If doc Is Nothing Then
Set doc = db.CreateDocument
doc.Form = "UserForm"
End If
doc.LastCheck = Now
' // set all document fields from record to document
Call doc.Save( True, False )
Loop
Set doc = UserView.GetFirstDocument
While Not ( doc Is Nothing )
Set nextdoc = UserView.GetNextDocument( doc )
Set docRemoved = doc.GetFirstItem( "Removed" )
If (docRemoved Is Nothing) Then
Set LastCheck = doc.GetFirstItem( "LastCheck" )
If Not( LastCheck Is Nothing) Then
If (StartTime.TimeDifference(
Msgbox "UserID deleted: " & doc.UserID(0)
Call doc.Remove( True )
End If
End If
End If
Set doc = nextdoc
Wend
Call UserView.Refresh
2.) I described that I search for the primary key in lotus notes view. If not found, then I create new document. I do not understand the wait you stated at end. Wait for what?
3.) No. I do not store for a field a comapre value. I store a value mask for all fields. Here an example:
Field Name: RACFDATA
Data Type: Text List
Data Length: 518 bytes
Seq Num: 215
Dup Item ID: 0
Field Flags: SUMMARY
"UID=NA2PAN;Zvonko;FULL;XI
The composite key is not realy neccessary if you not want to observe chnage history log. Then you can store all field values at every store. But...
4.) You definitely need that timestamp. Otherwise you do not know which dataset to delete because they was existant in external database but now are deleted in external database. You can delete all local documents which are NOT updated at this run.
So simplified for your scenario is your agent someting like this:
Sub Initialize
Dim StartTime As New NotesDateTime( "" )
Call StartTime.SetNow
Set session = New NotesSession
Set db = session.CurrentDatabase
Set UserView = db.GetView("($User)")
'// get every record from your external source
'// and compare the primary key to you notes view
Do While " new Records comming "
UserID = "your primary key"
Set doc = UserView.GetDocumentByKey(
If doc Is Nothing Then
Set doc = db.CreateDocument
doc.Form = "UserForm"
End If
doc.LastCheck = Now
' // set all document fields from record to document
Call doc.Save( True, False )
Loop
Set doc = UserView.GetFirstDocument
While Not ( doc Is Nothing )
Set nextdoc = UserView.GetNextDocument( doc )
Set docRemoved = doc.GetFirstItem( "Removed" )
If (docRemoved Is Nothing) Then
Set LastCheck = doc.GetFirstItem( "LastCheck" )
If Not( LastCheck Is Nothing) Then
If (StartTime.TimeDifference(
Msgbox "UserID deleted: " & doc.UserID(0)
Call doc.Remove( True )
End If
End If
End If
Set doc = nextdoc
Wend
Call UserView.Refresh
Sorry, that Removed item is confusing in your scenario.
Look better at this:
Sub Initialize
Dim StartTime As New NotesDateTime( "" )
Call StartTime.SetNow
Set session = New NotesSession
Set db = session.CurrentDatabase
Set UserView = db.GetView("($User)")
'// get every record from your external source
'// and compare the primary key to you notes view
Do While " new Records comming "
UserID = "your primary key"
Set doc = UserView.GetDocumentByKey(
If doc Is Nothing Then
Set doc = db.CreateDocument
doc.Form = "UserForm"
End If
doc.LastCheck = Now
' // set all document fields from record to document
Call doc.Save( True, False )
Loop
Set doc = UserView.GetFirstDocument
While Not ( doc Is Nothing )
Set nextdoc = UserView.GetNextDocument( doc )
Set LastCheck = doc.GetFirstItem( "LastCheck" )
If Not( LastCheck Is Nothing) Then
If (StartTime.TimeDifference(
Msgbox "UserID deleted: " & doc.UserID(0)
Call doc.Remove( True )
End If
End If
Set doc = nextdoc
Wend
Call UserView.Refresh
Look better at this:
Sub Initialize
Dim StartTime As New NotesDateTime( "" )
Call StartTime.SetNow
Set session = New NotesSession
Set db = session.CurrentDatabase
Set UserView = db.GetView("($User)")
'// get every record from your external source
'// and compare the primary key to you notes view
Do While " new Records comming "
UserID = "your primary key"
Set doc = UserView.GetDocumentByKey(
If doc Is Nothing Then
Set doc = db.CreateDocument
doc.Form = "UserForm"
End If
doc.LastCheck = Now
' // set all document fields from record to document
Call doc.Save( True, False )
Loop
Set doc = UserView.GetFirstDocument
While Not ( doc Is Nothing )
Set nextdoc = UserView.GetNextDocument( doc )
Set LastCheck = doc.GetFirstItem( "LastCheck" )
If Not( LastCheck Is Nothing) Then
If (StartTime.TimeDifference(
Msgbox "UserID deleted: " & doc.UserID(0)
Call doc.Remove( True )
End If
End If
Set doc = nextdoc
Wend
Call UserView.Refresh
ASKER
Zvonko,
Great, I'll try your idea today and we'll let you kow how I go
call doc.remove(True), I presume that you are removing the documents when the last checkdate has a different value than the start time.
Is this it?
Great, I'll try your idea today and we'll let you kow how I go
call doc.remove(True), I presume that you are removing the documents when the last checkdate has a different value than the start time.
Is this it?
Different is not enough.
If the difference is Positive, then is the document time stamp ("LastCheck") older then agent run start timestamp ("StartTime"). That mean, the document was not updated and is candidate for deletion.
Here is that difference calculation line:
If (StartTime.TimeDifference(
'// delete because it is last checked older then this run start time
If the difference is Positive, then is the document time stamp ("LastCheck") older then agent run start timestamp ("StartTime"). That mean, the document was not updated and is candidate for deletion.
Here is that difference calculation line:
If (StartTime.TimeDifference(
'// delete because it is last checked older then this run start time
ASKER
Yes, i understood the line, but I was rather concern about the remove bit.
Anyway, let me try to see how you go.
Thanks a million
Anyway, let me try to see how you go.
Thanks a million
ASKER
Almost working.
1)First I create a small database & a table in microsoft access
2)set the primary key in table
3) then create a field LastCheck on the form(editable)
4)modified my agent to accomodate changes recommended by you
5)then created a document in notes database so that I don't have a problem with "object variable not set" when I try to match the external source primary key with a field no on the form
6) ran the agent manually and here's the result
a) On the first import when the agent runs, it seems to delete the first record in the notes view and imports the others(obviously because the primary key doens't find a match) but why is the first document deleted from the view, no changes were made to it externally so by right it should remain intact in the view
b)Then when i ran the agent again, that document is imported(for the same reason above)
c) however, when I change fields on my external source records and reimport again, nothing gets imported and the fields in my lotus notes remain with the old field values which are different than those in the datasource.
Dim Con As New ODBCConnection
Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim nextDoc As NotesDocument
dim nstatus as integer
nstatus = Con.ConnectTo("","newdb", "newtable")
If nstatus = False Then
Msgbox "Unable to connect to database, please see your network administrator"
Exit Sub
End If
Dim Qry As New ODBCQuery
Set Qry.Connection = HOCon
Qry.SQL = "select * FROM newtable"
Dim res As New ODBCResultSet
Set Res.Query = Qry
Res.CacheLimit = 1000
Res.FetchBatchSize = 1000
Res.MaxRows = 0
Res.Execute
'-- Get First Row in ResultSet
nstatus = Res.FirstRow
If nstatus = True Then
Dim StartTime As New NotesDateTime( "" )
Call StartTime.SetNow
Set db = session.CurrentDatabase
Set view = db.GetView("notesview")
Dim doc1 As NotesDocument
Set doc1 = session.DocumentContext
Dim nunique As String
nunique=doc1.field1(0) ' i am using this later to set it = to the primary key of external source
End If
Do While nstatus = True
doc1.field1 = Res.GetValue("field1")
Set doc1 = view.GetDocumentByKey(nuni
If doc1 Is Nothing Then
Set doc1 = db.CreateDocument
Doc1.form = "notesform"
End If
'-- Set form item
' Doc1.form = "notesform"
doc1.LastCheck = Now
Call doc1.Save( True, False )
Doc1.field1 = Cstr( Res.GetValue("field1") )
Doc1.field2 = Cstr( Res.GetValue("field2") )
Doc1.field3 = Cstr( Res.GetValue("field3") )
Doc1.field4 = Cstr( Res.GetValue("field4") )
doc1.fieldn = Cstr(Res.GetValue("fieldn"
Call doc1.ComputeWithForm(False
Call doc1.save (True, True)
nstatus = Res.NextRow
Loop
Set doc1 = view.GetFirstDocument
While Not ( doc1 Is Nothing )
Set nextdoc = view.GetNextDocument( doc1 )
Set LastCheck = doc1.GetFirstItem( "LastCheck" )
If Not( LastCheck Is Nothing) Then
If (StartTime.TimeDifference(
Call doc1.Remove( True )
End If
End If
Set doc1 = nextdoc
Wend
Call view.Refresh
nstatus = Res.Close( DB_CLOSE )
nstatus = Con.Disconnect
Could the reason be because i am running the agent manually?
Are you able to test my code from your end?
This is a simple example, but the real one is much more complex, but conceptually it is the same.
Thank you
1)First I create a small database & a table in microsoft access
2)set the primary key in table
3) then create a field LastCheck on the form(editable)
4)modified my agent to accomodate changes recommended by you
5)then created a document in notes database so that I don't have a problem with "object variable not set" when I try to match the external source primary key with a field no on the form
6) ran the agent manually and here's the result
a) On the first import when the agent runs, it seems to delete the first record in the notes view and imports the others(obviously because the primary key doens't find a match) but why is the first document deleted from the view, no changes were made to it externally so by right it should remain intact in the view
b)Then when i ran the agent again, that document is imported(for the same reason above)
c) however, when I change fields on my external source records and reimport again, nothing gets imported and the fields in my lotus notes remain with the old field values which are different than those in the datasource.
Dim Con As New ODBCConnection
Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim nextDoc As NotesDocument
dim nstatus as integer
nstatus = Con.ConnectTo("","newdb", "newtable")
If nstatus = False Then
Msgbox "Unable to connect to database, please see your network administrator"
Exit Sub
End If
Dim Qry As New ODBCQuery
Set Qry.Connection = HOCon
Qry.SQL = "select * FROM newtable"
Dim res As New ODBCResultSet
Set Res.Query = Qry
Res.CacheLimit = 1000
Res.FetchBatchSize = 1000
Res.MaxRows = 0
Res.Execute
'-- Get First Row in ResultSet
nstatus = Res.FirstRow
If nstatus = True Then
Dim StartTime As New NotesDateTime( "" )
Call StartTime.SetNow
Set db = session.CurrentDatabase
Set view = db.GetView("notesview")
Dim doc1 As NotesDocument
Set doc1 = session.DocumentContext
Dim nunique As String
nunique=doc1.field1(0) ' i am using this later to set it = to the primary key of external source
End If
Do While nstatus = True
doc1.field1 = Res.GetValue("field1")
Set doc1 = view.GetDocumentByKey(nuni
If doc1 Is Nothing Then
Set doc1 = db.CreateDocument
Doc1.form = "notesform"
End If
'-- Set form item
' Doc1.form = "notesform"
doc1.LastCheck = Now
Call doc1.Save( True, False )
Doc1.field1 = Cstr( Res.GetValue("field1") )
Doc1.field2 = Cstr( Res.GetValue("field2") )
Doc1.field3 = Cstr( Res.GetValue("field3") )
Doc1.field4 = Cstr( Res.GetValue("field4") )
doc1.fieldn = Cstr(Res.GetValue("fieldn"
Call doc1.ComputeWithForm(False
Call doc1.save (True, True)
nstatus = Res.NextRow
Loop
Set doc1 = view.GetFirstDocument
While Not ( doc1 Is Nothing )
Set nextdoc = view.GetNextDocument( doc1 )
Set LastCheck = doc1.GetFirstItem( "LastCheck" )
If Not( LastCheck Is Nothing) Then
If (StartTime.TimeDifference(
Call doc1.Remove( True )
End If
End If
Set doc1 = nextdoc
Wend
Call view.Refresh
nstatus = Res.Close( DB_CLOSE )
nstatus = Con.Disconnect
Could the reason be because i am running the agent manually?
Are you able to test my code from your end?
This is a simple example, but the real one is much more complex, but conceptually it is the same.
Thank you
Did you try to Trace the Agent?
ASKER
Yes I have.
All good now except one small problem.
when I have the first record selected in the notes view and click to run the agent manually, the first record gets deleted, then when I run the agent again, this record is reimported into the notes database.
The funny thing is that when i select the second record in the view,it all runs fine and the first document doesn't get deleted.
I think this has something to with the way that I am initially setting doc1 = session.documentcontext but that's the only way I could do this to set the primary key to a value in the notes document.
Are you able to advise on this one?
All good now except one small problem.
when I have the first record selected in the notes view and click to run the agent manually, the first record gets deleted, then when I run the agent again, this record is reimported into the notes database.
The funny thing is that when i select the second record in the view,it all runs fine and the first document doesn't get deleted.
I think this has something to with the way that I am initially setting doc1 = session.documentcontext but that's the only way I could do this to set the primary key to a value in the notes document.
Are you able to advise on this one?
ASKER
Even when I change the setting doc1 to set doc1 = view.getfirstdocument I still have the problem of the first record deleted on first run.
ASKER
NOTES HELP - remove methods on the Document object returned by DocumentContext,
I think that this is where the problem is, how can I avoid it?
Plus this agent usually runs in the background, I am only running this manually because I don't have access to the server.
I think that this is where the problem is, how can I avoid it?
Plus this agent usually runs in the background, I am only running this manually because I don't have access to the server.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
All working fine, thank you so much for your ideas and help.
I was overwritten the document context with the create document, one I fixed this it all worked fine. Still try to run it manually.
I tried your local scheduled agent idea but I seem to be always asked for authentication for localhost.
The points are finally yours
I was overwritten the document context with the create document, one I fixed this it all worked fine. Still try to run it manually.
I tried your local scheduled agent idea but I seem to be always asked for authentication for localhost.
The points are finally yours
ASKER
....If I delete a document in the external data source and run the agent, the record dosen't get deleted from the notes view. How can I can this to update correctly?
It doesn't seem to have a problem with modification.
It doesn't seem to have a problem with modification.
Then is something in time stamp compare going wrong.
Add a view refresh please:
Call view.Refresh
Set doc1 = view.GetFirstDocument
While Not ( doc1 Is Nothing )
Set nextdoc = view.GetNextDocument( doc1 )
Set LastCheck = doc1.GetFirstItem( "LastCheck" )
If Not( LastCheck Is Nothing) Then
If (StartTime.TimeDifference(
Call doc1.Remove( True )
End If
End If
Set doc1 = nextdoc
Wend
Call view.Refresh
Also look into Document items for the value set in LastCheck.
Add a view refresh please:
Call view.Refresh
Set doc1 = view.GetFirstDocument
While Not ( doc1 Is Nothing )
Set nextdoc = view.GetNextDocument( doc1 )
Set LastCheck = doc1.GetFirstItem( "LastCheck" )
If Not( LastCheck Is Nothing) Then
If (StartTime.TimeDifference(
Call doc1.Remove( True )
End If
End If
Set doc1 = nextdoc
Wend
Call view.Refresh
Also look into Document items for the value set in LastCheck.
ASKER
I already have view refresh in my script, so this wouldn't be the problem.
I'll check lastcheck field on document.
But if the lastcheck property is not populating correctly in the documents, why then I don't have any problems updating the fields in the view when they are changed in the ODBC source.
I'll check lastcheck field on document.
But if the lastcheck property is not populating correctly in the documents, why then I don't have any problems updating the fields in the view when they are changed in the ODBC source.
1.) Before I start the compare (the replication with RACF), I set a start time stamp.
2.) Every user from RACF is searched in Domino database userid table. That is my primary key. If such document does not exist is a new user document is created.
3.) I store beside all fields used in the user document also a "compare" composite field. That field is the combination of all relevant field values which define userid as changed. For example, the last logon time value is not such a change value. With that one composite field I can compare at once wether my record changed or not.
4.) For every document, new one or changed one, I set the new timestamp for last looking in that document, even wen nothing changed. That is for delete purposes. Documents with that last lookup timestamp older then my initial timestamp are not looked up and therefore no more in the external RACF repository. They can be deleted or archived.
That sounds like an overhead to delete all and create all fresh. But deleting and creating documents is from my experiance more costintensive operation especialy when you have many views on that documents. Also the onDiskStructure of the database is colapsing and expanding. So basicaly is my expression that comparing is less expensive then deleting and creating. And when you want to store some history informations which you cannot get from external source, then you have no choice; you have to do the upper compare and update.