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
// 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