LC LSX: Data Pushing to oracle

HCL Domino Domino Forum
1

Hi,

I am pushing data fro Lotus Notes to Oracle 10g. The code works fine for the 1st document, then it throws an error “Error: Field Count Mismatch”. I am pasting the code here, any help will be highly appreciated.

Sub Initialize

Dim db As NotesDatabase 

Dim view As NotesView

Dim currentPartView As NotesView

Dim doc As NotesDocument



Dim x  As String 

Dim y  As String 

Dim z  As String 





Dim i As Integer 





Dim lccon As LCConnection



Dim lc_column1 As LCField 

Dim lc_column2 As LCField

Dim lc_column3 As LCField

Dim statement As String





Dim lcresultfieldlist   As  New LCFieldList (3)



i = 0



Dim session As New NotesSession



Set db = session.CurrentDatabase

Set doc = New NotesDocument(db)

Set View = db.GetView( "TestV" )





Set doc = View.GetFirstDocument





Set lccon = New LCConnection("oracle")

With lccon

	.server = "test"

	.Metadata = "LC_TEST"

	.Userid = "test"

	.Password = "test"

End With

lccon.Connect

’ Execute Query and return data from Oracle Pull back Active BOM data - item + components

statement = |

SELECT * FROM LC_TEST

|

'Set lcresultfieldlist = New LCFieldList



Do Until doc Is Nothing

Set lc_column1 = lcresultfieldlist.Append (“PRIORITY” , LCTYPE_TEXT)

	x = Cstr(doc.Test1(0))

	lc_column1.value = x

	

	Set lc_column2 = lcresultfieldlist.Append ("CATEGORY", LCTYPE_TEXT)

	y = Cstr(doc.Test2(0))

	lc_column2.value = y

	

	Set lc_column3 = lcresultfieldlist.Append ("PROBLEM_HEADING" , LCTYPE_TEXT)

	z = Cstr(doc.Test3(0))

	lc_column3.value = z



	Call lccon.Insert (lcresultfieldlist,1,i)

	

	Set lc_column = lcresultfieldlist.Insert (x (doc.ColumnValues(0)))

		

	Set doc = View.GetNextDocument(doc)

	i = i +1

Loop

DisconnectClean:

lccon.Disconnect

Exit Sub

End Sub

2

Subject: LC LSX: Data Pushing to oracle.

I dont see you executing the select statement query … that is the count … I am pasting a sample code here… Let me know if this helps you!

Dim LC_FldLst As LCFieldList

Set LC_FldLst = New LCFieldList(1)

SelectStatement={Select * from LC_TEST}

count=connection.Execute(SelectStatement, LC_FldLst)

If count <> 0 Then

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

 If count =0 Then ' if record is not found in the Oracle table

msgbox "No Records found"

 End If

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

Set lc_column1 = lcresultfieldlist.Append ("PRIORITY",LCTYPE_TEXT)

	x = Cstr(doc.Test1(0))

	lc_column1.value = x



	Set lc_column2 = lcresultfieldlist.Append ("CATEGORY", LCTYPE_TEXT)

	y = Cstr(doc.Test2(0))

	lc_column2.value = y



	Set lc_column3 = lcresultfieldlist.Append ("PROBLEM_HEADING" , LCTYPE_TEXT)

	z = Cstr(doc.Test3(0))

	lc_column3.value = z



	Call lccon.Insert (lcresultfieldlist,1,i)



	Set lc_column = lcresultfieldlist.Insert (x (doc.ColumnValues(0)))



end if

connection.Disconnect
3

Subject: RE: LC LSX: Data Pushing to oracle.

Thanks for the response, but I am facing the same error.

4

Subject: LC LSX: Data Pushing to oracle.

Why dont you try collecting all your values in an array within the while loop and then set the LCField (in ur case lc_xolumn1,2,3) outside the loop and try…

5

Subject: RE: LC LSX: Data Pushing to oracle.

Thanks , but I cracked it. This is the final code :

Sub Initialize

Dim sess As New NotesSession

On Error Goto ErrorHandler

Dim LCSess As LCSession

Dim sdbLCConn As LCConnection

Dim db As NotesDatabase

Dim view As NotesView

Dim doc As NotesDocument

Dim field1 As LCField

Dim field2 As LCField

Dim field3 As LCField

Dim fields As LCFieldList

Dim count As Long

Dim x As Integer



Set db = sess.CurrentDatabase

Set view = db.GetView("QAReport")

Set doc = view.GetFirstDocument

Set LCSess = New LCSession

Set fields = New LCFieldList



Set sdbLCConn = New LCConnection("Oracle")

table = "LC_TEST"

Set fields = New LCFieldList

'identify the table in oracle

sdbLCConn.Metadata = table

'set the connection strings - production

sdbLCConn.Server = "EASTEK"

sdbLCConn.Userid = "EASTEK"

sdbLCConn.Password = "EASTEK"

'make the connections

Call sdbLCConn.Connect



Count = 0



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

Set field2 = fields.Append("CATEGORY",LCTYPE_TEXT)

Set field3 = fields.Append("PROBLEM_HEADING", LCTYPE_TEXT)



While Not doc Is Nothing

	

	field1.value = doc.priority(0)

	

	

	field2.value = doc.q_category(0)

	

	

	

	field3.value = doc.ProblemHeading(0)

	

	Set doc = view.GetNextDocument(doc)

	

	

	count = sdbLCConn.Insert (fields) 

	

Wend

'disconnect

Call sdbLCConn.Disconnect



Exit Sub

ErrorHandler:

Messagebox "Error " & Err() & ": " & Error()

Exit Sub 

sdbLCConn.Disconnect

Exit Sub

End Sub