[AP] JDBC運用在procedure，將資料拋轉出去

'//JDBC運用在procedure
'//把excel第5欄的資料拋轉至CPIS中

Sub Initialize
'//將表單中附的excel file的設備編號，在結案時拋轉給CPIS
On Error Goto ErrorHandler
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 strSQL As String
Dim docQuery As NotesDocument
 '
Dim Session As New NotesSession
Dim SourceDoc As NotesDocument
Dim agent As NotesAgent
Dim SourceDB As NotesDatabase  
Dim dbdata As Variant, strDataSource As String

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


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

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

strDataSource = "apg_wsm"
dbdata = GetDBData(strDataSource)
If dbdata(0)="" Then
Goto ErrorHandler
End If

bClass.forName(dbdata(0)) 
Set bConnection = bDriverManager.getConnection(dbdata(1))
Set bStatement = bConnection.createStatement()

' --------------------* 請將你要客製化執行的程式放在這裡------------------------------------------------ 
 'select
%REM
 sql="xxxxxx"
 Set bResultSet = bStatement.executeQuery(sql)
 Do While bResultSet.next() 

 Loop
%END REM


    '//A.New release entity 新增機台|A
    '//B.Cancel release entity or re-layout 取消機台或移機|B

'//資料庫名稱@主機名稱：procedure的程式

strSQL = "Execute procedure getAELEntity (" +  "'" + docQuery.CustID(0) + "', "   '//CUSTID
strSQL = strSQL +  "'" + docQuery.SerialNumber(0) + "', "      '//serial no.
strSQL = strSQL +  "'" + docQuery.EntityNo(0) + "', "      '//entity no.
strSQL = strSQL +  "'" + docQuery.ActionItem(0) + "') "      '//ActionItem

Print strSQL
docQuery.SQLStatement = strSQL
Set bResultSet = bStatement.executeQuery(strSQL)
If bResultSet.Next() Then
If Trim(bResultSet.getString (1)) = "0000" Then
docQuery.Result = "S"
docQuery.ErrMsg_Code = bResultSet.getString (1)
docQuery.ErrMsg = bResultSet.getString (2)
Call docQuery.save(True,True)
Else
docQuery.Result = "F"
docQuery.ErrMsg_Code = bResultSet.getString (1)
docQuery.ErrMsg = bResultSet.getString (2)
Call docQuery.save(True,True)
End If
End If


' -------------------- ** 客製化到這裡結束 ----------------------------------------------------------------------------  
Call bResultSet.close()
Call bStatement.close()
Call bConnection.close()


TheEnd:
Print "agent__" + SourceDB.Title + "__" + agent.Name + "__RunAgentsOnServer end......." + Now()
Exit Sub
ErrorHandler:
Dim myError As JavaError
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