While creating and updating from ORacle to Lotus , setting a flag in Oracle as 'Y' per row

HCL Domino Domino Forum
1

Hi All,This code is creating and updating records in lotus db taking data from Oracle.

When the record is created / updated for one employee number at the same time I need to update a flag value in Oracle as 'Y" that this record is updated.

Can you please confirm if I have used the correct syntax.

Thanks in advance .

Code---------

Dim session As New NotesSession

Dim db As NotesDatabase

Dim doc,newdoc As NotesDocument

Dim ldoc As NotesDocument

Dim LogDate As Variant

Dim view As NotesView

Dim upd , newd As Long

Dim upd1 As Long	



Dim LC_S As New LCSession

Dim LC_FldLst As New LCFieldList(1)

Dim fields As New LCFieldList(1)

Dim LC_Conn As New LCConnection ("oracle")

Dim count As Long

Dim SelectStatement As String



Set session=New NotesSession

Set db = session.CurrentDatabase

Set view = db.GetView( "NRIDview_HRMS" )

'To update flag value in  ORacle  // Please check this part

Set fields = New LCFieldList

Set field1 = fields.Append("HRMSFLAG", LCTYPE_TEXT)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''

upd=0

upd1=0

newd=0



LC_S.ConnectionPooling = True

LC_S.ClearStatus

LC_Conn.Metadata = "XX_ORACLE_TO_TBL"

LC_Conn.Server = "TEST"    

LC_Conn.UserId = "apps"

LC_Conn.Password = "apps"	

LC_Conn.CommitFrequency = 0	

LC_Conn.Disconnect

LC_Conn.Connect



Print "Successfully connected to Server."	



'Excecute selection

If (LC_Conn.Execute (|SELECT * FROM XX_ORACLE_TO_TBL|, LC_FldLst) = 0) Then

	Print "No record matching query."

Else

	

	count=LC_Conn.Fetch(LC_FldLst,1,1)	

	

	While (count >0) 'And LC_S.Status=LC_Success

		Set doc = view.GetDocumentByKey(LC_FldLst.EMPLOYEE_NUMBER(0))

		If Not doc Is Nothing Then

			'Chek to be applied based on last modify incase of mismatch only update old records

			doc.Name=CStr(LC_FldLst.Name(0))

			doc.STAFF_ID=CStr(LC_FldLst.UNIQUE_ID(0))

			doc.GENDER=CStr(LC_FldLst.GENDER(0))

			

			updatedlog=	updatedlog & Chr(10)& "Record updated for - " & LC_FldLst.EMPLOYEE_NUMBER(0) & " > Staff Name - '" & doc.Name(0) 

			Call doc.save (True , False)

			upd=upd+1

			''''A flag will be set in Oracle for this row '''''// Please check this part

			field1.value = "Y"

			

		Else

			Set newdoc = db.CreateDocument  

			newdoc.Form = "ResourceNew"				

			newdoc.ALT_STAFF_ID=CStr(LC_FldLst.EMPLOYEE_NUMBER(0))

			newdoc.Name=CStr(LC_FldLst.Name(0))	

			newdoc.STAFF_ID=CStr(LC_FldLst.UNIQUE_ID(0))

			newdoc.GENDER=CStr(LC_FldLst.GENDER(0))

			

			Call newdoc.save(True, False)

			Createlog=	Createlog & Chr(10)& "Record created for - " & LC_FldLst.EMPLOYEE_NUMBER(0) & " > Staff Name - '" & newdoc.Name(0)

			newd=newd+1	

			''''A flag will be set in Oracle for this row '''''// Please check this part

			field1.value = "Y"

		End If	

					LC_Conn.Update(fields) // Please check this part

count=LC_Conn.Fetch(LC_FldLst,1,1)	

Wend
2

Subject: Wrong forum but…

You want to be in the Lotus Enterprise Integration forum; all the cool Oracle folks hang out there.

In any case, when you push data into Oracle, you need to use valid SQL statements so figure out how to build the relevant SQL code and then put that into Notes.

I pull data from Oracle all the time but am not allowed to push into Oracle so I can’t actually assist. Be advised that pushing data into Oracle may be more complex than you think since Oracle Business Systems (ERP) uses many layers of abstraction and lots of triggers so your Oracle DBA may not want you doing inserts or updates. You’ll most likely need to work with your Oracle DBA to get this right.

3

Subject: While creating and updating from ORacle to Lotus , setting a flag in Oracle as ‘Y’ per row

Hi Seema,

Please change the line - field1.value = “Y” to sql update command.

LC_Conn.Execute (|UPDATE XX_ORACLE_TO_TBL

SET field1 = ‘Y’ WHERE EMPLOYEE_NUMBER="& CStr(LC_FldLst.EMPLOYEE_NUMBER(0)) & "|, LC_FldLst)

4

Subject: While creating and updating from ORacle to Lotus , setting a flag in Oracle as ‘Y’ per row

Hi All ,

Please guide me on this , if I am missing something, unable to update.