Purpose
Read data from a HANA database

Remark
The HANA database which we want to read can be part of a full S4HANA system or it can be a standalone HANA database with non-SAP data. In the case of S4HANA an alternative to the approach described here is to call up appropriate function modules in the S4HANA system. 

Solution
Install the HANA client on the frontend PC from which you want to read HANA data and then use a VB call in your script that reads the database via the SAP ODBC driver.

Example 1
We display a table showing customer data read from an S4HANA system:

 

GuiXT Script:

GuiXT 
Pushbutton (toolbar) "Read HANA DB" process="read_hanadb.txt"

if V[customercount>0]
   Table (1,0) (16,93) name="customers" rows="&V[customercount]" _
       title="Customers in HANA DB" fixedColumns=4
  Column "Account" size=12 name="kunnr" -readOnly
  Column "Name" size=40 name="name1" -readOnly
  Column "City" size=30 name="ort01" -readOnly
  Column "Country" size=10 name="land1" -readOnly
endif

InputScript "read_hanadb.txt"

GuiXT 
// Connect to HANA DB
// pass server name or IP address, port, user and password
CallVB msg = hanadb.connection.open "myserver" "30215" "BPINST" "Welcome1"

// Any error message?
if V[msg]
   Message "No connect to HANA DB:\n&V[msg]"
  return
endif

// Clear table cell variables
Clear V[customers.cell.*]

// Clear table status
Clear V[customers.stat.*]



// Read database, row count is returned
CallVB customercount = hanadb.connection.read "customers.cell" _
     "Select KUNNR,NAME1,ORT01,LAND1 from saphanadb.KNA1 where MANDT = '100'"

// Close connection (or leave it open for further calls)
CallVB hanadb.connection.close

Return

 

Example 2
 Same as above but in addition

  • we add a case insensitive search by customer name
  • we display the country name instead of country code
  • we order the table by customer name
  • we display the number of customers found

 

GuiXT Script:

GuiXT 
InputField (1,1) "Customer name contains" (1,24) size=30 name="cname"
Pushbutton (1,59) "Search" process="search_customers.txt" size=(1,17)

if not V[customercount]
  Set V[customercount] 0
endif

Table (3,1) (18,104) name="customers" rows="&V[customercount]" _
       title="&V[customercount] customers found" fixedColumns=4
  Column "Account" size=12 name="kunnr" -readOnly
  Column "Name" size=40 name="name1" -readOnly
  Column "City" size=30 name="ort01" -readOnly
  Column "Country" size=20 name="country" -readOnly

InputScript "search_customers.txt"

GuiXT 
// Connect to HANA DB
// pass server name or IP address, port, user and password
CallVB msg = hanadb.connection.open "myserver" "30215" "BPINST" "Welcome1"

// Any error message?
if V[msg]
   Message "No connect to HANA DB:\n&V[msg]"
  return
endif

// Build select statement
Clear V[sql]
Set V[sql] "&V[sql] Select A.KUNNR, A.NAME1, A.ORT01, B.LANDX as country"
Set V[sql] "&V[sql]   from saphanadb.KNA1 A join saphanadb.T005T B"
Set V[sql] "&V[sql]   on B.LAND1 = A.LAND1"
Set V[sql] "&V[sql]   where CONTAINS(A.NAME1,'%&V[cname]%') "
Set V[sql] "&V[sql]   and A.MANDT = '100' and B.MANDT = '100' and B.SPRAS = 'E' "
Set V[sql] "&V[sql]   order by A.NAME1"

// Clear table cell variables
Clear V[customers.cell.*]

// Clear table status
Clear V[customers.stat.*]

// Read database, row count is returned
CallVB customercount = hanadb.connection.read "customers.cell" "&V[sql]"

// Close connection (or leave it open for further calls)
CallVB hanadb.connection.close

Return

 

Prerequisites

Download the HANA client 32Bit version from SAP store (free of charge)

 

 Install it on the PC (any folder of your choice):

The port number in the case of S4HANA is 3xx15 where xx is the SAP system number. The port number is shown in HANA studio (Eclipse):

The HANA database server can be located anywhere in the local net or in the internet, but you need to open the port, e.g. 30215, in the firewall settings and maybe instruct a router to forward this port to the right server.

 

VB.NET

In our VB.NET project we use a reference to the HANA interface

VB.NET 
' Prerequisite:
' download SAP HANA Client Software Packages -> 32Bit (!) version
'  of HANA client, from SAP 

Imports Sap.Data.Hana


Public Class connection

    Private conn As HanaConnection = Nothing
    Private guixt As New guinet.guixt


    ' open connection
    Public Function open(server As String, port As String, _
            userid As String, password As String) As String

        Dim connectionstring As String = "Server=" & server & ":" _ 
               & port & ";UserID=" & userid & ";Password=" & password

        Try

            If conn Is Nothing Then
                conn = New HanaConnection(connectionstring)
            End If

            conn.Open()

        Catch ex As Exception
            Return ex.Message + vbCrLf + ex.InnerException.Message
        End Try

        Return ""
    End Function


    ' read data
    Public Function read(varname As String, command As String) As Integer

        Dim n As Integer = 0

        If conn Is Nothing Then
            Return 0
        End If

        Try

            Dim cmd As HanaCommand = New HanaCommand(command, conn)
            Dim r As HanaDataReader = cmd.ExecuteReader()
            While r.Read()

                n += 1

                For k As Integer = 0 To r.FieldCount - 1
                    guixt.SetVariable(varname & "." & _
          r.GetName(k).ToLower & "." & n.ToString, r.Item(k).ToString)
                Next


            End While
            r.Close()

        Catch ex As Exception
            MsgBox(ex.Message)
            Return 0
        End Try


        Return n
    End Function

    ' close connection
    Public Sub close()

        If conn IsNot Nothing Then
            conn.Close()
        End If

    End Sub


End Class

 

Full VB project hanadb.zip

 

Components
InputAssistant + Controls