Bosch IoT Insights

Asynchronous query execution

The following code example shows you how to send an asynchronous MongoDB aggregation query but save the result in Excel with vb .

Make sure to set your authorization credentials and if necessary the appropriate proxy settings.

For the basic authentication, use the credentials of the API user. To create an API user, refer to Creating an API user or Creating an API user via API.

We recommend you to use preemptive authentication. That way, the basic authentication request is sent before the server returns an unauthorized response. Also refer to the Apache documentation.

Sub ImportCSVFile(filepath As String)
''' <summary>
''' Read and CSVFile in Excel-sheet.
''' </summary>
''' <value>The path of the CSVFile</value>
Dim line As String
Dim arrayOfElements
Dim linenumber As Integer
Dim elementNumber As Integer
Dim element As Variant
linenumber = 0
elementNumber = 0
Open filepath For Input As #1 ' Open file for input
Do While Not EOF(1) ' Loop until end of file
linenumber = linenumber + 1
Line Input #1, line
arrayOfElements = Split(line, ";")
elementNumber = 0
For Each element In arrayOfElements
elementNumber = elementNumber + 1
Cells(linenumber, elementNumber).Value = element
Close #1 ' Close file.
End Sub
Public WithEvents newButton As Windows.Forms.Button
Sub AsyncMongoRequest()
''' <summary>
''' Set header, basic authentication and proxy for the WinHttpRequest.
''' Save the HTTPRequest response as csvFile.
''' Read the csvFile in Excel-sheet.
''' Requirement:
''' JsonConverter See: (
''' TODO handle catch and exception
''' </summary>
Dim strResult As String
Dim query As String
Dim objHTTP As Object
Dim url As String
Dim Json As Object
Dim status As String
Dim postId As String
Dim proxyServer As String
Dim basicAuth As String
Dim ntUser As String
Dim ntPassword As String
Dim fso As Object
Dim oFile As Object
server = ""
serviceBaseUrl = "/mongodb-query-service/v2/<your_project>"
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
query = "{""collection"": ""<your_project>_processed_data"", ""query"": [{""$limit"":10}]}"
proxyServer = ""
' If you are inside your company network, a proxy authentication may be required. Otherwise, you can remove this from the example. This is an example for a Bosch internal proxy.
basicAuth = "Basic " & "<your basic authentication string>"
ntUser = "<username>"
ntPassword = "<password>"
url = server & serviceBaseUrl
objHTTP.Open "POST", url & "/submit-aggregation-query", False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.setRequestHeader "Content-type", "application/json"
objHTTP.setRequestHeader "Authorization", basicAuth
objHTTP.setRequestHeader "Connection", "Keep-Alive"
objHTTP.setProxy 2, proxyServer, ""
objHTTP.SetCredentials ntPassword, ntPassword, 1
objHTTP.send query
strResult = objHTTP.responseText
Set Json = JsonConverter.ParseJson(strResult)
postId = Json("queryId")
url = url & "/queries/" & postId
objHTTP.Open "GET", url, False
strResult = objHTTP.responseText
status = objHTTP.status
Set Json = JsonConverter.ParseJson(strResult)
status = Json("status")
If status = "SUCCESSFUL" Then
url = url & "/result?"
objHTTP.Open "GET", url, False
strResult = objHTTP.responseText
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFile = fso.CreateTextFile("filepath://where you want to store the response")
oFile.WriteLine strResult
ImportCSVFile "filepath://where your response is stored"
MsgBox "Error in data processing on the server: " & strResult & status
End If
End Sub
Private Sub Form1_Load() Handles Me.Load
''' <summary>
''' Create insightsDownloadButton and
''' append AsyncMongoRequest sub function to the button
''' </summary>
newButton = New Windows.Forms.Button
newButton.Name = "insightsDownloadButton" & i
newButton.Top = 20 * 30
newButton.Left = 40
AddHandler newButton.Click, AddressOf RequestButton
End Sub