lotuscript: some questions about connecting to a SQL DB

I've got this code that is working... I read from an MS SQL database some rows from a table and then send an email for each row.

I'm about to add an "attachment" field, on my SQL database and I'd like to add the attachment at the end of my body.

I have two questions: 1) what datatype should I use on MS SQL? (Binary field, maybe) and 2) if someone else has some example code, I'd really appreciate it.

A bonus question: on a more advanced version of this script, i first run by all my results from my resultset to get the IDs from the messages and then update their status on the MS SQL table. Then I try and run by the same resultset again, to actually perform the sending.... Somehow, on the second run, I'm having trouble starting from row 1, using the same code than bellow... any advice on what's the best way to do that?: My requirement is that I have to run twice by the same resultset. :)

Thanks in advance.

    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

Wasn't this line:

result.NextRowcode

supposed to be:

result.NextRow

?

I don't know about MSSQL, but in DB2 we routinely use Blob/Clob binary data type to store any type of files.
I hear that many MSSQL experts recommend rather storing files on file system with only their path and file name in a DB.

OK, here's the abbreviated code from my Java ScriptLibrary that uses JDBC to connect to DB2 and execute a query (you would only need to import your db's jar-s and use com.microsoft.sqlserver.jdbc.SQLServerDriver for 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;
    }
}

To check out the examples of using LS2J to use Java classes directly from your LotusScript code, download this great LS2J samples database from Julian Robichaux:
http://www.nsftools.com/tips/NotesTips.htm#ls2jexamples

And here's a good link to start with, explains how to use JDBC with MS SQL server:
http://support.microsoft.com/kb/313100