EDUCAÇÃO E TECNOLOGIA

Connect to SAP HANA from Windows PowerShell using ODBC

I was continuing with the tutorials mission Use Clients to Query an SAP HANA Database recently published by Daniel Van Leeuwen and arrived to the ODBC tutorial. There Dan described how to use ODBC Data Source application to configure connections to SAP HANA on Windows and then how to use MS Excel as a client to get data from SAP HANA using configured ODBC connections.

But as a developer, I asked myself

What can I use to try this out programmatically out of the box on Windows 10?

After a bit of research, it looked that scripts using Command Line and PowerShell are the “programming” environments (again, out-of-the-box, meaning no additional installation required).

Next step, I found that PowerShell provides Windows Data Access Components (Windows DAC) cmdlets. Wow, that was already something! Frankly speaking, I have used WDAC much since around 2004.

A bit more digging, and — another Wow! moment — PowerShell can create an instance of .NET Framework object using New-Object cmdlet. I do not know if this framework is out-of-the-box on each installation as assumed, but the version v4.0 is available on mine (below is the equivalent of dir %windir%\Microsoft.NET\Framework /ADusing PowerShell.

Get-ChildItem -Attributes Directory $Env:WinDir\Microsoft.NET\Framework\

Now I can access .NET Framework Data Provider for ODBC from Windows PowerShell (because, you know “The Future is Terminal“, right DJ Adams?)

Using WDAC cmdlets with SAP HANA ODBC

Get-OdbcDriver -Name "HDB*"

So, I have both 64 and 32 bits drivers installed.

The output looks to me like a nested structure. And I was right, as, after a bit of further experimentation, I was able to get nested information displayed.

(Get-OdbcDriver -Name "HDB*").GetValue(1).Attribute

Now, let’s check connections configured when following Dan’s tutorial: list and details of one of them in a JSON format.

Get-OdbcDsn -DriverName "HDBODBC*" Get-OdbcDsn -Name "hxehost" | ConvertTo-Json

If needed, you can modify or delete them from the shell too, but let’s move on to something equally exciting.

Btw, SAP provides two useful troubleshooting utilities as well, which you can find in the installation folder of HANA Clients: hdbodbc_cons.exe and odbcreg.exe. Their  use in not part of the today’s post.

Using .NET Framework to query SAP HANA from the PowerShell

$conn = new-object System.Data.Odbc.OdbcConnection $conn.connectionstring = "Driver={HDBODBC};SERVERNODE={hxehost:39015};UID={User1};PWD={Password1};encrypt={True};sslValidateCertificate=False" $conn.Open() Write-Output $conn

By now, we are connected to the instance of SAP HANA, express edition. Let’s query the same data as in the tutorial, and close the connection.

$cmd = New-object System.Data.Odbc.OdbcCommand("SELECT * FROM HOTEL.HOTEL",$conn) $dtab = New-Object System.Data.DataTable $adap = New-Object System.Data.Odbc.OdbcDataAdapter($cmd) $nrr = $adap.fill($dtab) Write-Output "Records selected: ${nrr}" Write-Output $dtab $conn.Close() Write-Output $conn

Let’s turn this into the runnable PowerShell script…

…called queryHana.ps1 in the new subfolder odbc.

New-Item -Path "$env:USERPROFILE\HANAClientsTutorial" -Name "odbc" -ItemType "directory" -Force cd "$env:USERPROFILE\HANAClientsTutorial\odbc" New-Item -Name "queryHana.ps1" -ItemType "file" -Force

Here is the code for thisqueryHana.ps1 script file.

param( [String] $hdbhost = "hxehost", [Int32] $hdbport = 39015, [String] $hdbuser = "User1", [String] $hdbpwd = "Password1" ) $hdbsql = "SELECT * FROM HOTEL.HOTEL" $conn = new-object System.Data.Odbc.OdbcConnection $conn.connectionstring = "Driver={HDBODBC};SERVERNODE={${hdbhost}:${hdbport}};UID={${hdbuser}};PWD={${hdbpwd}};encrypt={True};sslValidateCertificate=False" try { $conn.open() $cmd = New-object System.Data.Odbc.OdbcCommand($hdbsql,$conn) $dtab = New-Object System.Data.DataTable $adap = New-Object System.Data.Odbc.OdbcDataAdapter($cmd) $nrr = $adap.fill($dtab) Write-Output "Records selected: ${nrr}" Write-Output $dtab $conn.Close() } catch { Write-Output $_.Exception.Message }

I used the VS Code IDE and its PowerShell plug-in to develop this. Notepad++ has support for PowerShell language, and so does Atom with a plug-in.

Another cool cmdlet to try with the script is Get-Help that collects and shows parameters acceptable by the script.

 Get-Help .\queryHana.ps1

While by default the script connects to my HANA Express instance, I can call the script with my HANA Cloud instance host and port (and user plus password, if needed) as parameters. And PowerShell automatically supports tab autocompletion for parameter names after you type - :-O

.\queryHana.ps1 ` >> -hdbhost 2246ed61-81df-48e8-9711-323311f7613f.hana.prod-eu20.hanacloud.ondemand.com ` >> -hdbport 443

Use the HANA Clients secure user store (hdbuserstore)

If you followed Create a User, Tables and Import Data Using HDBSQL, which is the part of the same Use Clients to Query an SAP HANA Database tutorials mission, then you should have already user keys created in the HANA Clients secure user store, like USER1USERKEY.

These keys can (and should) be used in ODBC connection strings in the applications. Specify the user store key with the @ sign in your data source or in the connection string: servernode=@<KEY>.

$conn = new-object System.Data.Odbc.OdbcConnection $conn.connectionstring = "Driver={HDBODBC};SERVERNODE=@USER1USERKEY;CURRENTSCHEMA=HOTEL" $conn.Open() $cmd = New-object System.Data.Odbc.OdbcCommand("SELECT CURRENT_USER, CURRENT_SCHEMA FROM DUMMY",$conn) $dset = New-Object System.Data.DataSet $adap = New-Object System.Data.Odbc.OdbcDataAdapter($cmd) $nrr = $adap.fill($dset) Write-Output "Records selected: ${nrr}" Write-Output $dset.Tables $conn.Close()

Please note the use of another additional property CURRENTSCHEMA in the connection string. And experimental use of System.Data.DataSet instead of System.Data.DataTable.


I hope you find this helpful. At least, I enjoyed this exercise, because I did not know any of this when woke up today morning 🙂

-Vitaliy (aka @Sygyzmundovych)