Domino 7 使用 ADO 做 Mysql 的 新增,修改,刪除及查詢

查詢

Sub Initialize
On Error Goto ErrHandle
Set session=New NotesSession
Set db=session.CurrentDatabase
Set ws=New NotesUIWorkspace
Set agent = session.CurrentAgent
Set currentLog = New NotesLog( db.Title+" - Agent - "+agent.Name+" on "+db.Server )
Set profile=db.GetProfileDocument("SystemProfile")
Call currentLog.OpenNotesLog( db.Server, profile.LogPath(0) )
Dim conn As Variant
Set conn = CreateObject ("ADODB.Connection")
ConnAdmin = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER="+profile.SqlServer(0)+"; DATABASE="+profile.SqlDatabase(0)+";UID="+profile.SqlAccount(0)+";PASSWORD="+profile.SqlPasswd(0)+"; OPTION=3"
conn.open ConnAdmin
Set uidoc=ws.CurrentDocument
Set doc=uidoc.Document
Set RSTT = CreateObject("ADODB.Recordset")
'SQL = "SELECT * FROM mm_budget WHERE MM_MARA_MATNR='"+doc.ItemNo(0)+"'"
SQL = "SELECT * FROM mm_budget WHERE MM_MARA_MATNR='"+uidoc.FieldGetText("ItemNo")+"' AND BGTDATE>'" +uidoc.FieldGetText("Result")+"'"
rstt.CursorLocation=3
rstt.open SQL, conn, 1,3
rstt.Sort = "BGTDATE DESC"
Call uidoc.FieldClear("A")
Call uidoc.FieldClear("B")
Call uidoc.FieldClear("C")
Call uidoc.FieldClear("D")
Call uidoc.FieldClear("E")
Call uidoc.FieldClear("F")
Do While Not rstt.eof
' For i = 0 To 5
' this.field=rstt.Fields(i).field
' this.value=rstt.Fields(i).value
' Set item = doc.AppendItemValue (field, value)
' Next
Call uidoc.FieldAppendText("A",Cstr(rstt.Fields(0).value)+Chr(10)+Chr(13))
Call uidoc.FieldAppendText("B",Cstr(rstt.Fields(4).value)+Chr(10)+Chr(13))
Call uidoc.FieldAppendText("C",Cstr(rstt.Fields(2).value)+Chr(10)+Chr(13))
Call uidoc.FieldAppendText("D",Cstr(rstt.Fields(5).value)+Chr(10)+Chr(13))
Call uidoc.FieldAppendText("E",Cstr(rstt.Fields(3).value)+Chr(10)+Chr(13))
Call uidoc.FieldAppendText("F",Cstr(rstt.Fields(1).value)+Chr(10)+Chr(13))
RSTT.MoveNext
Loop
rstt.close
conn.close
ErrHandle:
Print  "Error" & Str(Err) & ": " & Error$ & " on line " & Cstr(Erl)
Call currentLog.LogError(0,"不明錯誤，請查詢Log 記錄 : " & "Error" & Str(Err) & ": " & Error$ & " on line " & Cstr(Erl))
End Sub

新增

Sub Initialize
On Error Goto ErrHandle
Set session=New NotesSession
Set db=session.CurrentDatabase
Set ws=New NotesUIWorkspace
Set agent = session.CurrentAgent
Set currentLog = New NotesLog( db.Title+" - Agent - "+agent.Name+" on "+db.Server )
Set profile=db.GetProfileDocument("SystemProfile")
Call currentLog.OpenNotesLog( db.Server, profile.LogPath(0) )
Dim avarNew As Variant
avarNew = Split(profile.RecNo(0), "|")
Dim conn As Variant
Set conn = CreateObject ("ADODB.Connection")
ConnAdmin = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER="+profile.SqlServer(0)+"; DATABASE="+profile.SqlDatabase(0)+";UID="+profile.SqlAccount(0)+";PASSWORD="+profile.SqlPasswd(0)+"; OPTION=3"
conn.open ConnAdmin
Set uidoc=ws.CurrentDocument
Set doc=uidoc.Document
SQL = "INSERT INTO `mis`.`mm_budget` (`BGTTYPE`, `BGTDATE`, `QTY`, `MM_MARA_MATNR`, `MM_KOS_KOSTL`) VALUES ('"+avarNew(4)+"', '"+avarNew(1)+"', '"+avarNew(3)+"', '"+avarNew(0)+"', '"+avarNew(2)+"')"
Call currentlog.LogAction(SQL)
conn.execute(SQL)
conn.close
ErrHandle:
Print  "Error" & Str(Err) & ": " & Error$ & " on line " & Cstr(Erl)
Call currentLog.LogError(0,"不明錯誤，請查詢Log 記錄 : " & "Error" & Str(Err) & ": " & Error$ & " on line " & Cstr(Erl))
End Sub

刪除

Sub Initialize
On Error Goto ErrHandle
Set session=New NotesSession
Set db=session.CurrentDatabase
Set ws=New NotesUIWorkspace
Set agent = session.CurrentAgent
Set currentLog = New NotesLog( db.Title+" - Agent - "+agent.Name+" on "+db.Server )
Set profile=db.GetProfileDocument("SystemProfile")
Call currentLog.OpenNotesLog( db.Server, profile.LogPath(0) )
Dim conn As Variant
Set conn = CreateObject ("ADODB.Connection")
ConnAdmin = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER="+profile.SqlServer(0)+"; DATABASE="+profile.SqlDatabase(0)+";UID="+profile.SqlAccount(0)+";PASSWORD="+profile.SqlPasswd(0)+"; OPTION=3"
conn.open ConnAdmin
Set uidoc=ws.CurrentDocument
Set doc=uidoc.Document
SQL = "DELETE FROM `mis`.`mm_budget` WHERE `BGTID`='"+profile.RecNo(0)+"';"
Call currentlog.LogAction(SQL)
conn.execute(SQL)
conn.close
ErrHandle:
Print  "Error" & Str(Err) & ": " & Error$ & " on line " & Cstr(Erl)
Call currentLog.LogError(0,"不明錯誤，請查詢Log 記錄 : " & "Error" & Str(Err) & ": " & Error$ & " on line " & Cstr(Erl))
End Sub

修改

Sub Initialize
On Error Goto ErrHandle
Set session=New NotesSession
Set db=session.CurrentDatabase
Set ws=New NotesUIWorkspace
Set agent = session.CurrentAgent
Set currentLog = New NotesLog( db.Title+" - Agent - "+agent.Name+" on "+db.Server )
Set profile=db.GetProfileDocument("SystemProfile")
Call currentLog.OpenNotesLog( db.Server, profile.LogPath(0) )
Dim avarNew As Variant
avarNew = Split(profile.RecNo(0), "|")
Dim conn As Variant
Set conn = CreateObject ("ADODB.Connection")
ConnAdmin = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER="+profile.SqlServer(0)+"; DATABASE="+profile.SqlDatabase(0)+";UID="+profile.SqlAccount(0)+";PASSWORD="+profile.SqlPasswd(0)+"; OPTION=3"
conn.open ConnAdmin
Set uidoc=ws.CurrentDocument
Set doc=uidoc.Document
SQL = "UPDATE `mis`.`mm_budget` SET `MM_MARA_MATNR`='"+DoTrim(avarNew(1))+"',`MM_KOS_KOSTL`='"+DoTrim(avarNew(3))+"',`BGTTYPE`='"+DoTrim(avarNew(5))+"',`BGTDATE`='"+DoTrim(avarNew(2))+"', `QTY`='"+DoTrim(avarNew(4))+"' WHERE `BGTID`='"+DoTrim(avarNew(0))+"';"
Call currentlog.LogAction(SQL)
conn.execute(SQL)
conn.close
ErrHandle:
Print  "Error" & Str(Err) & ": " & Error$ & " on line " & Cstr(Erl)
Call currentLog.LogError(0,"不明錯誤，請查詢Log 記錄 : " & "Error" & Str(Err) & ": " & Error$ & " on line " & Cstr(Erl))
End Sub

Domino利用JCO來透過RFC_RAED_TABLE取得SAP資料的範例

很多取SAP資料的小應用其實都不需要自己寫RFC，下面這個例子是讓用戶輸入料號及批號來取得品名及可用量和有效期。

import lotus.domino.*;
import java.util.*;
import com.sap.mw.jco.*;
public class SAPApprove extends AgentBase {
private Session session;
private AgentContext agentContext;
private Database db;
private String sapIP,sapClient,sapAccount,sapPasswd,sapLang,sapSysNo;
private JCO.Function function;
private JCO.Function function1;
private JCO.Function function2;
private JCO.Client client ;
private Log log;
private boolean isError=false;
private String errMsg="";
private String resultCode;
private String resultMsg ;
private String cnname ;
private String quantity ;
private String valid_date ;
private Document doc;

public void init(Session session,AgentContext agentContext) throws NotesException{
try {
this.session=session;
this.agentContext=agentContext;
this.db=agentContext.getCurrentDatabase();
Document profile=db.getProfileDocument("SystemProfile",null);
sapIP=profile.getItemValueString("SAPIP");
sapClient=profile.getItemValueString("SAPClient");
sapAccount=profile.getItemValueString("SAPAccount");
sapPasswd=profile.getItemValueString("SAPPasswd");
sapLang=profile.getItemValueString("SAPCode");
sapSysNo=profile.getItemValueString("SAPSysNO");
String logPath=profile.getItemValueString("LogPath");
log = session.createLog( db.getTitle()+" 's Agent "+agentContext.getCurrentAgent().getName()+" on " + db.getServer());
log.openNotesLog(db.getServer(),logPath);
Agent agent = agentContext.getCurrentAgent();
doc = db.getDocumentByID(agent.getParameterDocID());
System.out.println("Load Profile OK.");
}catch(Exception e) {
e.printStackTrace();
try{
session.setEnvironmentVar("AgentResult","載入SAP 設定時，發生錯誤 !!");
}catch ( Exception ex){}
}
}


public void processApprove() {
boolean isok;
Item notifyitem;
try{
System.out.println("Begin JCO Connection....");
log.logAction("Begin JCO Connection....");
client = JCO.createClient(sapClient, sapAccount, sapPasswd, sapLang, sapIP, sapSysNo);
client.connect();
JCO.Repository repository = new JCO.Repository( "Notes", client );
JCO.Attributes attr = client.getAttributes();
IFunctionTemplate ftemplate = repository.getFunctionTemplate( "RFC_READ_TABLE" );
function = new JCO.Function( ftemplate );
function1 = new JCO.Function( ftemplate );
function2 = new JCO.Function( ftemplate );
System.out.println("Begin Load Note Procsess Documents....");
log.logAction("Begin Load Note Procsess Documents....");
if (doc != null) {
String sn=doc.getItemValueString("ItemNo").replaceAll("\\s+", "").trim();
String sn2=doc.getItemValueString("Batch").replaceAll("\\s+", "").trim();
System.out.println("Begin Process ItemNo: " + sn + "-Batch-" + sn2);
log.logAction("Begin Process ItemNo: " + sn + "-Btach-" + sn2);

//取中文名稱
function.getImportParameterList().setValue(",","DELIMITER");
function.getImportParameterList().setValue("MAKT","QUERY_TABLE");
JCO.ParameterList inputTables  = function.getTableParameterList();
            JCO.Table table = inputTables.getTable("OPTIONS");
table.appendRows(1);
table.setValue("MATNR EQ '"+ sn +"' ", "TEXT");
function.setTableParameterList(inputTables);
client.execute( function );
JCO.Table returnTable1 = function.getTableParameterList().getTable("DATA");
int records = returnTable1.getNumRows();
if (records == 0) {
cnname = "N/A";
}
else {
returnTable1.setRow(1);
String[] aArray = returnTable1.getString("WA").split(",");
cnname=aArray[3];
}

//取庫存量
function1.getImportParameterList().setValue(",","DELIMITER");
function1.getImportParameterList().setValue("MCHB","QUERY_TABLE");
JCO.ParameterList inputTables1  = function1.getTableParameterList();
            JCO.Table table1 = inputTables1.getTable("OPTIONS");
table1.appendRows(1);
table1.setValue("MATNR EQ '"+ sn +"' AND CHARG EQ '"+ sn2 +"'", "TEXT");
function1.setTableParameterList(inputTables1);
client.execute( function1);
JCO.Table returnTable2 = function1.getTableParameterList().getTable("DATA");
int records2 = returnTable2.getNumRows();
if (records2 == 0) {
quantity = "N/A";
}
else {
returnTable2.setRow(1);
System.out.println("Get Data: " + returnTable2.getString("WA"));
String[] bArray = returnTable2.getString("WA").split(",");
quantity=bArray[13];
}

//取有效期
function2.getImportParameterList().setValue(",","DELIMITER");
function2.getImportParameterList().setValue("MCH1","QUERY_TABLE");
JCO.ParameterList inputTables2  = function2.getTableParameterList();
            JCO.Table table2 = inputTables2.getTable("OPTIONS");
table2.appendRows(1);
table2.setValue("MATNR EQ '"+ sn +"' AND CHARG EQ '"+ sn2 +"'", "TEXT");
function2.setTableParameterList(inputTables2);
client.execute( function2);
JCO.Table returnTable3 = function2.getTableParameterList().getTable("DATA");
int records3 = returnTable3.getNumRows();
if (records3 == 0) {
valid_date = "N/A";
}
else {
returnTable3.setRow(1);
System.out.println("Get Data: " + returnTable3.getString("WA"));
String[] cArray = returnTable3.getString("WA").split(",");
valid_date = cArray[9];
}
session.setEnvironmentVar("AgentResult",cnname+","+quantity+","+valid_date);
}
}catch(java.lang.NoClassDefFoundError e){
e.printStackTrace();
try{
session.setEnvironmentVar("AgentResult","SAP 元件未安裝!!");
}catch ( Exception ex){}
}
catch(Exception e) {
e.printStackTrace();
try{
log.logError(0,e.getLocalizedMessage());
session.setEnvironmentVar("AgentResult","代理程式發生不明錯誤!!");
}catch(Exception err){}
}finally{
try{
  JCO.releaseClient(client);
log.close();
}catch(Exception err){}
}
}

public void NotesMain() {

try {
Session session = getSession();
AgentContext agentContext = session.getAgentContext();
SAPApprove sapobj=new SAPApprove();
sapobj.init(session,agentContext);

Agent agent = agentContext.getCurrentAgent();

sapobj.processApprove();

} catch(Exception e) {
e.printStackTrace();
}
}
}