lotus连接访问MS SQL SERVER数据库

Published by admin on 06月 15, 2011

方式有很多如：

1、使用DECS、LEI

2、自动写接口：ODBC(LOTUSSCRIPT)、JDBC(JAVA)、ADO(LOTUSSCRIPT)等方式

用ADO连接SQL是比较好的，不用象ODBC一样建DSN，速度快，稳定。Sub Initialize
On Error Goto err_code
starttime=Now
Dim session As New Notessession
Dim db As Notesdatabase
Dim doc As notesdocument
Dim view As NotesView
Dim c As NotesViewEntryCollection

Set db=session.currentdatabase
Set view=db.GetView(”All Parts Doc”)
Set c=view.AllEntries
If c.Count<>0 Then
   T=c.Count
   Call c.RemoveAll(False)
   Print “Update SC01- ADO:Old records “+Cstr(T)+” removed”
End If

Dim pdoc As NotesDocument
Set pdoc=db.GetProfileDocument(”Profile”)
Set doc=New notesdocument(db)
doc.form = “Supplier”
Print “Update SC01- ADO:Initialize completed”

Dim conn As Variant
Set conn = CreateObject(”ADODB.Connection”)
ConnAdmin = “driver={SQL Server};server=ServerIP;database=DBName;uid=xxx;pwd=xxx”
conn.open ConnAdmin
Set rstt=createobject(”adodb.recordset”)
SQL=”SELECT SC01001,SC01002,SC01010,SC01099 from SC010100 where SC01066=6 or SC01066=7″
rstt.open SQL,conn,1,3
Print “Update SC01- ADO:Database connected”
r=0
Do While Not rstt.eof
   For i=0 To 14
    field=rstt.Fields(i).name
    value=rstt.Fields(i).value
    Set item = doc.AppendItemValue(field,value)
   Next
   rstt.MoveNext
   Call doc.save(True,True)
   Set db = session.CurrentDataBase
   Set doc = New NotesDocument(db)
   doc.form = “Supplier”
   R=R+1
Loop
rstt.close
Print “Update SC01- ADO:Update completed”
endtime=Now
Set mail = New NotesDocument(db)
mail.form=”Memo”
mail.principal=”WUX Price Adjust 2″
mail.Subject=”ADO:Success Import “+Cstr(R)+” Records from SC01[start at “+Cstr(starttime)+”], Old records “+Cstr(T)+” Deleted. [end at”+Cstr(endtime)+”]”
Call mail.send(True,pdoc.SystemMessage)

Exit Sub
err_code:
Dim erm As New NotesDocument(db)
erm.form=”Memo”
erm.subject=db.title+”: Error in ‘Update SC01 from scala ADO’ agent . “+Str(Err)+” At “+Str(Erl)+”. “+Error$
txt=”Server: “+db.server+Chr(10)+”File: “+db.FilePath
If er<>”" Then
   txt=txt+”sending to”+er
End If
erm.body=txt
Call erm.send(False,”yang li”)
End Sub