VBA xmlhttprequest loop without closing connection

Question

I am connecting to an API using xmlhttp in VBA. The problem is that I need to create a loop multiple times using the same API link with the only difference that I send different JSON string.

Is it possible to open the connection and send headers and authentification only once then send data and receive response multiple times?

The main purpose is to improve the speed of the code. I am aware that I can send header and authenticate inside the loop but it runs too slow for my needs.

Thank you!

    APIKey = "xx" 
    Session = "ss" 
    API_Link = "zz" 
    Dim xhr: Set xhr = CreateObject("MSXML2.XMLHTTP")
        With xhr
            .Open "POST", API_Link, False
            .setRequestHeader "X-Application", APIKey
            .setRequestHeader "Content-Type", "application/json"
            .setRequestHeader "Accept", "application/json"
            .setRequestHeader "X-Authentication", Session
        End With
        For Each cl In Rng
       'The below runs only the first time then it gives an error 
            xhr.send JSON_Query
            a = xhr.ResponseText 
        next cl

QHarr · Accepted Answer · 2019-11-19T12:27:03.767

No, I don't believe you can with xmlhttp (happy to be proven wrong) in terms of keeping headers. The best you can do is create the xmlhttp object outside the loop and hold certain values in variables for re-use. You still have to provide the transactions of .Get , .Send and the headers.

In terms of preserving authentication I think you can login once and then pass the cookies (JSESSION) in subsequent requests - depending on expiration period - using MSXML2.ServerXMLHTTP.

In other languages e.g. python you have the implementation of http sessions. This allows you to persist certain parameters across requests.

HTTP Persistent Connection

HTTP persistent connection, also called HTTP keep-alive, or HTTP connection reuse, is the idea of using a single TCP connection to send and receive multiple HTTP requests/responses, as opposed to opening a new connection for every single request/response pair. The newer HTTP/2 protocol uses the same idea and takes it further to allow multiple concurrent requests/responses to be multiplexed over a single connection.

In python, for example:

Sessions can also be used to provide default data to the request methods. This is done by providing data to the properties on a Session object.

So, you create the Session object, which has the same methods as the API library which issues the requests (like xmlhttp does) and then update headers which persist.

s = requests.Session()
s.auth = ('user', 'pass')
s.headers.update({'x-test': 'true'})

# both 'x-test' and 'x-test2' are sent
s.get('https://httpbin.org/headers', headers={'x-test2': 'true'})

A session identifies the requests that originate from the same browser during the period of conversation. All the servlets can share the same session. The JSESSIONID is generated by the server and can be passed to client through cookies _{Srinivas Balasani}

Advantages:

Reduced latency in subsequent requests (no handshaking).

Reduced CPU usage and round-trips because of fewer new connections and TLS handshakes.

Enables HTTP pipelining of requests and responses.

Reduced network congestion (fewer TCP connections).

Errors can be reported without the penalty of closing the TCP connection.

According to RFC 7230, section 6.4, "a client ought to limit the number of simultaneous open connections that it maintains to a given server". The previous version of the HTTP/1.1 specification stated specific maximum values but in the words of RFC 7230 "this was found to be impractical for many applications... instead... be conservative when opening multiple connections". These guidelines are intended to improve HTTP response times and avoid congestion. If HTTP pipelining is correctly implemented, there is no performance benefit to be gained from additional connections, while additional connections may cause issues with congestion

Disadvantages:

If the client does not close the connection when all of the data it needs has been received, the resources needed to keep the connection open on the server will be unavailable for other clients. How much this affects the server's availability and how long the resources are unavailable depend on the server's architecture and configuration.

So, still using python example, we commonly use a with statement which automatically ensures closure of connection with an exit method after execution of the code block nested within.

I think WinHttp has functionality around setting cookies. Additional session info here. Whilst there is mention of pointers to string

lpszHeaders:

A pointer to a string that contains the additional headers to append to the request. This parameter can be WINHTTP_NO_ADDITIONAL_HEADERS if there are no additional headers to append.

this is not something I have ever tried.

AFAIK VBA doesn't come with a TCP implementation by default though you may find a solution via COM.

VBA xmlhttprequest loop without closing connection

1 Answers1

Linked