PDA

View Full Version : Use WinHttpRequest#responseStream for SOAP Request



puperzenkarl
12-12-2016, 02:41 AM
I have written a VBA Add-In for Excel which calls a SOAP Web Service and prints the XML response data to a worksheet. I am using the SOAP toolkit, but I need a replacement. It is not supported any longer and it does not perform very well on big responses. SoapClient30 and lower APIs like XMLHTTP do not support HTTP chunking. Therefore the full response is loaded into memory and then parsed into a DOM. Not very fast and inefficient.

My idea: I would like to use WinHttp 5.1 which works with HTTP chunking. My Web Service now supports HTTP chunking and "streams" the data to the VBA application. While the data is coming in, I would like to use a SAX reader to read the response and process the data. This should be faster and consume less memory.



Public Sub SoapTest()
Dim pRequest As New WinHttpRequest
pRequest.Open "POST", "serverUrl", True
pRequest.SetRequestHeader "SOAPAction", """soapAction"""
pRequest.SetRequestHeader "Content-Type", "text/xml; charset=""UTF-8"""
pRequest.SetRequestHeader "User-Agent", "FIRST Excel Add-In"
pRequest.Send CreateGetListMessage
pRequest.WaitForResponse (100)

Dim pReader As New SAXXMLReader60
Dim pContentHandler As New SAXHandlerImplPrintSheet
pReader.Parse pRequest.ResponseStream
End Sub


I can see the SOAP request in my network tracing tool and the outgoing XML response. SAXHandlerImplPrintSheet implements IVBSAXContentHandler and should print out some debug statements to console when the document/element starts/ends, but it does nothing. I am afraid, that there are some limitations to VBA, so that my concept will never work. Is it event possible?

puperzenkarl
12-12-2016, 04:48 AM
There was an error. The following statement is missing before parse:

Set pReader.contentHandler = pContentHandler

It works now, but WaitForResponse is waiting until full reponse is loaded. So there is no difference to what I am doing now with the SOAP toolkit.
Next try is using the events.



Option Explicit

Private WithEvents soap As WinHttpRequest

Private Sub Class_Initialize()
Set soap = New WinHttpRequest
soap.SetTimeouts 0, 60000, 30000, 600000
End Sub

Private Sub Class_Terminate()
Set soap = Nothing
End Sub

Private Sub soap_OnResponseStart(ByVal Status As Long, ByVal ContentType As String)
Debug.Print "WinHttpRequest#OnResponseStart"
End Sub

Private Sub soap_OnResponseDataAvailable(data() As Byte)
'Debug.Print "WinHttpRequest#OnResponseDataAvailable"
End Sub

Private Sub soap_OnResponseFinished()
Debug.Print "WinHttpRequest#OnResponseDataAvailable"
End Sub

Private Sub soap_OnError(ByVal ErrorNumber As Long, ByVal ErrorDescription As String)
Debug.Print "WinHttpRequest#OnError"
End Sub

Public Sub Send()
soap.Open "POST", "...", True
soap.SetRequestHeader "SOAPAction", """..."""
soap.SetRequestHeader "Content-Type", "text/xml; charset=""UTF-8"""
soap.SetRequestHeader "User-Agent", "..."
Debug.Print "WinHttpRequest#Send"
soap.Send CreateGetListMessage
Debug.Print "WinHttpRequest#WaitForResponse"
soap.WaitForResponse 180
Debug.Print "Ready"
End Sub

Private Function CreateGetListMessage() As String
Dim sMessage As String
sMessage = ...
CreateGetListMessage = sMessage
End Function


This is a step further, because while WaitForResponse is waiting for Send to complete (this class module is called in main), the events are called while the response is streamed. But how can I parse the response? In OnResponseStart the response stream is not ready to be parsed by the SAX reader. OnResponseDataAvailable is called multiple times with chunks of data, but how can I parse it?