How to connect to external database from Lotus Script agent

HCL Domino Domino Forum
1

Hello.I have an agent which is scheduled to execute on the Domino server. From this agent I need to connect to external database (such as Oracle or SQL) and retrieve some data.

Should I use Domino Enterprise Connection Services (DECS) or LCLSX?

Do I need to install any additional software on the Domino server?

Does anybody have an example I could start from?

Thank you very much for your time and answers.

2

Subject: How to connect to external database from Lotus Script agent

Hello Julia.

The following is an aget that connects to an SQL Server 2000 table:

Option Public

Option Explicit

Uselsx “*LSXLC”

Sub Initialize

Dim session As New LCSession

Dim connect As New LCConnection("oledb")

connect.Provider = "SQLOLEDB"

connect.Init_Datasource = "sever_name"

connect.Database = "database_name"

connect.Userid = "userid_name"

connect.Password = "password_for_above_userid"

connect.Connect



Dim fldLst As New LCFieldList



Dim fldLst1 As New LCFieldList

Dim fldLst2 As New LCFieldList



Dim fldId As LCField

Dim fldTitle As LCField

Dim fldFirstname As LCField

Dim fldSurname As LCField

Dim fldCity As LCField

Dim fldChar As LCField

Dim fldDateTime As LCField

Dim fldFloat As LCField

Dim fldnText As LCField

Dim fldText As LCField



If (connect.Execute("SELECT top 5 * FROM table_name", fldLst) = 0) Then

	Msgbox "No records were found"

	connect.Disconnect

	Exit Sub 

End If





Call fldLst2.Map(fldLst, "fId, fTitle, fFirstname, fSurname, fCity, cChar, fDateTime, fFloat, fnText, fText")



Set fldfId = fldLst2.GetField(1)

Set fldfTitle = fldLst2.GetField(2)

Set fldfmFirstname = fldLst2.GetField(3)

Set fldfSurname = fldLst2.GetField(4)

Set fldfCity = fldLst2.GetField(5)

Set fldChar = fldLst2.GetField(6)

Set fldDateTime = fldLst2.GetField(7)

Set fldFloat = fldLst2.GetField(8)

Set fldnText = fldLst2.GetField(9)

Set fldText = fldLst2.GetField(10)



connect.MapByName = True



Dim msg As String

msg = "Fetching ID, Title" & Chr(13)



Dim count As Integer

count = 0

While(connect.Fetch(fldLst2,1,1) > 0)

	

	count = count + 1

	msg = msg & Cstr(count)

	msg = msg & " - " & fldId.Text(0)

	msg = msg & "  -  " &  fldTitle.Text(0)

	msg = msg & "  -  " &  fldFirstname.Text(0)

	msg = msg & "  -  " &  fldSurname.Text(0)

	msg = msg & "  -  " &  fldCity.Text(0)

	msg = msg & "  -  " &  fldChar.Text(0)

	msg = msg & "  -  " &  fldDateTime.Text(0)

	msg = msg & "  -  " &  fldFloat.Text(0)

	msg = msg & "  -  " &  fldnText.Text(0)

	msg = msg & "  -  " &  fldText.Text(0)

	

	msg = msg & Chr(13)			

	

Wend

Messagebox msg

End Sub

Also, check this forum for LSXLC/LCLSX

Happy coding!

N

3

Subject: RE: How to connect to external database from Lotus Script agent

Thank you very mach for your answer.But I couldn’t save this agent, I got an error: Data not saved due to script error(s) and the row Uselsx “*LSXLC” is marked as red.

4

Subject: RE: How to connect to external database from Lotus Script agent

Hello Julia.

not sure why you are getting the error on the “Use…”

Did you, I am sure you did, but just checking, put the Uselsx “*LSXLC” in the Options of the agent?

NM

5

Subject: RE: How to connect to external database from Lotus Script agent

I installed DECS on the client and than I could save my script.It’s working now.

Thank you.