lotuscript:有关连接到SQL DB的一些问题 [英] lotuscript: some questions about connecting to a SQL DB
问题描述
我有这段代码正在工作...我从MS SQL数据库中读取了表中的一些行，然后为每行发送一封电子邮件.
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.
我要在SQL数据库上添加一个附件"字段，我想在正文的末尾添加附件.
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.
我有两个问题:1)我应该在MS SQL上使用哪种数据类型? (也许是二进制字段)和2)如果其他人有一些示例代码，我将非常感谢.
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.
一个额外的问题:在此脚本的更高级版本上，我首先使用结果集中的所有结果来获取消息中的ID，然后在MS SQL表上更新其状态. 然后，我尝试再次使用同一结果集运行，以实际执行发送.... 不知何故，在第二次运行中，我无法从第1行开始，使用与下面相同的代码...关于什么是最佳方法的任何建议?:我的要求是我必须以相同的方式运行两次结果集. :)
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. :)
谢谢.
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
推荐答案
好的，这是我的Java ScriptLibrary中的缩写代码，它使用JDBC连接到DB2并执行查询(您只需导入数据库的jar-s并将com.microsoft.sqlserver.jdbc.SQLServerDriver用于MS SQL)
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;
}
}
要查看直接从您的LotusScript代码中使用LS2J来使用Java类的示例，请从Julian Robichaux下载此出色的LS2J示例数据库:
http://www.nsftools.com/tips/NotesTips.htm#ls2jexamples
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
下面是一个很好的链接，它说明了如何在MS SQL服务器上使用JDBC:
http://support.microsoft.com/kb/313100
And here's a good link to start with, explains how to use JDBC with MS SQL server:
http://support.microsoft.com/kb/313100
这篇关于lotuscript:有关连接到SQL DB的一些问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
