Automate Sybase IQ Integration Tasks from PowerShell

Ready to get started?

Download for a free trial:

Download Now

Learn more:

Sybase IQ ADO.NET Provider

Rapidly create and deploy powerful .NET applications that integrate with Sybase IQ.



Are you looking for a quick and easy way to access Sybase IQ data from PowerShell? We show how to use the Cmdlets for Sybase IQ and the CData ADO.NET Provider for Sybase IQ to connect to Sybase IQ data and synchronize, automate, download, and more.

The CData Cmdlets for Sybase IQ are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Sybase IQ.

Cmdlets or ADO.NET?

The cmdlets are not only a PowerShell interface to the Sybase IQ API, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete Sybase IQ data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for Sybase IQ. To access Sybase IQ data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for Sybase IQ.

After obtaining the needed connection properties, accessing Sybase IQ data in PowerShell consists of three basic steps.

To connect to SybaseIQ, set User, Password, Server and Database properties. To secure connections with TLS/SSL, set UseSSL to TRUE.

PowerShell

  1. Install the module:

    Install-Module SybaseIQCmdlets
  2. Connect:

    $sybaseiq = Connect-SybaseIQ -User "$User" -Password "$Password" -Server "$Server" -Database "$Database"
  3. Search for and retrieve data:

    $productname = "Konbu" $products = Select-SybaseIQ -Connection $sybaseiq -Table "Products" -Where "ProductName = `'$ProductName`'" $products

    You can also use the Invoke-SybaseIQ cmdlet to execute SQL commands:

    $products = Invoke-SybaseIQ -Connection $sybaseiq -Query 'SELECT * FROM Products WHERE ProductName = @ProductName' -Params @{'@ProductName'='Konbu'}

ADO.NET

  1. Load the provider's assembly:

    [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Sybase IQ\lib\System.Data.CData.SybaseIQ.dll")
  2. Connect to Sybase IQ:

    $conn= New-Object System.Data.CData.SybaseIQ.SybaseIQConnection("User=myuser;Password=mypassword;Server=localhost;Database=Northwind") $conn.Open()
  3. Instantiate the SybaseIQDataAdapter, execute an SQL query, and output the results:

    $sql="SELECT ProductName, Price from Products" $da= New-Object System.Data.CData.SybaseIQ.SybaseIQDataAdapter($sql, $conn) $dt= New-Object System.Data.DataTable $da.Fill($dt) $dt.Rows | foreach { Write-Host $_.productname $_.price }

Update Sybase IQ Data

PowerShell

Update-SybaseIQ -Connection $SybaseIQ -Columns @('ProductName','Price') -Values @('MyProductName', 'MyPrice') -Table Products -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.SybaseIQ.SybaseIQCommand("UPDATE Products SET ProductName='Konbu' WHERE Id = @myId", $conn) $cmd.Parameters.Add(new System.Data.CData.SybaseIQ.SybaseIQParameter("@myId","10456255-0015501366")) $cmd.ExecuteNonQuery()

Insert Sybase IQ Data

PowerShell

Add-SybaseIQ -Connection $SybaseIQ -Table Products -Columns @("ProductName", "Price") -Values @("MyProductName", "MyPrice")

ADO.NET

$cmd = New-Object System.Data.CData.SybaseIQ.SybaseIQCommand("INSERT INTO Products (ProductName) VALUES (@myProductName)", $conn) $cmd.Parameters.Add(new System.Data.CData.SybaseIQ.SybaseIQParameter("@myProductName","Konbu")) $cmd.ExecuteNonQuery()

Delete Sybase IQ Data

PowerShell

Remove-SybaseIQ -Connection $SybaseIQ -Table "Products" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.SybaseIQ.SybaseIQCommand("DELETE FROM Products WHERE Id=@myId", $conn) $cmd.Parameters.Add(new System.Data.CData.SybaseIQ.SybaseIQParameter("@myId","001d000000YBRseAAH")) $cmd.ExecuteNonQuery()