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
            Next
        Loop
    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: (https://github.com/VBA-tools/VBA-JSON)
    ''' 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 = "https://bosch-iot-insights.com"
    serviceBaseUrl = "/mongodb-query-service/v2/<your_project>"
    Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    query = "{""collection"": ""<your_project>_processed_data"", ""query"": [{""$limit"":10}]}"
    proxyServer = "rb-proxy-de.bosch.com:8080"
    ' 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
    objHTTP.send
    strResult = objHTTP.responseText
    status = objHTTP.status
    Set Json = JsonConverter.ParseJson(strResult)
    status = Json("status")
 
    If status = "SUCCESSFUL" Then
        url = url & "/result?format=text%2Fvnd.insights.excel.de%2Bcsv"
        objHTTP.Open "GET", url, False
        objHTTP.send
 
        strResult = objHTTP.responseText
 
        Set fso = CreateObject("Scripting.FileSystemObject")
 
        Set oFile = fso.CreateTextFile("filepath://where you want to store the response")
 
        oFile.WriteLine strResult
        oFile.Close
        Close
 
        ImportCSVFile "filepath://where your response is stored"
    Else
        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
    Me.Controls.Add(newButton)
End Sub