How To Get Data From SAP NW Gateway Interface with OData via VBScript

SAP offers a new interface technology on OData platform – you can find much more about OData here. The OData interface is primarly for use with the new UI5 technology – you can find much more information here and about OpenUI5 here. But it is also possible to use the SAP NetWeaver Gateway Interface with VBScript. Here an easy example how to get data:

'-Begin-----------------------------------------------------------------

  '-Constants-----------------------------------------------------------
    Const Base64 = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/"

  '-Function MyASC------------------------------------------------------
  '-
  '- (c) 2001 Antonin Foller, Motobit Software
  '- www.motobit.com/tips/detpg_base64encode/
  '-
  '---------------------------------------------------------------------
    Function MyASC(OneChar)
      If OneChar = "" Then
        MyASC = 0
      Else
        MyASC = Asc(OneChar)
      End If
    End Function

  '-Function Base64Encode-----------------------------------------------
  '-
  '- (c) 2001 Antonin Foller, Motobit Software
  '- www.motobit.com/tips/detpg_base64encode/
  '-
  '---------------------------------------------------------------------
    Function Base64Encode(inData)

      '-Variables-------------------------------------------------------
        Dim cOut, sOut, i, nGroup, pOut, sGroup

      'For each group of 3 bytes
        For i = 1 To Len(inData) Step 3

          'Create one long from this 3 bytes.
            nGroup = &H10000 * Asc(Mid(inData, i, 1)) + _
              &H100 * MyASC(Mid(inData, i + 1, 1)) + _
              MyASC(Mid(inData, i + 2, 1))

          'Oct splits the long To 8 groups with 3 bits
            nGroup = Oct(nGroup)

          'Add leading zeros
            nGroup = String(8 - Len(nGroup), "0") & nGroup

          'Convert To base64
            pOut = Mid(Base64, CLng("&o" & Mid(nGroup, 1, 2)) + 1, 1) + _
              Mid(Base64, CLng("&o" & Mid(nGroup, 3, 2)) + 1, 1) + _
              Mid(Base64, CLng("&o" & Mid(nGroup, 5, 2)) + 1, 1) + _
              Mid(Base64, CLng("&o" & Mid(nGroup, 7, 2)) + 1, 1)

          'Add the part To OutPut string
            sOut = sOut + pOut

          'Add a new line For Each 76 chars In dest (76*3/4 = 57)
          'If (I + 2) Mod 57 = 0 Then
          '  sOut = sOut + vbCrLf
          'End If

        Next

      Select Case Len(inData) Mod 3
        Case 1: '8 bit final
          sOut = Left(sOut, Len(sOut) - 2) + "=="
        Case 2: '16 bit final
          sOut = Left(sOut, Len(sOut) - 1) + "="
      End Select

      Base64Encode = sOut
    End Function

  '-Sub Main------------------------------------------------------------
    Sub Main()

      '-Variables-------------------------------------------------------
        Dim Http, Authorization, User, Password, Result

      Set Http = CreateObject("WinHttp.WinHttpRequest.5.1")
      If IsObject(Http) Then

        User = "MyUser"
        Password = "Secret"
        Authorization = Base64Encode(User & ":" & Password)

        Http.Open "GET", "http://sid.MyHost.de:8330" & _
          "/sap/opu/odata/ZDELIVERY_SRV/DeliveriesSet?$format=xml", True
        Http.SetRequestHeader "Authorization", "Basic " & Authorization
        Http.Send

        Http.WaitForResponse 10

        MsgBox Http.StatusText & vbCrLf & Http.GetAllResponseHeaders
        If Http.Status = 200 Then
          Result = Http.ResponseText
          MsgBox Result
        End If

        Set Http = Nothing
      End If

    End Sub

  '-Main----------------------------------------------------------------
    Main

'-End-------------------------------------------------------------------

In the Main subroutine we create an object from WinHttp.WinHttpRequest – a standard library which should be available on any Windows OS installation. We set the user and password variables and encode them into Base64 format. Now we open a connection to the target SAP system and OData service. We set the Authorization keyword of the request header with Basic and the encoded user and password. Now the get request is send to the backend system and we wait for response. The status text and the complete response header is shown in a message box. If the status is ok (200) the response text is set in the variable result and it is also shown in a message box.

As you can see it is very easy to use the OData interface from the SAP NetWeaver Gateway inside a scripting language like VBScript.