Lotusscript 与SQL交互基本示例(存储过程)

存储过程新增：

USE [MyTest]
GO

/****** Object:  StoredProcedure [dbo].[Doc2SqlForTest]    Script Date: 05/13/2013 13:40:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Doc2SqlForTest]
  @Unid nvarchar(50),
  @CustomerNo nvarchar(50),
  @CustomerName varchar(30),
  @Address1 varchar(30),
  @Address2 varchar(30),
  @City varchar(20),
  @State char(2),
  @Zip varchar(10),
  @Contact varchar(25),
  @Phone varchar(15),
  @FedIDNo varchar(9),
  
  @success bit = 0 output
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

   INSERT INTO [MyTest].[dbo].[Customers]
           ([Unid]
           ,[CustomerNo]
           ,[CustomerName]
           ,[Address1]
           ,[Address2]
           ,[City]
           ,[State]
           ,[Zip]
           ,[Contact]
           ,[Phone]
           ,[FedIDNo])
     VALUES
           (@Unid,@CustomerNo,@CustomerName,@Address1,@Address2 ,@City,@State, @Zip,@Contact, @Phone,@FedIDNo)
     set @success=1
END

GO

 存储过程获取：

USE [MyTest]
GO

/****** Object:  StoredProcedure [dbo].[GetDocByUnid]    Script Date: 05/13/2013 13:41:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE proc [dbo].[GetDocByUnid]
 @unid nvarchar(32),
 @Success bit =0 output
 /**@returnVal nvarchar(max) output**/
 
as
select *
from Customers c
where c.Unid=@unid
set @success=1

GO

 

 

Lotus脚本代码：

 

Function GetSqlConnectString()
%REM
 返回连接数据库字符串
%END REM
 On Error GoTo ErrHandler
 Dim RetString As string
 
 RetString=|Provider=SQLOLEDB;|
 RetString=RetString & |Data Source=localhost\LOCALSQL;|
 RetString=RetString & |Uid=sa;|
 RetString=RetString & |Pwd=123456;|
 RetString=RetString & |Database=MyTest|
 
 GetSqlConnectString=RetString
 
 Exit Function
ErrHandler:
 MsgBox "Server:"+cdb.Server+",Database:"+cdb.FilePath+"LotusScript:LS_SQL"+",Function:GetSqlConnectString()"+_
 "Error:" & CStr(Error) + ",Code:" & CStr(Err) + ",Line:" & CStr(Erl)
End Function

 

Function OpenSqlConnect() As Variant
%REM
 创建数据连接对象实例
%END REM
 On Error GoTo ErrHandler

 If GSysSqlConnect Is Nothing Then
  Dim SqlConnectString As String
  SqlConnectString=GetSqlConnectString()
  
  If SqlConnectString=""Then
   Exit Function
  End If
  Set GSysSqlConnect=CreateObject("ADODB.Connection")
  GSysSqlConnect.ConnectionString=SqlConnectString
  GSysSqlConnect.ConnectionTimeout=30
  GSysSqlConnect.Open
  Set OpenSqlConnect=GSysSqlConnect
 End If

 If GSysSqlConnect Is Nothing Then
  MsgBox "Server:"+cdb.Server+",Database:"+cdb.FilePath+"LotusScript:LS_K2"+",Function:OpenSqlConnect()"+",连接数据库失败!"
 End If
 Exit Function
ErrHandler:
 Set GSysSqlConnect=Nothing
 MsgBox  "OpenSqlConnect"+ CStr(Error) + ",Code:" & CStr(Err) + ",Line:" & CStr(Erl)
End Function

 

%REM
 Function Doc2Sql
 Description: Comments for Function
%END REM
Function Doc2Sql(doc As NotesDocument)
 On Error GoTo Errorhandler
 Call OpenSqlConnect()
 Dim rs As Variant
 Dim sqlCommand As Variant
 Dim RetRecoredSet As Variant
 Dim defaultsql As String
 
 Set rs = CreateObject("ADODB.Recordset")
 Set SqlCommand=CreateObject("ADODB.command")
 Set SqlCommand.ActiveConnection=GSysSqlConnect
 SqlCommand.CommandType=adCmdStoredProc
 SqlCommand.CommandText = "dbo.Doc2SqlForTest"
 
 SqlCommand.Parameters.Append SqlCommand.CreateParameter("@unid",adVarWChar,AdParamInput,32,doc.Universalid)
 SqlCommand.Parameters.Append SqlCommand.CreateParameter("@CustomerNo",adVarWChar,AdParamInput,50,doc.CustomerNo(0))
 SqlCommand.Parameters.Append SqlCommand.CreateParameter("@CustomerName",adVarChar,AdParamInput,30,doc.CustomerName(0))
 SqlCommand.Parameters.Append SqlCommand.CreateParameter("@Address1",adVarChar,AdParamInput,30,doc.Address1(0))
 SqlCommand.Parameters.Append SqlCommand.CreateParameter("@Address2",adVarChar,AdParamInput,30,doc.Address2(0))
 SqlCommand.Parameters.Append SqlCommand.CreateParameter("@City",adVarChar,AdParamInput,20,doc.City(0))
 SqlCommand.Parameters.Append SqlCommand.CreateParameter("@State",adChar,AdParamInput,2,doc.State(0))
 SqlCommand.Parameters.Append SqlCommand.CreateParameter("@Zip",adVarChar,AdParamInput,10,doc.Zip(0))
 SqlCommand.Parameters.Append SqlCommand.CreateParameter("@Contact",adVarChar,AdParamInput,25,doc.Contact(0))
 SqlCommand.Parameters.Append SqlCommand.CreateParameter("@Phone",adVarChar,AdParamInput,15,doc.Phone(0))
 SqlCommand.Parameters.Append SqlCommand.CreateParameter("@FedIDNo",adVarChar,AdParamInput,9,doc.FedIDNo(0))
 SqlCommand.Parameters.Append SqlCommand.CreateParameter("@Success",adBoolean,adParamOutput,1)
 
 Call SqlCommand.Execute() 
 
 Dim success As Variant
 success=SqlCommand.Parameters("@success").value
 MsgBox "保存+" +success
  Exit Function
Errorhandler:
  MsgBox "ls_sql,Doc2Sql:"+Error +","+CStr(Erl)
End Function

 

 

%REM
 Function Sql2Doc
 Description: Comments for Function
%END REM
Function Sql2Doc(doc As NotesDocument)
 On Error GoTo Errorhandler
 Call OpenSqlConnect()
 Dim rs As Variant
 Dim sqlCommand As Variant
 Dim RetRecoredSet As Variant
 Dim defaultsql As String
 
 Set rs = CreateObject("ADODB.Recordset")
 Set SqlCommand=CreateObject("ADODB.command")
 Set SqlCommand.ActiveConnection=GSysSqlConnect
 SqlCommand.CommandType=adCmdStoredProc
 
 SqlCommand.CommandText = "dbo.GetDocByUnid"
 
 SqlCommand.Parameters.Append SqlCommand.CreateParameter("@unid",adVarWChar,AdParamInput,32,"461C8D734839A9B648257B67001CC9C9")
 SqlCommand.Parameters.Append SqlCommand.CreateParameter("@Success",adBoolean,adParamOutput,1)
 Set rs=SqlCommand.Execute()
 rs.MoveFirst
 If(Not rs.eof())Then
  MsgBox rs.Fields("City").name
  MsgBox rs.Fields("City").value
  rs.MoveNext
 End If
 Exit Function
Errorhandler:
 MsgBox "ls_sql,Sql2Doc:"+Error +","+CStr(Erl)
End Function