Notes/Domino 4 and 5 Forum

Hi All,

I have a Notes database with thousands of documents... When it comes to generate reports, the database becomes too slow... So we decided to upload the data to Oracle... Then using a tool users will take the reports from Oracle table...

So my main task is to create a agent to upload the data to Oracle... When this agent first runs, it needs to create a table in oracle and upload all the documents for the first time... After wards it needs to upload only the new or modified documents created after last run to existing table...

Actually I had created a agent which works fine... When this agent runs it will create a table and upload all the documents into Oracle... But when it comes to run second time, it will delete the existing table in Oracle and again upload all the documents... It should not be like that... Even I tried "All New and Modified documents since last run" option... But no luck...

Can any one check my code and give me some suggestions...

My Code:
----------

Option Public
Option Declare
Uselsx "*lsxlc"
---------
Public Const con_type = "Oracle"
Public Const con_server = "MyServer"
Public Const con_user = "Username"
Public Const con_password = "Password"
Public Const con_table = "UploadSR"
Dim src As LCConnection
Dim dest As LCConnection
-------
Sub Initialize
Dim session As New NotesSession
Dim db As NotesDatabase
Set db = session.CurrentDatabase

Set src = New LCConnection ("Notes")
src.Server = db.Server
src.Database = db.FilePath
src.Metadata = "NewMFrmSrNo"
src.Connect

Set dest = New LCConnection (con_type)
dest.Server = con_server
dest.Userid = con_user
dest.Password = con_password
dest.Metadata = con_table
dest.Connect
Stop
Call NukeTable
Stop
Call CreateTable
Stop
Call TransferData

End Sub
----------------------------------

Sub NukeTable
On Error Goto NoMetadata
Call dest.Drop (LCOBJECT_METADATA)
Print "The '" & dest.Metadata & "' table has been deleted."
Exit Sub

NoMetadata:

Resume Next
Exit Sub
End Sub
--------------------------------

Sub CreateTable
Dim fldLstRecord As New LCFieldList
Dim fld As LCField


Set fld = FldLstRecord.Append ("TextCreatedBy", LCTYPE_TEXT)
Call fld.SetFormatStream (0, 40, LCSTREAMFMT_LMBCS)

Set fld = FldLstRecord.Append ("Subject", LCTYPE_TEXT)
Call fld.SetFormatStream (0, 200, LCSTREAMFMT_LMBCS)

Set fld = FldLstRecord.Append ("Status", LCTYPE_TEXT)
Call fld.SetFormatStream (0, 40, LCSTREAMFMT_LMBCS)

Set fld = FldLstRecord.Append ("TextDepartment", LCTYPE_TEXT)
Call fld.SetFormatStream (0, 16, LCSTREAMFMT_LMBCS)

Set fld = FldLstRecord.Append ("TextSR", LCTYPE_TEXT)
Call fld.SetFormatStream (0, 16, LCSTREAMFMT_LMBCS)

Set fld = FldLstRecord.Append ("TextTitle", LCTYPE_TEXT)
Call fld.SetFormatStream (0, 300, LCSTREAMFMT_LMBCS)

Set fld = FldLstRecord.Append ("TextRf", LCTYPE_TEXT)
Call fld.SetFormatStream (0, 16, LCSTREAMFMT_LMBCS)

Call FldLstRecord.Append ("TextDtReceived", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextSRAssignedDt", LCTYPE_DATETIME)

Set fld = FldLstRecord.Append ("TextSRSponsor", LCTYPE_TEXT)
Call fld.SetFormatStream (0, 24, LCSTREAMFMT_LMBCS)

Set fld = FldLstRecord.Append ("TextSRReq", LCTYPE_TEXT)
Call fld.SetFormatStream (0, 24, LCSTREAMFMT_LMBCS)

Set fld = FldLstRecord.Append ("TextOwner", LCTYPE_TEXT)
Call fld.SetFormatStream (0, 24, LCSTREAMFMT_LMBCS)

Set fld = FldLstRecord.Append ("TextPriConPer", LCTYPE_TEXT)
Call fld.SetFormatStream (0, 24, LCSTREAMFMT_LMBCS)

Call FldLstRecord.Append ("TextUrsPlanDT", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextUrsAssDt", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextUrsSubPlanDT", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextUrSubDt", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextRFPlanDT", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextRfAssDt", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextEstRecPlanDt", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextEstRecDt", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextBizAppPlanDT", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextAppDt", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextScheRecPlanDT", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextSchRecDt", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextSchStartPlanDt", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextSchStartActDT", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextSchEndPlanDT", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextSchEndActDT", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextDesiStartPlanDT", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextDesiStartActDT", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextDevStarPlanDT", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextDevStartActDT", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextSysStartPlanDT", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextSysStartActDT", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextSysEndPlanDT", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextSysEndActDT", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextUATDt", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextUATStartActDT", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextUATEndPlanDT", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextUATEndActDT", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextProdTurnPlanDT", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextProdTurnActDT", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextClosePlanDT", LCTYPE_DATETIME)
Call FldLstRecord.Append ("TextCloseActDT", LCTYPE_DATETIME)

Set fld = FldLstRecord.Append ("TextInitEstCost", LCTYPE_TEXT)
Call fld.SetFormatStream (0, 24, LCSTREAMFMT_LMBCS)

Set fld = FldLstRecord.Append ("TextLastEstCost", LCTYPE_TEXT)
Call fld.SetFormatStream (0, 24, LCSTREAMFMT_LMBCS)

Stop

On Error LCFAIL_DUPLICATE Goto tableexists
Call dest.Create (LCOBJECT_METADATA, fldLstRecord)
Print "The '" & dest.Metadata & "' table was created."
Exit Sub

tableexists:
Print "warning: the '" & dest.Metadata & "' table exists."
Resume Next
End Sub
---------------------------------------------------

Sub TransferData
Dim fetchLst As New LCFieldList
Dim insertLst As New LCFieldList
Dim fld As LCField
Dim count As Long
Dim index As Long
Dim statement As String

If (src.Select (Nothing, 1, fetchLst) <> 0) Then
dest.MapByName = True
Dim srcNames As String
Dim destNames As String

srcNames = "TextCreatedBy, Subject, Status, TextDepartment, TextSR, " +_
"TextTitle, TextRf, TextDtReceived, TextSRAssignedDt, TextSRSponsor, " + _
"TextSRReq, TextOwner, TextPriConPer, " +_
"TextUrsPlanDT, TextUrsAssDt, TextUrsSubPlanDT, TextUrSubDt, TextRFPlanDT, TextRfAssDt, " +_
"TextEstRecPlanDt, TextEstRecDt, TextBizAppPlanDT, TextAppDt, TextScheRecPlanDT, " +_
"TextSchRecDt, TextSchStartPlanDt, TextSchStartActDT, TextSchEndPlanDT, TextSchEndActDT, " +_
"TextDesiStartPlanDT, TextDesiStartActDT, TextDevStarPlanDT, TextDevStartActDT, " +_
"TextSysStartPlanDT, TextSysStartActDT, TextSysEndPlanDT, TextSysEndActDT, " +_
"TextUATDt, TextUATStartActDT, TextUATEndPlanDT, TextUATEndActDT, TextProdTurnPlanDT, " +_
"TextProdTurnActDT, TextClosePlanDT, TextCloseActDT, TextInitEstCost, TextLastEstCost"

destnames = "TextCreatedBy, Subject, Status, TextDepartment, TextSR, " +_
"TextTitle, TextRf, TextDtReceived, TextSRAssignedDt, TextSRSponsor, " + _
"TextSRReq, TextOwner, TextPriConPer, " +_
"TextUrsPlanDT, TextUrsAssDt, TextUrsSubPlanDT, TextUrSubDt, TextRFPlanDT, TextRfAssDt, " +_
"TextEstRecPlanDt, TextEstRecDt, TextBizAppPlanDT, TextAppDt, TextScheRecPlanDT, " +_
"TextSchRecDt, TextSchStartPlanDt, TextSchStartActDT, TextSchEndPlanDT, TextSchEndActDT, " +_
"TextDesiStartPlanDT, TextDesiStartActDT, TextDevStarPlanDT, TextDevStartActDT, " +_
"TextSysStartPlanDT, TextSysStartActDT, TextSysEndPlanDT, TextSysEndActDT, " +_
"TextUATDt, TextUATStartActDT, TextUATEndPlanDT, TextUATEndActDT, TextProdTurnPlanDT, " +_
"TextProdTurnActDT, TextClosePlanDT, TextCloseActDT, TextInitEstCost, TextLastEstCost"

Call insertLst.MapName(fetchLst,srcNames,destNames)

index = 1
count = 0
While (src.Fetch (fetchLst, 1, 1) > 0)
count = count + dest.Insert (insertLst, 1, 1)
index = index + 1
Wend

Print "Transfered " & count & " records from " & src.Name & " to " &dest.Name

statement = "Alter Table uploadsr Rename Column TextCreatedBy To Created_By"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextDepartment To Department"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextSR To SR_No"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextTitle To Title"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextRf To RF_No"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextDtReceived To Date_Received"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextSRAssignedDt To SR_Assign_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextSRSponsor To SR_Sponsor"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextSRReq To SR_Req"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextOwner To SR_Owner"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextPriConPer To Primary_Contact_Person"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextUrsPlanDT To URS_Start_Plan_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextUrsAssDt To URS_Start_Act_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextUrsSubPlanDT To URS_Sub_Plan_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextUrSubDt To URS_Sub_Act_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextRFPlanDT To RF_Rec_Start_Est_Plan_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextRfAssDt To RF_Rec_Start_Est_Act_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextEstRecPlanDt To Est_Rec_Plan_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextEstRecDt To Est_Rec_Act_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextBizAppPlanDT To Biz_App_Plan_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextAppDt To Biz_App_Act_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextScheRecPlanDT To Sche_Rec_Plan_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextSchRecDt To Sche_Rec_Act_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextSchStartPlanDt To Sche_Start_Plan_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextSchStartActDt To Sche_Start_Act_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextSchEndPlanDT To Sche_End_Plan_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextSchEndActDT To Sche_End_Act_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextDesiStartPlanDT To Design_Start_Plan_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextDesiStartActDT To Design_Start_Act_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextDevStarPlanDT To Dev_Start_Plan_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextDevStartActDT To Dev_Start_Act_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextSysStartPlanDT To Sys_Test_Start_Plan_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextSysStartActDT To Sys_Test_Start_Act_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextSysEndPlanDT To Sys_Test_End_Plan_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextSysEndActDT To Sys_Test_End_Act_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextUATDt To UAT_Start_Plan_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextUATStartActDT To UAT_Start_Act_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextUATEndPlanDT To UAT_End_Plan_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextUATEndActDT To UAT_End_Act_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextProdTurnPlanDT To Prod_TOver_Plan_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextProdTurnActDT To Prod_TOver_Act_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextClosePlanDT To Close_Plan_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextCloseActDT To Close_Act_Date"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextInitEstCost To Init_Est_Cost"
Call dest.Execute(statement, insertLst)
statement = "Alter Table uploadsr Rename Column TextLastEstCost To Last_Est_Cost"
Call dest.Execute(statement, insertLst)
End If

End Sub
-------------------------------------

Note : I know that i am directly using the form name to get the documents... But i am not sure how can use a view name... Anyway, i want to upload all the data in a database... So thought it won't be a problem...

"NukeTable" will delete the existing table if any...

"CreateTable" will create the table...

"TransferData" will transfer all the data and renames the column names of the table....

I think i need to give some loops which i am not able...

I wanted to create the table and upload all the documents once and then it should upload only New or Modified documents.... Pls give me your valuable suggestion...

Thanks
Suresh