HOWTO: SAP Sybase IQ, SQLAnywhere, ADO.net and Powershell

Connecting to IQ can be a rather pain in the tuckus.

In the v16 IQ client for Microsoft Windows, the .net assembly changed to “Sap.Data.SQLAnywhere” from “iAnywhere.Data.SQLAnywhere”.

Another annoyance is the loading of the assembly where SAP recommends loading it via

Add-Type -AssemblyName ("Sap.Data.SQLAnywhere.v4.5, Version=17.0.0.10624, Culture=neutral, PublicKeyToken=f222fc4333e0d400")

This forces you to remember the exact version of the assembly as well as the public key token. If you’re worried about a nefarious individual gaining access to your machine and infecting your ado.net assemblies, continue to use the recommended method.

A simpler mechanism is using “LoadWithPartialName” where powershell will load the v4.5 ado.net driver for SQLAnywhere if it is in the Global Assembly Cache (GAC):

[void][System.Reflection.Assembly]::LoadWithPartialName("Sap.Data.SQLAnywhere.v4.5")

Working example of connecting to IQ from PowerShell with a v16.1 IQ client (v17 SQLAnywhere client under the covers):

$HostName = "127.0.0.1"
$Port = 20001
$User = "DBA"
$Password = "password"

[void][System.Reflection.Assembly]::LoadWithPartialName("Sap.Data.SQLAnywhere.v4.5")

# Connection string
$connectionString = "Host=$HostName`:$Port;UserID=$User;Password=$Password"

# Create SAConnection object
$conn = New-Object Sap.Data.SQLAnywhere.SAConnection($connectionString)

# Connect to remote IQ server
$conn.Open()

# simple query
$Query = 'select 1 as col1 from dummy'

# Create a SACommand object and feed it the simple $Query
$command = New-Object Sap.Data.SQLAnywhere.SACommand($Query, $conn)

# Execute the query on the remote IQ server
$reader = $command.ExecuteReader()

# create a DataTable object 
$Datatable = New-Object System.Data.DataTable

# Load the results into the $DataTable object
$DataTable.Load($reader)

# Send the results to the screen in a formatted table
$DataTable | Format-Table -Auto

Output:

col1
----
   1
Share Button

One Reply to “HOWTO: SAP Sybase IQ, SQLAnywhere, ADO.net and Powershell”

  1. I purposefully left out the error handling for the example code. Make sure you implement with error handling that matches your organization’s requirements

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.