Importing records from an external Database

HCL Domino Domino Forum
1

Hi All

I would like to create an agent that connects to a table on my external database and inport all the records into a lotus notes view.

For a test I created a form with the fields I wanted filled and added the following code to a button to populate the fields. This works fine.

Dim con As New ODBCConnection

Dim qry As New ODBCQuery

Dim res As New ODBCResultSet

Dim ws As New Notesuiworkspace

Dim uidoc As Notesuidocument

Set uidoc = ws.CurrentDocument

If con.ConnectTo("SWAN","USername","Password") Then

	Set qry.Connection = con

	qry.SQL =  _

	"select * from STOCK_CONTROL_FILE where  ITEM_NUMBER LIKE'CS%'"

	Set res.Query = qry

	res.Execute

	If res.IsResultSetAvailable Then

		Do

			res.NextRow

			Call uidoc.FieldSetText("StockCode", _

			res.GetValue("ITEM_NUMBER"))

			Call uidoc.FieldSetText("ItemDescription", _

			res.GetValue("ITEM_DESCRIPTION_1"))

			Call uidoc.FieldSetText("ItemDescription2", _

			res.GetValue("ITEM_DESCRIPTION_2"))		

			Call uidoc.FieldSetText("InStock", _

			res.GetValue("OVERALL_STOCK_LEVEL"))

		Loop Until res.IsEndOfData

		Call uidoc.Refresh 

		Messagebox("Finished importing record :-  " + _  

		uidoc.FieldGetText("Code") )      

	Else

		Messagebox("No information found for " + _  

		uidoc.FieldGetText("Code") )      

	End If

	res.Close(DB_CLOSE)

	con.Disconnect

Else

	Messagebox("Could not connect to database server")   

End If

How can I adapt my code for an agent so that it will populate my view with the records.

Regards

Douglas Bell

2

Subject: Importing records from an external Database

Additions in blue

Dim con As New ODBCConnection

Dim qry As New ODBCQuery

Dim res As New ODBCResultSet

Dim ws As New Notesuiworkspace

Dim sess As New NotesSession

Dim db As NotesDatabase

Dim doc As NotesDocument

Dim uidoc As Notesuidocument

Set uidoc = ws.CurrentDocument

Set db = sess.CurrentDatabase

If con.ConnectTo("SWAN","USername","Password") Then

	Set qry.Connection = con

	qry.SQL = _

	"select * from STOCK_CONTROL_FILE where ITEM_NUMBER LIKE'CS%'"

	Set res.Query = qry

	res.Execute

	If res.IsResultSetAvailable Then

		Do

			res.NextRow

			Set doc = db.CreateDocument

			With doc

				.Form = "FormName"

				.StockCode = res.GetValue("ITEM_NUMBER")

				.ItemDescription = res.GetValue("ITEM_DESCRIPTION_1")

				.ItemDescription2 = res.GetValue("ITEM_DESCRIPTION_2")

				.InStock = res.GetValue("OVERALL_STOCK_LEVEL")

				.Save True,False

			End With

			Call uidoc.FieldSetText("StockCode", _

			res.GetValue("ITEM_NUMBER"))

			Call uidoc.FieldSetText("ItemDescription", _

			res.GetValue("ITEM_DESCRIPTION_1"))

			Call uidoc.FieldSetText("c", _

			res.GetValue("ITEM_DESCRIPTION_2"))

			Call uidoc.FieldSetText("InStock", _

			res.GetValue("OVERALL_STOCK_LEVEL"))

		Loop Until res.IsEndOfData

                                           Messagebox "Finished importing records !!"

		Call uidoc.Refresh

		Messagebox("Finished importing record :- " + _

		uidoc.FieldGetText("Code") )

	Else

                                            Messagebox "No information found"

		Messagebox("No information found for " + _

		uidoc.FieldGetText("Code") )

	End If

	res.Close(DB_CLOSE)

	con.Disconnect

Else

	Messagebox("Could not connect to database server")

End If

cheers !!

Ashish

3

Subject: RE: Importing records from an external Database

Thanks Ashish

That worked perfectly apart from one thing, it only imports the first 771 records, is there a limit to the amount of records that is supported?

Regards

Douglas Bell

4

Subject: RE: Importing records from an external Database

AFAIK there is no limit. It could be because of the time-out. Did you get any error ?

cheers !!

Ashish

5

Subject: RE: Importing records from an external Database

The default fetch cache will create an artificial limit; try setting it to zero.

6

Subject: RE: Importing records from an external Database

Checked the help.You need to set the MaxRows property of the resultst to 0 inorder to fetch all the records.

so add the following and it should get all the records,

res.MaxRows = 0

cheers!!

Ashish

7

Subject: RE: Importing records from an external Database

Hi All

Thanks for the help, for me to get all the records I had to set the CacheLimit = DB_NONE.

Everything is working great now.

Cheers

Douglas Bell

8

Subject: Importing records from an external Database

Douglas,

For doing this purely through script (not using LEI)…

At the start of each loop through the resultset, create a new document

Set notesDocument = New NotesDocument( notesDatabase

and then use the syntax of the doucment to populate the fields

eg: notesDocument.InStock=res.getvalue(“OVERALL_STOCK_LEVEL”)

and then save your document

notesDocument.Save

HTH

Mike