[AP] JDBC運用在procedure，將資料讀進來

'//JDBC，透過procedure，輸入LotNo取得相關資料

[Option]
Option Public
Uselsx "*javacon"

Use "AgnesLibrary"

---------------------------------------------------------

[Declarations]
Dim bClass As JAVACLASS
Dim bDriverManager As JavaClass
Dim bConnection As JavaObject
Dim bStatement As JavaObject
Dim bResultSet As javaObject

Dim i As Integer
Dim varLotNo As Variant
Dim aryCLotNo() As String
Dim aryPkgType() As String
Dim aryPartNo() As String
Dim aryTQty() As String
Dim aryCustID() As String
Dim aryCustNm() As String
Dim aryAssy() As String
Dim aryWaferSize() As String
Dim aryBin3() As String
Dim aryBin4() As String
Dim aryBin6() As String
Dim aryFactoryCode() As String
Dim strProduct As String

Dim strFailLotNo As String

---------------------------------------------------------

[Initialize]
Sub Initialize
'//(JDBC)以lotno透過procedure取得相關欄位資料
On Error Goto ErrorHandler
Dim javasession As New JAVASESSION
Dim sql As String, strSQL As String
'
Dim Session As New NotesSession
Dim SourceDoc As NotesDocument, docQuery As NotesDocument
Dim agent As NotesAgent
Dim SourceDB As NotesDatabase
Dim sysdoc As NotesDocument
Dim strServerNm As String

Redim aryCLotNo(0)
Redim aryPkgType(0)
Redim aryPartNo(0)
Redim aryTQty(0)
Redim aryCustID(0)
Redim aryCustNm(0)
Redim aryBin3(0)
Redim aryBin4(0)
Redim aryBin6(0)
Redim aryFactoryCode(0)

Set agent=session.CurrentAgent
Set SourceDb=session.CurrentDatabase
Set docQuery=SourceDb.GetDocumentByID(agent.ParameterDocID)
strServerNm = Strright(Ucase(SourceDB.Server), "/O=")

Set sysdoc = GetSystemDocument("Form-SystemSetup")
If sysdoc Is Nothing Then Print "not found SystempSetup!!" : Exit Sub

Print "agent__" + 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")

'Print "DataSourceNm_MPG = " + sysdoc.DataSourceNm_MPG(0)
dbdata = GetDBData2(sysdoc.DataSourceNm_MPG(0))
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

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


strSQL = "Execute procedure comets@hpk380s:notes_ng_report (" +  "'" + docQuery.LotNo(0) + "', "   '//LotNo
'strSQL = strSQL +  "'" + SourceDoc.Fab(0) + "') "      '//step
strSQL = strSQL +  "'" + "IP" + "') "      '//step
Print strSQL
docQuery.SQLStatement = strSQL

Set bResultSet = bStatement.executeQuery(strSQL)
If bResultSet.Next() Then
If Trim(bResultSet.getString (1)) <> "" Then
Print "QuerySuccess..."
docQuery.Result = "S"
Call QuerySuccess()
Else
Print "QueryFail..."
docQuery.Result = "F"
Call QueryFail()
'SourceDoc.ErrMsg_Code = bResultSet.getString (1)
'SourceDoc.ErrMsg = bResultSet.getString (2)
End If
End If

'Call bStatement.execute(strSQL)
'Call SourceDoc.ReplaceItemValue("AgentStatus","OK")


' -------------------- ** 客製化到這裡結束 ----------------------------------------------------------------------------

Call bStatement.close()
Call bConnection.close()

'Print " aryPartNo = " + aryPartNo(0)

docQuery.CusLotNo = aryCLotNo
docQuery.PartNo = aryPartNo
docQuery.TQty = aryTQty
docQuery.PKG_Type = aryPkgType
docQuery.Cus= aryCustID
docQuery.Cus_1= aryCustNm
docQuery.binItem1 = aryBin3
docQuery.binItem2 = aryBin4
docQuery.binItem3 = aryBin6
docQuery.FactoryCode = aryFactoryCode

Call docQuery.Save(True,False)




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

---------------------------------------------------------

[QuerySuccess]
Sub QuerySuccess
' Get Lot No
'Print "bResultSet.getString (1) = " + bResultSet.getString (1)
If aryCLotNo(0) = "" Then
aryCLotNo(0) = bResultSet.getString (1)
Else
Redim Preserve aryCLotNo(0 To Ubound(aryCLotNo) + 1)
aryCLotNo(Ubound(aryCLotNo)) = bResultSet.getString (1)
End If

' Get  PartNo
'Print "bResultSet.getString (2) = " + bResultSet.getString (2)
If aryPartNo(0) = "" Then
aryPartNo(0) = Trim(bResultSet.getString (2))
Else
Redim Preserve aryPartNo(0 To Ubound(aryPartNo) + 1)
aryPartNo(Ubound(aryPartNo)) = Trim(bResultSet.getString (2))
End If

' Get Total Qty
If aryTQty(0) = "" Then
aryTQty(0) = Cstr(bResultSet.getString (3))
Else
Redim Preserve aryTQty(0 To Ubound(aryTQty) + 1)
aryTQty(Ubound(aryTQty)) = Cstr(bResultSet.getString (3))
End If

' Get Pkg Type
If aryPkgType(0) = "" Then
aryPkgType(0) = Trim(bResultSet.getString (5)) + " " + Trim(bResultSet.getString (4))
Else
Redim Preserve aryPkgType(0 To Ubound(aryPkgType)+1)
aryPkgType(Ubound(aryPkgType)) = Trim(bResultSet.getString (5)) + " " + Trim(bResultSet.getString (4))
End If

' Get Customer
If aryCustID(0) = "" Then
If Len(Trim(bResultSet.getString (6))) = 2 Then
aryCustID(0) = "A" + Trim(bResultSet.getString (6))
Else
aryCustID(0) = bResultSet.getString (6)
End If
Else
Redim Preserve aryCustID(0 To Ubound(aryCustID)+1)
If Len(Trim(bResultSet.getString (6))) = 2 Then
aryCustID(Ubound(aryCustID)) ="A" + Trim(bResultSet.getString (6))
Else
aryCustID(Ubound(aryCustID)) = Trim(bResultSet.getString (6))
End If
End If

'// Get Customer Name
If aryCustNm(0) = "" Then
aryCustNm(0) = bResultSet.getString (7)
Else
Redim Preserve aryCustNm(0 To Ubound(aryCustNm)+1)
aryCustNm(Ubound(aryCustNm)) = bResultSet.getString (7)
End If

' Get Bin3
If aryBin3(0) = "" Then
aryBin3(0) = bResultSet.getString (8)
Else
Redim Preserve aryBin3(Ubound(aryBin3)+1) 
aryBin3(Ubound(aryBin3)) = bResultSet.getString (8)
End If

' Get Bin4
If aryBin4(0) = "" Then
aryBin4(0) = bResultSet.getString (9)
Else
Redim Preserve aryBin4(Ubound(aryBin4)+1) 
aryBin4(Ubound(aryBin4)) = bResultSet.getString (9)
End If

' Get Bin6
If aryBin6(0) = "" Then
aryBin6(0) = bResultSet.getString (10)
Else
Redim Preserve aryBin6(Ubound(aryBin6)+1) 
aryBin6(Ubound(aryBin6)) = bResultSet.getString (10)
End If

' Get FactoryCode
Print "bResultSet.getString (11) = " + Cstr(bResultSet.getString (11))
If aryFactoryCode(0) = "" Then
aryFactoryCode(0) = "F" + bResultSet.getString (11)
Else
Redim Preserve aryFactoryCode(0 To Ubound(aryFactoryCode)+1)
aryFactoryCode(Ubound(aryFactoryCode)) = "F" + bResultSet.getString (11)
End If

End Sub

---------------------------------------------------------

[QueryFail]
Sub QueryFail
' Get Customer Lot No
If aryCLotNo(0) = "" Then
aryCLotNo(0) = "-"
Else
Redim Preserve aryCLotNo(0 To Ubound(aryCLotNo) + 1)
aryCLotNo(Ubound(aryCLotNo)) = "-"
End If

    ' Get  PartNo
If aryPartNo(0) = "" Then
aryPartNo(0) = "-"
Else
Redim Preserve aryPartNo(0 To Ubound(aryPartNo) + 1)
aryPartNo(Ubound(aryPartNo)) = "-"
End If

' Get Total Qty
If aryTQty(0) = "" Then
aryTQty(0) = "0"
Else
Redim Preserve aryTQty(0 To Ubound(aryTQty) + 1)
aryTQty(Ubound(aryTQty)) = "0"
End If

' Get Pkg Type
If aryPkgType(0) = "" Then
aryPkgType(0) = "-"
Else
Redim Preserve aryPkgType(0 To Ubound(aryPkgType)+1)
aryPkgType(Ubound(aryPkgType)) ="-"
End If

    ' Get Vendor
If aryCustID(0) = "" Then
aryCustID(0) = "-"
Else
Redim Preserve aryCustID(0 To Ubound(aryCustID)+1)
aryCustID(Ubound(aryCustID)) ="-"
End If
If aryCustNm(0) = "" Then
aryCustNm(0) = "-"
Else
Redim Preserve aryCustNm(0 To Ubound(aryCustNm)+1)
aryCustNm(Ubound(aryCustNm)) ="-"
End If

' Get Bin3
If aryBin3(0) = "" Then
aryBin3(0) = "-"
Else
Redim Preserve aryBin3(0 To Ubound(aryBin3)+1)
aryBin3(Ubound(aryBin3)) ="-"
End If

' Get Bin4
If aryBin4(0) = "" Then
aryBin4(0) = "-"
Else
Redim Preserve aryBin4(0 To Ubound(aryBin4)+1)
aryBin4(Ubound(aryBin4)) ="-"
End If

' Get Bin6
If aryBin6(0) = "" Then
aryBin6(0) = "-"
Else
Redim Preserve aryBin6(0 To Ubound(aryBin6)+1)
aryBin6(Ubound(aryBin6)) ="-"
End If

' Get FactoryCode
If aryFactoryCode(0) = "" Then
aryFactoryCode(0) = "-"
Else
Redim Preserve aryFactoryCode(0 To Ubound(aryFactoryCode)+1)
aryFactoryCode(Ubound(aryFactoryCode)) ="-"
End If

End Sub

---------------------------------------------------------

[ReFormat]
Function ReFormat(Source As String)
'格式化改版理由,將字串中的 \',\" 符號換成空白
Dim target As String
Dim i As Integer
i=1
While Not Mid(source,i,1)=""
p=Mid(Source,i,1)
q=Asc(p)
'如果取得換行符號時,將它換成空白並跳過一個字元
'因為它會佔用二個字元
If q=34 Or q=39 Or q=13 Or q=10 Or q=2Then   ' 前面五個數字分別代表下面字元 ""/'/CR/LF STX(TEXT START)
target=target+""
'i=i+1
Else
target=target+Mid(Source,i,1)
End If
i=i+1
Wend
Reformat=Ucase(target)

End Function

---------------------------------------------------------

[GetDBData2]
Function GetDBData2(dbname As String) As Variant
On Error Goto errorhandler
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
GetDBData2 = returndata

TheEnd:
Exit Function
ErrorHandler:
Print "Lib: AgnesLibrary: GetDBData function error line = " + Str(Erl) + ", error = " + Error$
Resume TheEnd

End Function

=====================================================

在LotNo欄位，設定輸入資料滑鼠離開欄位時觸發[Exiting]動作
Sub Exiting(Source As Field)

Dim session As New NotesSession
Dim db As NotesDatabase
Dim ws As New NotesUiWorkSpace
Dim uidoc As NotesUiDocument
Dim doc As NotesDocument
Dim agent As NotesAgent
Dim docQuery_BE As NotesDocument
Dim docQuery As NotesDocument
Dim view As NotesView
Dim itemAuthor As NotesItem
Dim varAuthor As Variant
Dim DocID As String

Set db = session.CurrentDatabase
Set uidoc = ws.CurrentDocument
Set  doc = uidoc.Document
' Set view = db.GetView("ByNoteId")

' A document to store the data query by the agent running on server
If Trim(uidoc.FieldGetText("LotNo")) <> "" Then
Print "擷取後端資料中....."
Set docQuery = db.CreateDocument
docQuery.Form = "QueryFormQAR"
docQuery.LotNo = doc.LotNo

' varAuthor = doc.DefaultDocAuthors
' Redim Preserve varAuthor(0 To Ubound(varAuthor)+1)
' varAuthor(Ubound(varAuthor)) = session.UserName
' Set itemAuthor = New NotesItem(docQuery,"Authors",varAuthor,AUTHORS)

Call  docQuery.save(True,True)
DocID =  docQuery.NoteId
Set docQuery = Nothing

Set agent = db.GetAgent("QueryByLotNo")
If agent.runonserver(DocID ) = 0 Then
Else
Messagebox "無法連結後端資料庫",, "Failure"
Exit Sub
End If
' view.refresh

Set docQuery_BE = db.GetDocumentById( DocID )
doc.CusLotNo = docQuery_BE.CusLotNo
doc.PartNo = docQuery_BE.PartNo
doc.PKG_Type = docQuery_BE.PKG_Type
doc.TQty = docQuery_BE.TQty
doc.Cus = docQuery_BE.Cus
doc.Cus_1 = docQuery_BE.Cus_1
doc.Bin3 = docQuery_BE.Bin3
doc.Bin4 = docQuery_BE.Bin4
doc.Bin6 = docQuery_BE.Bin6
doc.FactoryCode = docQuery_BE.FactoryCode


Call uidoc.refresh

' Call docQuery_BE .Remove(True)
Print "擷取後端資料完成....."
End If

uidoc.refresh

End Sub