'-----------------------------------------------------------------------------
'-
'- Start a QlikView 11 EDX Task and monitor the execution trough QMS API
'-
'-----------------------------------------------------------------------------
'-
'- This sample is provided 'AS-IS', without any express or implied warranty.
'-
'- In no event will the authors be held liable for any damages arising from
'- the use of this sample code.
'-
'-----------------------------------------------------------------------------
'-
'- The MIT License (MIT)
'-
'- Copyright (c) 2012 Alberto Luigi Piccoli - QV Italy - http://www.qlikviewitaly.com/
'-
'- Permission is hereby granted, free of charge, to any person obtaining a copy
'- of this software and associated documentation files (the "Software"), to deal
'- in the Software without restriction, including without limitation the rights
'- to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
'- copies of the Software, and to permit persons to whom the Software is
'- furnished to do so, subject to the following conditions:
'-
'- The above copyright notice and this permission notice shall be included in
'- all copies or substantial portions of the Software.
'-
'- THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
'- IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
'- FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
'- AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
'- LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
'- OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
'- THE SOFTWARE.
'-
'-----------------------------------------------------------------------------
'- History
'-----------------------------------------------------------------------------
'-
'- 2012-09-05 - 01.00 - Initial release 01.00
'- 2012-12-01 - 01.10 - Fix automatic inheritance of the QlikView session user
'- to execute the SOAP calls
'- 2012-12-05 - Automatic discovery of client Plug-in or Ajax
'- - Ajax add wait end of excution Ajax
'-
'-----------------------------------------------------------------------------
'- NOTES
'-----------------------------------------------------------------------------
'-
'- Using Full Browser Verision (Ajax) Macro & Unsafe macro execution
'- must be enaled on the server
'- on QMC > System > QVS > Security Tab
'-
'-----------------------------------------------------------------------------
'- PARAMETERS
'-----------------------------------------------------------------------------
'
' Public variables
Public sXmlElement, sXmlKeyValue, sKeyValue, sReturnCode
' Qlikview Server API Endpoint - QMS API
Const sWebServiceURL="http://server08:4799/QMS/Service"
Const sSoapNameSpace="http://ws.qliktech.com/QMS/11/IQMS/"
'-----------------------------------------------------------------------------
'- Windows Credentials
'-----------------------------------------------------------------------------
'-
'- Windows Credentials - to execute an moinitor the task the user must be:
'- * member of groups "QlikView EDX", "QlikView Management API" and Document Folder Administrator
'- or
'- * member of group "Qlikview Administrators"
'-
'- If credential are omitted
'- - using plug-in the Windows session user is inherited to execute the SOAP calls
'- - using AJAX user running the QlikView Server Service is inherited to execute the SOAP calls
'-
Const sUser="" 'INSERT HERE THE USER (ADMIN)
Const sUserPassword="" 'INSERT HERE THE PASSWORD
'-----------------------------------------------------------------------------
'- Task Name and Password
'-----------------------------------------------------------------------------
Const sTaskName="myFile.qvw" 'INSERT HERE THE TASK NAME AS CREATED IN QV SERVER
Const sTaskPassword="XXX" 'INSERT HERE THE TASK PASSWORD AS INSERTED IN QV SERVER
'-----------------------------------------------------------------------------
'- ServiceSoapRequest
'-----------------------------------------------------------------------------
'Class for sending SOAP 1.2 request
Class ServiceSoapRequest
Private oWinHttp,sContentType
'Public sWebServiceURL, sSOAPAction, sSOAPRequest,sResponse,servicename
Public sSOAPAction, sSOAPRequest,sResponse,servicename
Private Sub Class_Initialize
Set oWinHttp = CreateObject("WinHttp.WinHttpRequest.5.1")
'Web Service Content Type
sContentType ="text/xml;charset=UTF-8"
End Sub
Public Function SetSoapAction(servicename)
'sWebServiceURL = sWebServiceURL
sSoapAction = sSoapNameSpace & servicename
End Function
Public Function SendRequest
'Open HTTP connection
oWinHttp.Open "PUT", sWebServiceURL, False
'Setting request headers
oWinHttp.setRequestHeader "Content-Type", sContentType
oWinHttp.setRequestHeader "SOAPAction", sSOAPAction
if len(trim(sKeyValue))>0 then
oWinHttp.setRequestHeader "X-Service-Key", sKeyValue
end if
If (sUser <> "") And (sUserPassword <> "") Then
oWinHttp.SetCredentials sUser, sUserPassword, 0
else
' if not username and password are specified the macro
' try to use current user credentials
oWinHttp.SetAutoLogonPolicy(0)
end if
'Send SOAP request
oWinHttp.Send sSOAPRequest
'Get XML Response
sResponse = oWinHttp.ResponseText
End Function
Public Function ExtractXmlValue
set oXmlDoc=CreateObject("Microsoft.XMLDOM")
oXmlDoc.async="false"
oXmlDoc.loadXML sResponse
if oXmlDoc.parseError.errorCode <> 0 then
msgbox("Error Code: " & oXmlDoc.parseError.errorCode) & vbCrLf & vbCrLf & _
("Error Reason: " & oXmlDoc.parseError.reason) & vbCrLf & vbCrLf & _
("Error Line: " & oXmlDoc.parseError.line)
end if
sXmlKeyValue = oXmlDoc.GetElementsByTagName(sXmlElement).item(0).text
set oXmlDoc= Nothing
End Function
Public Function Close
Set oWinHttp = Nothing
End Function
End Class
'-----------------------------------------------------------------------------
'- fnGetTimeLimitedServiceKeyResult
'-----------------------------------------------------------------------------
'Request a Time Limited Service Key to QMS to use in the header of each request
Function fnGetTimeLimitedServiceKeyResult
' XML Soap Request to get Time Limited Service Key
Set objSOAP = New ServiceSoapRequest
objSOAP.SetSoapAction("GetTimeLimitedServiceKey")
objSOAP.sSOAPRequest = "" & _
"" & _
"" & _
"" & _
"" & _
""
' Send Soap Request
objSOAP.SendRequest
sResponse = objSOAP.sResponse
' Extract Time Limited Service Key
sXmlElement="GetTimeLimitedServiceKeyResult"
objSOAP.ExtractXmlValue
sKeyValue = sXmlKeyValue
Set objSOAP = Nothing
End Function
'-----------------------------------------------------------------------------
'- sbCleanBox
'-----------------------------------------------------------------------------
sub sbCleanBox
set svMsgBoxTxtVar = ActiveDocument.GetVariable("vAjaxMsg")
sMsgTxt = ""
svMsgBoxTxtVar.SetContent sMsgTxt, true
' set vmvMsgBoxTxtVar = ActiveDocument.Variables("vAjaxMsg")
' vmvMsgBoxTxtVar.SetContent "",true
'
' set vmMsgBoxShow=ActiveDocument.Variables("svMsgBoxShow")
' vmMsgBoxShow.SetContent 0, true
' vmMsgBoxShow.SetContent 1, true
end sub
'-----------------------------------------------------------------------------
'- fnStartEDXandMonitorAllClient
'-----------------------------------------------------------------------------
function fnStartEDXandMonitorAllClient
Platform = ActiveDocument.Evaluate("ClientPlatform()")
if len(Trim(Platform))=0 then
msgbox "My Client is Plug-in or Local Client"
call fnStartEDXandMonitorPlugIn()
else
sMsgTxt = "My Client is Ajax using - " & Platform
ActiveDocument.GetVariable("vAjaxMsg").SetContent sMsgTxt, true
call fnStartEDXandMonitorAjax()
end if
end function
'-----------------------------------------------------------------------------
'- fnStartEDXandMonitorAjax
'-----------------------------------------------------------------------------
function fnStartEDXandMonitorAjax
'-----------------------------------------------------------------------------
'- Get QDS & Task GUID
'-----------------------------------------------------------------------------
' Get Service Key
fnGetTimeLimitedServiceKeyResult
'msgbox sKeyValue
' XML Soap Request to get EDX TASK UID e QDS UID
Set objSOAP = New ServiceSoapRequest
objSOAP.SetSoapAction("FindEDX")
objSOAP.sSOAPRequest ="" & _
"" & _
"" & _
""& sTaskName &"" & _
"" & _
"" & _
""
'msgbox objSOAP.sSOAPRequest
' Send Soap Request
objSOAP.SendRequest
sResponse = objSOAP.sResponse
'msgbox sResponse
' Extract GUID of the Task EDX
sXmlElement="a:ID"
objSOAP.ExtractXmlValue
sTaskID = sXmlKeyValue
'msgbox sTaskID
' Extract GUID of the QDS
sXmlElement="a:QDSID"
objSOAP.ExtractXmlValue
sQDSID = sXmlKeyValue
'msgbox sQDSID
'-----------------------------------------------------------------------------
'- Start Task EDX
'-----------------------------------------------------------------------------
' XML Soap Request to Start Task
Set objSOAP = New ServiceSoapRequest
objSOAP.SetSoapAction("TriggerEDXTask")
objSOAP.sSOAPRequest ="" & _
"" & _
"" & _
"" & sQDSID & "" & _
"" & sTaskID & "" & _
"" & sTaskPassword & "" & _
"" & _
"" & _
""
'msgbox objSOAP.sSOAPRequest
' Send Soap Request
objSOAP.SendRequest
sResponse = objSOAP.sResponse
'msgbox sResponse
' Extract Execution Result
sXmlElement="a:EDXTaskStartResult"
objSOAP.ExtractXmlValue
sTaskEDXTaskStartResult = sXmlKeyValue
'msgbox sTaskEDXTaskStartResult
' Extract Execution Result code
sXmlElement="a:EDXTaskStartResultCode"
objSOAP.ExtractXmlValue
sEDXTaskStartResultCode = sXmlKeyValue
' Extract Task Execution GUID
sXmlElement="a:ExecId"
objSOAP.ExtractXmlValue
sExecId = sXmlKeyValue
'msgbox sEDXTaskStartResultCode
' Extract TaskStatus
'sXmlElement="a:Status"
'objSOAP.ExtractXmlValue
'sStatus = sXmlKeyValue
'msgbox sStatus
if sTaskEDXTaskStartResult = "Success" then
'-----------------------------------------------------------------------------
'- If the Task is correctly started then monitor the execution
'-----------------------------------------------------------------------------
sMsgTxt = "Task " & sTaskName & " successfully started" & vbCrLf & vbCrLf & _
"Monitor of execution"
ActiveDocument.GetVariable("vAjaxMsg").SetContent sMsgTxt, true
' Clean loop variable
sResultStatus=""
' Wait time before first execution check without pause the check start before the task startup
call fnGetToSleepAJax("10")
' Loop to chehk the end of the Task
while sResultStatus <> "Completed"
' Get new Service Key to prevent to be expired
fnGetTimeLimitedServiceKeyResult
' XML Soap Request to get Task Status
Set objSOAP = New ServiceSoapRequest
objSOAP.SetSoapAction("GetEDXTaskStatus")
objSOAP.sSOAPRequest = "" & _
"" & _
"" & _
""&sQDSID&"" & _
""&sExecId&"" & _
"" & _
"" & _
""
'msgbox objSOAP.sSOAPRequest
' Send Soap Request
objSOAP.SendRequest
sResponse = objSOAP.sResponse
' Extract Result Status
sXmlElement="a:TaskStatus"
objSOAP.ExtractXmlValue
sResultStatus = sXmlKeyValue
' Extract Task Start Time
'sXmlElement="a:StartTime"
'objSOAP.ExtractXmlValue
'sTaskStartTime = sXmlKeyValue
' Extract Task End Time
'sXmlElement="a:FinishTime"
'objSOAP.ExtractXmlValue
'sTaskFinishTime = sXmlKeyValue
' Wait time between check
call fnGetToSleepAJax("10")
' Output progress to user
'msgbox sResultStatus
Wend
'-----------------------------------------------------------------------------
'- Check the excution results
'-----------------------------------------------------------------------------
' Wait time before check (some time it gets still running without timeout)
call fnGetToSleepAJax("20")
' Get new Service Key to prevent to be expired
fnGetTimeLimitedServiceKeyResult
' XML Soap Request to get Task Execution Results
Set objSOAP = New ServiceSoapRequest
objSOAP.SetSoapAction("GetTaskStatus")
objSOAP.sSOAPRequest = "" & _
"" & _
"" & _
""&sTaskID&"" & _
"All" & _
"" & _
"" & _
""
'msgbox objSOAP.sSOAPRequest
' Send Soap Request
objSOAP.SendRequest
sResponse = objSOAP.sResponse
'msgbox sResponse
' Extract Task excution Result Status
sXmlElement="a:Status"
objSOAP.ExtractXmlValue
sTaskStatus = sXmlKeyValue
'msgbox sXmlKeyValue
' Extract Task excution Log message
'sXmlElement="a:LastLogMessages"
'objSOAP.ExtractXmlValue
'sTaskStatusLogMsg = sXmlKeyValue
' Extract Task excution Finish Time
'sXmlElement="a:FinishTime"
'objSOAP.ExtractXmlValue
'sTaskStatusFinishTime = sXmlKeyValue
' Check if the Task was completed with Success
sTaskStatusResust = Instr(1, sResponse, "finished successfully", 1)
'msgbox sTaskStatusResust
if sTaskStatusResust > 0 then
sMsgTxt = "Task " & sTaskName & " finished successfully"
ActiveDocument.GetVariable("vAjaxMsg").SetContent sMsgTxt, true
ActiveDocument.GetVariable("vAjaxMsg2").SetContent sMsgTxt, true
sReturnCode = "0"
else
sMsgTxt = "Task " & sTaskName & " finished with errors:" & vbCrLf & vbCrLf & _
sResponse
ActiveDocument.GetVariable("vAjaxMsg").SetContent sMsgTxt, true
ActiveDocument.GetVariable("vAjaxMsg2").SetContent sMsgTxt, true
sReturnCode = "1"
end if
else
'-----------------------------------------------------------------------------
'- The task start-up is failed
'-----------------------------------------------------------------------------
sMsgTxt = sMsgTxt & vbCrLf & vbCrLf & "Task " & sTaskName & " is not started correctly with following messagge: " & vbCrLf & _
sTaskEDXTaskStartResult
ActiveDocument.GetVariable("vAjaxMsg").SetContent sMsgTxt, true
sReturnCode = sEDXTaskStartResultCode
end if
'-----------------------------------------------------------------------------
'- End
'-----------------------------------------------------------------------------
Set objSOAP = Nothing
fnGetTimeLimitedServiceKeyResultPlugIn = sReturnCode
end function
'-----------------------------------------------------------------------------
'- fnStartEDXandMonitorPlugIn
'-----------------------------------------------------------------------------
function fnStartEDXandMonitorPlugIn
'-----------------------------------------------------------------------------
'- Get QDS & Task GUID
'-----------------------------------------------------------------------------
'msgbox "a"
' Get Service Key
fnGetTimeLimitedServiceKeyResult
'msgbox sKeyValue
' XML Soap Request to get EDX TASK UID e QDS UID
Set objSOAP = New ServiceSoapRequest
objSOAP.SetSoapAction("FindEDX")
objSOAP.sSOAPRequest ="" & _
"" & _
"" & _
""& sTaskName &"" & _
"" & _
"" & _
""
'msgbox objSOAP.sSOAPRequest
' msgbox "b"
' Send Soap Request
objSOAP.SendRequest
sResponse = objSOAP.sResponse
'msgbox sResponse
' Extract GUID of the Task EDX
sXmlElement="a:ID"
objSOAP.ExtractXmlValue
sTaskID = sXmlKeyValue
'msgbox sTaskID
' Extract GUID of the QDS
sXmlElement="a:QDSID"
objSOAP.ExtractXmlValue
sQDSID = sXmlKeyValue
'msgbox sQDSID
'-----------------------------------------------------------------------------
'- Start Task EDX
'-----------------------------------------------------------------------------
' XML Soap Request to Start Task
Set objSOAP = New ServiceSoapRequest
objSOAP.SetSoapAction("TriggerEDXTask")
objSOAP.sSOAPRequest ="" & _
"" & _
"" & _
"" & sQDSID & "" & _
"" & sTaskID & "" & _
"" & sTaskPassword & "" & _
"" & _
"" & _
""
'msgbox objSOAP.sSOAPRequest
' Send Soap Request
objSOAP.SendRequest
sResponse = objSOAP.sResponse
'msgbox sResponse
' Extract Execution Result
sXmlElement="a:EDXTaskStartResult"
objSOAP.ExtractXmlValue
sTaskEDXTaskStartResult = sXmlKeyValue
'msgbox sTaskEDXTaskStartResult
' Extract Execution Result code
sXmlElement="a:EDXTaskStartResultCode"
objSOAP.ExtractXmlValue
sEDXTaskStartResultCode = sXmlKeyValue
' Extract Task Execution GUID
sXmlElement="a:ExecId"
objSOAP.ExtractXmlValue
sExecId = sXmlKeyValue
'msgbox sEDXTaskStartResultCode
' Extract TaskStatus
'sXmlElement="a:Status"
'objSOAP.ExtractXmlValue
'sStatus = sXmlKeyValue
'msgbox sStatus
if sTaskEDXTaskStartResult = "Success" then
'-----------------------------------------------------------------------------
'- If the Task is correctly started then monitor the execution
'-----------------------------------------------------------------------------
msgbox "Task " & sTaskName & " successfully started" & vbCrLf & vbCrLf & _
"Monitor of execution"
' Clean loop variable
sResultStatus=""
' Wait time before first execution check without pause the check start before the task startup
ActiveDocument.GetApplication.Sleep 10000
' Loop to chehk the end of the Task
while sResultStatus <> "Completed"
' Wait time between check
ActiveDocument.GetApplication.Sleep 10000
' Get new Service Key to prevent to be expired
fnGetTimeLimitedServiceKeyResult
' XML Soap Request to get Task Status
Set objSOAP = New ServiceSoapRequest
objSOAP.SetSoapAction("GetEDXTaskStatus")
objSOAP.sSOAPRequest = "" & _
"" & _
"" & _
""&sQDSID&"" & _
""&sExecId&"" & _
"" & _
"" & _
""
'msgbox objSOAP.sSOAPRequest
' Send Soap Request
objSOAP.SendRequest
sResponse = objSOAP.sResponse
' Extract Result Status
sXmlElement="a:TaskStatus"
objSOAP.ExtractXmlValue
sResultStatus = sXmlKeyValue
' Extract Task Start Time
'sXmlElement="a:StartTime"
'objSOAP.ExtractXmlValue
'sTaskStartTime = sXmlKeyValue
' Extract Task End Time
'sXmlElement="a:FinishTime"
'objSOAP.ExtractXmlValue
'sTaskFinishTime = sXmlKeyValue
' Output progress to user
'msgbox sResultStatus
Wend
'-----------------------------------------------------------------------------
'- Check the excution results
'-----------------------------------------------------------------------------
' Wait time before check (some time it gets still running without timeout)
ActiveDocument.GetApplication.Sleep 20000
' Get new Service Key to prevent to be expired
fnGetTimeLimitedServiceKeyResult
' XML Soap Request to get Task Execution Results
Set objSOAP = New ServiceSoapRequest
objSOAP.SetSoapAction("GetTaskStatus")
objSOAP.sSOAPRequest = "" & _
"" & _
"" & _
""&sTaskID&"" & _
"All" & _
"" & _
"" & _
""
'msgbox objSOAP.sSOAPRequest
' Send Soap Request
objSOAP.SendRequest
sResponse = objSOAP.sResponse
'msgbox sResponse
' Extract Task excution Result Status
sXmlElement="a:Status"
objSOAP.ExtractXmlValue
sTaskStatus = sXmlKeyValue
'msgbox sXmlKeyValue
' Extract Task excution Log message
'sXmlElement="a:LastLogMessages"
'objSOAP.ExtractXmlValue
'sTaskStatusLogMsg = sXmlKeyValue
' Extract Task excution Finish Time
'sXmlElement="a:FinishTime"
'objSOAP.ExtractXmlValue
'sTaskStatusFinishTime = sXmlKeyValue
' Check if the Task was completed with Success
sTaskStatusResust = Instr(1, sResponse, "finished successfully", 1)
'msgbox sTaskStatusResust
if sTaskStatusResust > 0 then
msgbox "Task " & sTaskName & " finished successfully"
sReturnCode = "0"
else
msgbox "Task " & sTaskName & " finished with errors:" & vbCrLf & vbCrLf & _
sResponse
sReturnCode = "1"
end if
else
'-----------------------------------------------------------------------------
'- The task start-up is failed
'-----------------------------------------------------------------------------
msgbox "Task " & sTaskName & " is not started correctly with following messagge: " & sTaskEDXTaskStartResult
sReturnCode = sEDXTaskStartResultCode
end if
'-----------------------------------------------------------------------------
'- End
'-----------------------------------------------------------------------------
Set objSOAP = Nothing
fnGetTimeLimitedServiceKeyResultPlugIn = sReturnCode
end function
function fnOsUser
Dim objNet
On Error Resume Next
'In case we fail to create object then display our custom error
Set objNet = CreateObject("WScript.NetWork")
If Err.Number <> 0 Then 'If error occured then display notice
MsgBox "Don't be Shy." & vbCRLF &_
"Do not press ""No"" If your browser warns you."
Document.Location = "UserInfo.html"
'Place the Name of the document.
'It will display again
End if
Dim strInfo
strInfo = "User Name is " & objNet.UserName & vbCRLF & _
"Computer Name is " & objNet.ComputerName & vbCRLF & _
"Domain Name is " & objNet.UserDomain
fnOsUser = objNet.UserDomain & "\" & objNet.UserName
ActiveDocument.GetVariable("vAjaxMsg").SetContent strInfo, true
'MsgBox strInfo
Set objNet = Nothing 'Destroy the Object to free the Memory
end function
function fnGetToSleepAJax(nSecWait)
'strCmd = "%COMSPEC% /c ping -n " & nSecWait & " 127.0.0.1"
'strCmd = "CHOICE /C ync /T " & nSecWait '& " > nul"
strCmd = "timeout /T " & nSecWait
set WScriptShell = CreateObject("WScript.Shell")
WScriptShell.Run strCmd,0,1
set WScriptShell = Nothing
end function
sub sbTestSleep
sMsgTxt = now()
ActiveDocument.GetVariable("vAjaxMsg").SetContent sMsgTxt, true
call fnGetToSleepAJax(10)
sMsgTxt = sMsgTxt & vbcrlf & now()
ActiveDocument.GetVariable("vAjaxMsg").SetContent sMsgTxt, true
end sub