Learn more
 

Ready to get started?

Download a free trial of the Sybase IQ Data Provider to get started:

 Download Now

Learn more:

Sybase IQ Icon Sybase IQ ADO.NET Provider

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

In this article

Related articles

Automate Sybase IQ Integration Tasks from PowerShell


Are you in search of a quick and easy way to access Sybase IQ data from PowerShell? This article demonstrates how to utilize the Sybase IQ Cmdlets for tasks like connecting to Sybase IQ data, automating operations, downloading data, 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.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to Sybase IQ, 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.

Once you have acquired the necessary connection properties, accessing Sybase IQ data in PowerShell can be enabled in three 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-Object 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-Object 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-Object System.Data.CData.SybaseIQ.SybaseIQParameter("@myId","001d000000YBRseAAH"))) $cmd.ExecuteNonQuery()