Connect to SAP HANA from Windows PowerShell using ODBC

[Vitaliy-R]

I was continuing with the tutorials mission Use Clients to Query an SAP HANA Database recently published by daniel.vanleeuwen 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 like scripting using Command Line .bat and PowerShell .ps1 are the only "programming" environments (again, out-of-the-box, meaning no additional installation required).

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

A bit more digging, and another Wow! moment: PowerShell can create an instance of .NET Framework object in the command line using New-Object cmdlet. I do not know if this framework is out-of-the-box on each installation, but the version v4.0 is available on mine.

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

A statement above is the PowerShell equivalent of cmd's dir %windir%\Microsoft.NET\Framework /AD. In this post I am going to do all commands using PowerShell, but if you want to run them from the Command Line, then prefix them with powershell -c.

Cool, I can work .NET Framework Data Provider for ODBC directly from Windows PowerShell (because, you know "The Future is Terminal", right dj.adams?)

Using WDAC cmdlets with SAP HANA ODBC

Get-OdbcDriver -Name "HDB*"

Both 64 and 32 bits HANA ODBC drivers installed on my machine.

The output looked to me like a nested structure for Attribute. And I was right. After a bit of further experimentation, I was able to get nested information displayed too.

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

Now, let's check HANA ODBC DSNs already configured while following Dan's tutorial: listing all followed checking all details of one of them in a JSON format. DSN is a "Data Source Name", or a symbolic name that represents the predefined ODBC connection.

Get-OdbcDsn -DriverName "HDBODBC*"

Get-OdbcDsn -Name "hxehost" | ConvertTo-Json

If needed, you can modify or delete defined DSNs from the shell too, but I leave it to you to experiment.

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.

Let's move on to something really exciting.

.NET Framework to query SAP HANA from the PowerShell using DSN in a connection string

One thing before we proceed. For whatever reason errors in PowerShell have a different background color. This hurts my eyes.

I was running into many errors during my work on this blog. Let's fix their color scheme first.

$host.privatedata.errorbackgroundcolor= $host.ui.rawui.BackgroundColor

Ok, now we are ready to rock the shell!

$conn = new-object System.Data.Odbc.OdbcConnection

$conn.connectionstring = "Dsn=SAP HANA Cloud;UID={User1};PWD={Password1};CurrentSchema=HOTEL"

$conn.Open()

Write-Output $conn

By now, we are connected to the instance of the SAP HANA Cloud using the available DSN in the connection string. DSN, UID and PWD are ODBC standard attribute keywords. CurrentSchema is a driver-defined attribute keyword, in this case, HANA-specific attribute.

Assuming your connection was successful, let's query the same data as in the tutorial.

$cmd = New-object System.Data.Odbc.OdbcCommand("SELECT * FROM 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

Closing the connection is obligatory in ODBC to avoid performance degradation or hitting a limit in open connections.

.NET Framework to query SAP HANA from the PowerShell using DSN-less connection string

You do not need to configure Connections in ODBC Data Source Administrator app, to be able to query the database.

$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. And we can repeat the same steps as in DSN exercise to get the data from 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

Note that this time we need to include HOTEL schema to query from HOTEL table, as this time the connection string did not have CurrentSchema property defined.

Let's turn this into the executable 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 this queryHana.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

}

Today 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. It collects and shows parameters accepted by the script.

 Get-Help .\queryHana.ps1

While by default queryHana.ps1 connects to a HANA Express instance, I can call the script as well 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 - ?

.\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 additional property CURRENTSCHEMA in the connection string and its influence of the result. And note the use of System.Data.DataSet instead of System.Data.DataTable.

---

I enjoyed this exercise because I did not know any of this when woke up this morning 🙂 But...

...it took me quite a while to get things right. One of the most confusing steps was the use of curly braces in connection strings. I reread a few times the required syntax of connection strings, but maybe it was already late. I will need to read this once again.

It is easy to get "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified" when assumed that because of spaces in it the name of the DSN attribute value should be in {}. Like in $conn.connectionstring = "DSN={SAP HANA Cloud}".

Because of "...no default driver specified" in the previous error, I made another mistake that kept me on toes, now because of the new error "ERROR [HY000] [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;-10719 Connect failed (invalid SERVERNODE '')" when I added the DRIVER attribute keyword to the connection string. Like in $conn.connectionstring = "DRIVER={HDBODBC};Dsn={SAP HANA Cloud}".

I mean how would you assume something is wrong even after staring at this for an hour??

One more problem I was facing was with some strange C:\Windows\ODBC.ini file although the whole configuration should be in the registry Computer\HKEY_CURRENT_USER\Software\ODBC\ODBC.INI.

---

The level unlocked! I managed to practice coding ODBC interactions with SAP HANA using only what is available in Windows.

-Vitaliy (aka @Sygyzmundovych)

 

PS. For the reference, product versions used (because, you know, it is the software we are talking about):

• MS Windows 10.0.17763.1098


• PowerShell 5.1.17763


• .NET Framework 4.0.30319


• ODBC Driver Manager 10.0.17763


• SAP HANA Clients 64-bit and 32-bit 2.4.191


• SAP HANA Cloud (4.0) and SAP HANA, express edition (2.0.45)

[former_member247294]

I had no idea that you can instantiate .NET objects from Power Shell until today.

Will this work with the .NET Core driver release in windows_clients.zip of HANA also ?

 

Thanks for the insights !

[Vitaliy-R]

And I had no idea about that till yesterday neither!

I do not know about .NET Core, but you know I thought about you immediately after seeing .NET ?

[menona]

Is there a way to logon using single sign on ?

[Vitaliy-R]

Do you mean SSO using ODBC from Windows PowerShell, menona?

[rferraton]

Hello Witalij,

I would like to thank you about this very clear and detailed article that help me a lot.

I made my own article that deal with HANA Export data to CSV I talk about ODBC but also ADO to connect to HANA using powershell too !