Connect to SAP HANA from Windows PowerShell using ODBC

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.

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.

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 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 have a different background color in PowerShell. This hurts my eyes. And because I was running into many errors during work on this blog, let make sure we fix the color scheme first.

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

Ok, now let’s rock!

$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 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.

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

It is easy to get "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified" if — like me — you assume that because of spaces in the name 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 because of the new error "ERROR [HY000] [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;-10719 Connect failed (invalid SERVERNODE '')" when added the DRIVER ODBC standard 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??

Assuming you connection was successful, let’s query the same data as in the tutorial, and close the connection.

$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 above to get the data from the connection. But 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.

$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. It 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.