lotuscript：有关连接 SQL DB 的一些问题

Question

我已经得到了正在运行的代码...我从 MS SQL 数据库读取表中的一些行，然后为每一行发送一封电子邮件。

我要在我的 SQL 数据库上添加一个“附件”字段，并且我想在正文末尾添加附件。

我有两个问题：1）我应该在 MS SQL 上使用什么数据类型？ （也许是二进制字段）2）如果其他人有一些示例代码，我将非常感激。

一个额外的问题：在这个脚本的更高级版本中，我首先运行结果集中的所有结果，以从消息中获取 ID，然后更新它们在 MS SQL 表上的状态。 然后我尝试再次运行相同的结果集，以实际执行发送...... 不知何故，在第二次运行时，我从第一行开始遇到了麻烦，使用与下面相同的代码...关于最好的方法是什么的建议？：我的要求是我必须以相同的方式运行两次结果集。 ：）

提前致谢。

    Option Public 
    Uselsx "*LSXODBC"

    Sub Initialize
    Dim session As New NotesSession
    Dim db As NotesDatabase
    Dim doc As NotesDocument
    Dim subject As String, cc As String, bcc As String, sender As String, OID As String, mailto As String, bodyNotMIME As String
    Dim body As NotesMIMEEntity


    On Error Goto errorCounter

    Set db = session.CurrentDatabase

    Gosub SendMailGeneral

    Exit Sub

    SendMailGeneral:
    Dim con As New ODBCConnection
    Dim qry As New ODBCQuery
    Dim result As New ODBCResultSet
    Dim defaultQuery As String
    Set qry.Connection = con    
    con.SilentMode = True
    If con.ConnectTo("DSN_Name","USER_NAME", "PASSWORD") Then
        Set result.Query = qry
        defaultQuery = "select TOP (10)  * from Message  where StatusType=0"
        qry.SQL = defaultQuery      
            result.Execute
            If (result.IsResultSetAvailable) Then
                Do


  result.NextRow

                Gosub GetRowFields

                Gosub SendMail

            Loop Until result.IsEndOfData
        End If
        End If
        result.Close(DB_CLOSE)  
        Return
        End Sub


    GetRowFields:
        mailto = result.GetValue("To")
        cc = result.GetValue("CC")
        bcc = result.GetValue("Bcc")
        sender = result.GetValue("Sender")
        subject = result.GetValue("Subject")
        bodyNotMIME = result.GetValue("Body")               
        OID = result.GetValue("OID")

        Return


    SendMail:
            Dim mail As NotesDocument
            Set mail = New NotesDocument(db)
            Dim stream As NotesStream
        Set stream = session.CreateStream

    'Recipients 
        mail.SendTo = mailto
        mail.CopyTo = cc
        mail.BlindCopyTo = bcc

    ' Set all sender-related fields 
        mail.ReplyTo = sender
        mail.Principal = sender
        mail.From = sender
        mail.AltFrom = sender
        mail.SendFrom = sender
        mail.INetFrom = sender
        mail.tmpDisplaySentBy = sender
        mail.tmpDisplayFrom_Preview = sender
        mail.DisplaySent = sender 

    'Body   

        Call stream.WriteText(bodyNotMIME)
        Set body = mail.CreateMIMEEntity
        Call body.SetContentFromText _
        (stream, "text/html; charser=iso-8859-1", ENC_NONE)

    'Subject    
        mail.Subject = subject

    'Send

        Call mail.Send(False, False)

        Return

Answer 1

这行不是

result.NextRowcode

应该是： 吗

result.NextRow

？

我不了解 MSSQL，但在 DB2 中，我们通常使用 Blob/Clob 二进制数据类型来存储任何类型的文件。
我听说许多 MSSQL 专家建议将文件存储在文件系统上，而只将其路径和文件名存储在数据库中。

Answer 2

好的，这是我的 Java ScriptLibrary 中的简短代码，它使用 JDBC 连接到 DB2 并执行查询（您只需要导入数据库的 jar 并使用 com.microsoft.sqlserver.jdbc.SQLServerDriver 对于 MS SQL）：

import java.sql.*;
import com.ibm.db2.jcc.DB2Driver;

public class DB2Connection {
    protected String server;
    protected String port;
    protected String dbName;
    protected String userdb2;
    protected String pwddb2;
    protected java.sql.Connection con;


    public DB2Connection( String srv, String port, String db, String user, String pass ){
        this.server = srv;
        this.port = port;
        this.dbName = db;
        this.userdb2 = user;
        this.pwddb2 = pass;

        connectDB2();
    }


    public void connectDB2() {
        try {
            Class.forName("com.ibm.db2.jcc.DB2Driver"); // .newInstance();
            String url = "jdbc:db2://" + server + ":" + port + "/" + dbName;
            con = DriverManager.getConnection(url, userdb2, pwddb2);
            System.out.println("Connection to DB2 succeded");

        } catch (Exception e) {
            System.out.println("Error connecting DB2 Server") ;
            e.printStackTrace();
        }
    }

    protected ResultSet executeQuery( String queryString ) throws SQLException, Exception {
        System.out.println( "Preparing query:\t" + queryString );
        ResultSet rs = null;
        try {
            Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery(queryString);

        } catch (SQLException sqle) {
            String error = ("SQLException : Could not execute query");
            throw new SQLException(error);
        } catch (Exception e) {
            String error1 = ("Exception : An Unknown error occurred.");
            throw new Exception(error1);
        }
        return rs;
    }

    protected int executeCountQuery( StringBuffer queryStr ){
        System.out.println( "Preparing query:\t" + queryStr );
        try {
            ResultSet rs = executeQuery( queryStr.toString( ) );
            rs.next();  //only one row in result set
            return rs.getInt(1);

        } catch (SQLException sqle) {
            System.out.println("SQLException: Could not get ResultSet from DB2Connection.executeQuery");
            sqle.printStackTrace();
        } catch (Exception e) {
            System.out.println("Exception : An Unknown error occurred while calling.");
            e.printStackTrace();
        }
        return 0;
    }

    protected int executeCountQuery( PreparedStatement ps ){
        //System.out.println( "Preparing prepared statement - query:\t" );  //+ ps.getQuery( ) );
        try {
            ResultSet rs = ps.executeQuery( );
            rs.next();  //only one row in result set
            return rs.getInt(1);

        } catch (SQLException sqle) {
            System.out.println("SQLException: Could not get ResultSet from DB2Connection.executeQuery");
            sqle.printStackTrace();
        } catch (Exception e) {
            System.out.println("Exception : An Unknown error occurred while calling.");
            e.printStackTrace();
        }
        return 0;
    }


    public Connection getConnection(){
        return con;
    }
}

要查看使用 LS2J 直接从 LotusScript 代码使用 Java 类的示例，请从 Julian Robichaux 下载这个出色的 LS2J 示例数据库：
http://www.nsftools.com/tips/NotesTips.htm#ls2jexamples

这里有一个很好的开始链接，解释了如何将 JDBC 与 MS SQL Server 结合使用：
http://support.microsoft.com/kb/313100