[AP] 用JDBC的寫法，將資料拋轉出去

'//在ODBC設定文件上增加ClassName, Db Connect String欄位
系統直接call設定件件使用




Option Public
Uselsx "*javacon"

Sub Initialize
On Error Goto ErrorHandle
Dim javasession As New JAVASESSION
Dim bClass As JAVACLASS
Dim bDriverManager As JavaClass
Dim bConnection As JavaObject
Dim bStatement As JavaObject
Dim bResultSet As javaObject
Dim sql As String
'
Dim Session As New NotesSession
Dim SourceDoc As NotesDocument
Dim agent As NotesAgent
Dim SourceDB As NotesDatabase

Set agent=session.CurrentAgent
Set SourceDb=session.CurrentDatabase
Set SourceDoc=SourceDb.GetDocumentByID(agent.ParameterDocID)

Print SourceDB.Title + "__" + agent.Name + "__ run start..." + Now()

Set javasession = New JAVASESSION()
Set bClass = javasession.GetClass("java.lang.Class")
Set bDriverManager = javasession.GetClass("java.sql.DriverManager")

dbdata = GetDBData("comets_ws")
If dbdata(0)="" Then
Goto ErrorHandle
End If

bClass.forName(dbdata(0)) 
Set bConnection = bDriverManager.getConnection(dbdata(1))
Set bStatement = bConnection.createStatement()
Print "for debug -1"
' --------------------* 請將你要客製化執行的程式放在這裡------------------------------------------------
'select
%REM
sql="xxxxxx"
Set bResultSet = bStatement.executeQuery(sql)
Do While bResultSet.next() 

Loop
%END REM

'ins or del or update
'sql="xxxxx"
'Call bStatement.execute(sql)

SQL = "select * from notes_prs where prs_number='" & SourceDoc.PRS_No(0) & "'"
Print "for debug -2"
Print Sql
Set bResultSet = bStatement.executeQuery(Sql)
Print "for debug -3"
If bResultSet.Next() Then
Print "for debug -4"
Sql = "update notes_prs set prs_station='" & SourceDoc.Station(0) &_
"',_prs_status_time='" & Format(Now,"YYYY-MM-DD hh:mm:ss") &_
"', prs_status='" & SourceDoc.Status(0) &_
"' where prs_number='" & SourceDoc.PRS_No(0) & "'"
Else
Print "for debug -5"
Sql = "insert into notes_prs (prs_lot_no,prs_number,prs_station,prs_status,prs_insert_time,prs_deleted) values('" &_
ReFormat(SourceDoc.LotNo(0)) & "','" &_
SourceDoc.PRS_No(0) & "','" &_
ReFormat(SourceDoc.Station(0)) & "','" &_
SourceDoc.Status(0) & "','" &_
Format(Now,"YYYY-MM-DD hh:mm:ss") & "','N')"
End If

Print "for debug -6"
Call bStatement.execute(SQL)
Call SourceDoc.ReplaceItemValue("AgentStatus","OK")
Call SourceDoc.Save(True,False)

' -------------------- ** 客製化到這裡結束 ----------------------------------------------------------------------------
Print "for debug -7"
Call bStatement.close()
Call bConnection.close()

Print SourceDB.Title + "__" + agent.Name + "__ run end..." + Now()
Exit Sub


ErrorHandle:
Set myError = javasession.getLastJavaError()
Print Cstr(myError.errormsg)
Print Error() & "    ErrLine : " & Cstr(Erl())
If Not bResultSet Is Nothing Then  Call bResultSet.close()
If Not bStatement Is Nothing Then Call bStatement.close()
If Not bConnection Is Nothing Then Call bConnection.close()
javasession.ClearJavaError
Exit Sub
End Sub

Function GetDBData(dbname As String) As Variant
Dim s As New NotesSession
Dim viewODBC As NotesView
Dim docODBC As NotesDocument 
Dim odbcdb As NotesDatabase
Dim returndata(1) As String

Set odbcdb  = New NotesDatabase(s.CurrentDatabase.Server,"ap\sys\odbc.nsf")
Set viewODBC = odbcdb.GetView("View_DSName")
Set docODBC = viewODBC.getdocumentByKey(dbname,True)

If Not docODBC Is Nothing Then
returndata(0) = docODBC.ClassName(0)
returndata(1) = docODBC.ConnectString(0)
End If
GetDBData = returndata
End Function