Connecting to multiple Hana DB's via ODBC

PowerShell Help
1

Good morning,

I am trying to create a script that connects to multiple HANA db’s, runs a query, then closes the connection/sends me the results. I am struggling on the connection piece, as it only seems to pull the query from one system, even though it says multiple connections were opened when I run the foreach loop to connect. Below is what I currently have for the script:

$password=Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR((($Credential.Password))))
$nodes = get-content C:\Users\Me\serverlist.txt #contents are listed as servername:portID ex: server1:30015
$DBs = "server1","server2"
Foreach ($node in $nodes)
    {
$conn = new-object System.Data.Odbc.OdbcConnection
$conn.connectionstring = "Driver={HDBODBC};SERVERNODE={$node};UID={$env:USERNAME};PWD={$password}"
$conn.Open()
Write-Output $conn

$DBInfo = Foreach($DB in $DBs){
$CurrentDate = "%" + ((get-date).AddDays(-1)).ToString('MM-dd') + "%"

$Query = "Query Details Here"

$cmd = New-object System.Data.Odbc.OdbcCommand($Query,$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}}

I have also tried

$password=[Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR((($Credential.Password))))
$allServers = get-content C:\Users\Me\AD.txt #contents are listed as servername:portID ex: server1:30015
# loop through the servers array and collect the output in variable $result
$result = foreach($servername in $allServers) { 
    # Connect HANA Server
$conn =  new-object System.Data.Odbc.OdbcConnection
$conn.connectionstring = "Driver={HDBODBC};SERVERNODE={$servername};UID={$env:USERNAME};PWD=$password}"
$conn.Open()
Write-Output $conn

$CurrentDate = "%" + ((get-date).AddDays(-1)).ToString('MM-dd') + "%"

$Query = "Query Details Here"

$cmd = New-object System.Data.Odbc.OdbcCommand($Query,$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}

$result | Out-GridView

I run into the same problem with both scripts. The trouble is when I run the first foreach loop, it returns connections for both DB’s, but when I check $conn, it only lists server 2. When I check the log info from the query, it only returns information from server 2. Is there something I am missing in order to get server 1 info as well, or is it not possible to do what I am asking?

image
image693×253 50 KB

2

nfrosa01,
Welcome to the forum. :wave:t4:

Of course. You assign the variable inside the loop. That’s why you only get the status of the last assignment.

If I got it right … if you want to catch the output of all $nodes you have to assign the output of the loop to a variable or you have to add/collect the output inside the loop to a variable.
This:

$Result =
Foreach ($node in $nodes) {
   $node   
}

Or this:

Foreach ($node in $nodes) {
    $Result += $node 
}
3

Hi Olaf,

Thanks for the assistance so far. I want to make sure I understand what you are saying. You are saying to add the $Result code above before the $conn information? This is still only connecting to server2, so I probably misunderstood. What we have below will pass both nodes to the $Result variable. Do I need another ForEach for the connectionstring? If I run this as it is, $Result will return the contents of the nodes.txt file, but when I check $conn, it still shows server2. I need it to connect to server1 and server2 and subsequently run the Query statement.

$password=[Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR((($Credential.Password))))
$nodes = get-content C:\Users\me\nodes.txt #contents are listed as servername:portID ex: server1:30015
$DBs = "server1","server2"
$Result = Foreach ($node in $nodes) {
   $node   
}

$conn = new-object System.Data.Odbc.OdbcConnection
$conn.connectionstring = "Driver={HDBODBC};SERVERNODE={$node};UID={$env:USERNAME};PWD={$password}"
$conn.Open()
Write-Output $conn

$CurrentDate = "%" + ((get-date).AddDays(-1)).ToString('MM-dd') + "%"

$Query = "Query details here'"

$cmd = New-object System.Data.Odbc.OdbcCommand($Query,$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
4

No. My code snippet was just a generalized example to show the two different methods of collecting the results from a foreach loop.

Here is a more detailed example:
Input data …

$nodes = 
'Node_01',
'Node_02',
'Node_03'

Collecting the results with a variable assignment in front of the loop …

$Result =
Foreach ($node in $nodes) {
   $node   
}

Collecting the results with an additive variable inside the loop …

Foreach ($node in $nodes) {
    $output += $node 
}

The results depend pretty much on the objects you process … for strings the results differ from each other …

$Result
$output

And the output looks like this:

Node_01
Node_02
Node_03
Node_01Node_02Node_03
5

Looks like I got this to work after some slight adjustments. Removed the write-outputs and changed how the nodes are passed to the connectionstring.

$Results = Foreach ($node in $nodes) {
$conn = new-object System.Data.Odbc.OdbcConnection
$nodeport = $node + ":30015"
$conn.connectionstring = "Driver={HDBODBC};SERVERNODE={$nodeport};UID={$env:USERNAME};PWD={$password}"
$conn.Open()


$CurrentDate = "%" + ((get-date).AddDays(-1)).ToString('MM-dd') + "%"

$Query = "Query Details'"

$cmd = New-object System.Data.Odbc.OdbcCommand($Query,$conn)
$dtab = New-Object System.Data.DataTable
$adap = New-Object System.Data.Odbc.OdbcDataAdapter($cmd)
$nrr = $adap.fill($dtab)

This will connect to all Hana DB’s in the $nodes variable and run your query details.

6

Cool. And thanks for sharing. :+1:t4: :love_you_gesture:t4: :slightly_smiling_face: